Formula won't calculate, shows it as text

[anderslyman]

I'm fairly new to Calc, but I know this formula is correct:
=VLOOKUP(C39;Sheet2.A2:B1358;2)

This generates no errors, the formula simply stays in the cell as text and won't calculate anything. (see attached image)

Any thoughts?

Edit:  I should add that the formulas checkbox is unchecked in the Tools>Options>OO.org Calc>View>Display dialog

[acknak]

Are those cells formatted as text?

Format > Cells > Numbers > Category: Text, or style: Default set to Numbers > Category: Text?

[anderslyman]

No, they are formatted as numbers, I tried a couple different formats, nothing works.

[Hagar Delest]

Can you upload a sample file?

[anderslyman]

I wish I could, it's a work spreadsheet and has sensitive information. If I try to duplicate the error on other spreadsheets I can't recreate the error. I'll have to find a work-around I guess. Thanks for your help.

[acknak]

You could use search and replace to remove the confidential information--change all characters to 'x' or '9', something like that. Just make sure you aren't recording changes.

If you can, you might want to try the sheet under OOo on XP. I would not be at all surprised if there are still some strange glitches with OOo under Vista.

[zcat7777]

I think that comes from formatting the cells with the formulas as text after creating the formulas, and then trying to edit the formula, since that's what happened to me.

I think it can be worked around by deleting the cells with the formulas and replacing them with new ones in the same position, although that's a little complicated.

A workaround I used was to remove the equal sign at the beginning of the first formula in the range and enter it, which, as the program is designed, showed the formula as text. Then I put the equal sign back in at the beginning and the formula displayed properly. Then I was able to copy my formula edit to the rest of the range.

Should this be submitted as a bug? It does seem to be a problem with OOo Calc.

Example:
=sum(a1:a3) [shows as text]
Edit:
sum(a1:a3) [still shows as text]
Re-edit:
=sum(a1:a3) [now displays formula results properly]

[Villeroy]

I wish I could, it's a work spreadsheet and has sensitive information.

Simply remove everything except for the offending text-formulas.

I think that comes from formatting the cells with the formulas as text after creating the formulas

It's the other way round. Any input is treated literally after number format "Text" has been applied.
No formatting will ever change your values, but this special number format affects new values to come.
A text-value "123" won't be converted to number when you apply some number format.
A numeric value 123 won't be converted to text when you apply number format "Text".

Same with formulas:
A text-value "=A1" won't be converted to a formula when you apply some number format.
A formula =A1 won't be converted to text when you apply number format "Text".

I wish they would ban number format "Text" from the UI and replace it with a check box "Treat input as literal text", which disables all number formatting options while being checked.

[zcat7777]

It's the other way round. Any input is treated literally after number format "Text" has been applied.
No formatting will ever change your values, but this special number format affects new values to come.
A text-value "123" won't be converted to number when you apply some number format.
A numeric value 123 won't be converted to text when you apply number format "Text".

I see what you are saying. I thought that when you started with an equal sign Calc always considered the cell a formula.
My problem is, even when I change the change the format back to Default, I still have to re-enter the formula (easiest for me by removing the equal sign at the beginning and then re-inserting it). I see Excel works the same way, though.

I think it would make sense for cells starting with the equal sign to always be a formula unless you specify otherwise, such as beginning with a single quote, but I suppose there might be problems with that, too...

[Villeroy]

It words like most other spreadsheets work since decades.

I think it would make sense for cells starting with the equal sign to always be a formula unless you specify otherwise

Number format "Text" is the way to do so for ranges of cells, preferably through cell styles. For single cells with arbitrary number format other than "Text" you can use the quote.

My problem is, even when I change the change the format back to Default, I still have to re-enter

The "problem" is intended, thus being a feature. No formatting will ever change nor convert any of your data. No color-change, font, border, protection status nor number format will ever do this. Your data will be preserved. This is why a text-formatted value "123" will be tagged with the leading quote (showing '123 in the formula bar) after you set a numeric number format. The application adds this tag in order to prevent the conversion from text to number. Same with text-formatted string "=A1" which becomes tagged as literal string when you apply any other number format.

Try formula =PI() with number format "YYYY-MM-DD HH:MM:SS". The formatting will never change the value of pi. The displayed date-time is just another representation of the very same value and switching to number format "Text" won't change it neither.

[tommcdonald]

You guys have missed something here, that is that this problem happens after installing Open Office 3.2.1
Suddenly all my formulas turned into text! It is a disaster. I do not have the time to go in and delete the = sign then reEnter it. Can you give another fix? Like can I highlight the formula column and turn them all back into numbers and formulas? I really need help since I have a major business proposal that has quit working since the new release.

[Villeroy]

You guys have missed something here, that is that this problem happens after installing Open Office 3.2.1

No, we guys do not miss anything here since this is the first time we read about it.

menu:Edit>Find>Replace...
[More Options]
[x]Regular Expressions
[x] Search all sheets
Search within <Formulas>
Search: .+
Replace: &
[Replace All]

Replaces all content with itself.

[tommcdonald]

After I installed 3.2.1 release of Open Office all my formulas turned into text. Somehow the Preference file changed when I installed this new release. Here is the fix:
1- go to Open Office - Preferences - OpenOffice.org Calc - View
2- Under Display options you will find the check box "Formulas". Make sure this is NOT checked. If it is click on it to unCheck it.
3- Click on "OK". Now you should see all the numbers back in the spreadsheet.

[tommcdonald]

Thanks Villeroy! I will save this thread in case I need your Search-Replace idea someday.

[Villeroy]

I should add that the formulas checkbox is unchecked in the Tools>Options>OO.org Calc>View>Display dialog

Indeed, this possible cause of the problem should have been mentioned more clearly for all the readers who stumble upon this topic.
View>Highlight [Ctrl+F8] shows all formulas in green font, numbers in blue and text in black.
If you see green formulas, simply switch off this "debug view" in the view options and you will see the formula results.
If you see black formulas, they have been entered or imported as literal text. Entering/importing correct data is the only way to get rid of wrong data. No feature will turn wrong user data into right ones automatically. The find&replace tool is a helper for repetitive editing.

[pkalmonte]

Thanks so much for posting the fix to this problem. It was driving me absolutely crazy, and I so appreciate the folks on this forum who generously shared their knowledge! You have saved at least one person from pulling out all her hair.

Mahalo nui loa (thank you very much).

Aloha,

pkalmonte

[svsiemers]

I had the same problem. I opened another spreadsheet and it worked fine. So I saved the bad file and named it bad, then saved the good spreadsheet the worked right with the bad file's original name, then copied sheet by sheet from the bad file to the new one, and it worked fine. Really puzzling! So long as I copied it one sheet at a time, it worked. If I just tried to resave the whole file, it would not work.

[Georgian]

Any input is treated literally after number format "Text" has been applied.
No formatting will ever change your values, but this special number format affects new values to come.
A text-value "123" won't be converted to number when you apply some number format.
A numeric value 123 won't be converted to text when you apply number format "Text".

I had the same problem but, acting on this advice from Villeroy, I solved it I deleted the contents of the cell, changed the format to Number, replaced the contents and it's now fine!!

Thank you, Villeroy!