Re: [libreoffice-users] Re: Formulas in text instead of result - LO Calc

At 13:41 13/09/2013 +0200, Paul Steyn wrote:
> Thanks, but as I stated in my original email, changing the cell 
> formatting doesn't do anything. I've tried a number of different 
> formats, including percent, currency, number and text, as well as 
> clearing direct formatting.

I won't guarantee this, but I think the damage is done when you enter 
a formula into a cell already formatted as Text when you have opened 
the spreadsheet from a .csv file.  What you type is then interpreted 
and stored as text.  *Changing* the format afterwards will not help: 
you need not to have had the format as Text when you typed the entry.

> That said, I did redo my experiment (for about the 10th time), but 
> this time I put the formula in a new column to the right of the 
> existing data, and it worked fine.

That would be because your new column is not formatted as 
Text.  Alternatively, what do you mean by "put" the formula?  If you 
copy the existing formula - already fossilised as text - you will get 
text again, of course.  But if you retype the formula into a 
non-text-formatted cell, won't you then obtain the formula you require?

> However, when I do as previously, and make a new column B, and put 
> the formula in there, it still doesn't work.

Hmm: perhaps that depends on how you create any new column: does it 
inherit the (troublesome) formatting of the existing column?  Or 
perhaps by "put" you again mean "paste" rather than "type"?  (These 
have different effects.)

> Actually, now that I experiment, it seems to work in any column I 
> put the formula, existing or new, provided it isn't between the 
> original first two columns.

Look at the formatting of any relevant cells before you enter any 
formula: is it Text?  Change it *first*.

> When I save it and then open it in a plain text editor, I see that 
> in the column that worked, the result is saved, but in column B the 
> formula value is saved. However, when I then open the file in LO 
> again, now both columns show the result, although the originally 
> working one is of course just a number.

When you reopen the .csv file, your text string is evidently 
reinterpreted - as if it had been typed, not pasted - so it is 
converted to the formula you want.

Note that the formatting of cells has (at least?!) two effects: how 
any stored value is displayed and how any entered (i.e. typed) input 
is interpreted.  Reopening a proper (.ods) spreadsheet file preserved 
the save cell contents, of course.  Opening a .csv file seems to 
reinterpret it as if the contents were retyped.  You could override 
this - the opposite of what you want - by selecting Text as the 
column type in the Text Import dialogue.

I trust the helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted