quote-equalSign convention gone from Calc 7.3.2.2 on Linux?

[mfoley]

I’m pretty certain the convention when populating cells to preserve leading zeros: =“01234” used to work in Calc for Linux. I don’t know when that disappeared, but my version 7.3.2.2 now shows that raw string, with equals and quotes, when opened. Likewise on Calc for Windows 7.3.1.3. I know for certain that Calc for Windows 7.1 behaved as expected: just showed the number with leading zero(s) and w/o the =" bit (I know, I just tried it).

Was support of this convention dropped in 7.3? Is it a setting? If not, how to open csv files with columns having leading zeros I want to preserve? Note that Excel 365 still supports this convention.

[EarnestAl]

Is there a functional difference between that and just preceding the digits with an apostrophe? They both seem to format as Text.

The Text Import dialogue gives you the possibility to set a format for each column, in this case Text. The date is good for disambiguation with US date vs rest-of-world date

Note that the box Detect special numbers is useful to have ticked, especially if importing currency.

[Villeroy]

@EarnestAl

They both seem to format as Text.

I think we should distinguish between “formatting” and data types. “Formatting” can display the exact same values in different ways with colours, borders, special fonts, sizes, with or without currency symbol, as decimal, as integer, as date or time. It remains the same value no matter which formatting attribute you change.
A text value 0123 is a completely different value than its numeric counterpart 0123 which is 123 formatted as 4-digit number.

[Villeroy]

If the cell is formatted with number format “Text” (number format code @) before data entry, any input will be taken as literal text. =SUM(A1:B99) will not be interpreted as a formula and 0123 is a text of 4 characters with no arithmetic value. It is not possible to change the data type of a cell by application of some number format.
If the cell has any number format other than @, a leading apostrophe can suppress numeric evaluation.
'0123 yields the 4 character text 0123. The leading apostrophe is not part of the text.
However '=SUM(A1:B99) enters the text including the leading apostrophe. IMHO, this is a bug.
Entering a formula like =“0123” or ="=SUM(A1:B99)" is a 3rd method to enter literal strings without evaluation.

[mikekaganski]

@mfoley

my version 7.3.2.2 now shows that raw string, with equals and quotes, when opened

It means that you either have View|Show Formula active, or the cells in question were formatted as text before you entered the formula (and having the cells formatted as text prior to entry disallows Calc from converting your entry into the formula, which is what you want in this case), as @Villeroy explained.

[mfoley]

When the cell value has '0123 or @0123, it shows those actual values. Subsequently formatting that column as Text makes no difference. I do not have View|Show formula set. When you (mikekaganski) say, “or the cells in question were formatted as text before you entered the formula”, that’s not the way it’s working. I have a completely different program generating a csv file that is subsequently opened by Calc. I do not first have a spreadsheet into which I then enter cell data, so I can’t pre-format the column to text.

When importing, I can set the column to Text, as EarnestAl illustrates, and that does serve to preserve leading zeros. However, several people can run the programs that generate the CSV file and there are often several such columns. The =“0123” convention did not require the user to pre-designate the datatype for each such column manually. Also, some (probably most) people open with Excel where this convention still works.

Like I said, the =“01234” convention used to work up until 7.3. Too bad LibreOffice removed it (never a good idea to remove a feature). Any solution?

[mikekaganski]

@mfoley

Too bad LibreOffice removed it

Who told you it did?

1. Do you say that you have a CSV with the ="0123" data inside?
2. Do you make sure that the CSV dialog in 7.3 has “Evaluate formulas” enabled?

@mfoley

that’s not the way it’s working.

If what I ask above is your case (you missed the new option in the dialog), then it is exactly the way it’s working: the cells are pre-formatted as text, just not by you consciously.

[mfoley]

Yes - I did miss the new option. If I do check ‘Evaluate Formulas’ it does work as I expect and I see numbers with leading zeros in the spreadsheet. When I save to an .xlsx file, that formatting persists. Great! Thanks! I’ve just checked again and this is described in the Release Notes under “A new Evaluate formulas option”, but it’s application to my case wasn’t obvious. So, I assume that the pre-7.3 versions did not have this checkbox, but previous versions must have done this “evaluate formula” by default. If they have kept that default behavior in the 7.3 release I wouldn’t have posted this question. Anyway, thanks for the solution. I can keep using Calc w/o worries!

[mikekaganski]

If they have kept that default setting in the 7.3 release

If you check the release notes, they mention tdf#114878, which considers the pre-existing automatic evaluation of formulas in CSV to be a security risk. So having such an option disabled in new versions is reasonable for such cases.

“it’s application to my case wasn’t obvious” is about the fundamental concept that equal sign starts formulas is spreadsheets.