is a CSV with equal sign valid?

Question

I just got a CSV input file to be processed, which has an equal-sign before the first delimiting quote, and wondered if this is valid and has any purpose. Example (simplified):

"2"
"3"
="4"

After reading some postings like this one I experimented with a CSV like this:

"2"
"3"
="A1+A2"

and:

"2"
"3"
"=A1+A2"

It seems that both Excel and LibreOffice silently ignore the equal-sign before the quote, and nicely treat the equal-sign after the quote as the flag for a formula. However, I could not find any documentation about this.

(For Excel, this CSV needs to be saved with the .txt extension, and opened with control-O)

I am inclined to call the CSV with equal-sign before the open quote as an error that is easy to deal with when reading this file, but still wondering if there is more to say about this.

Answer 1

This is used by Excel to avoid the loss of leading zero's.

For example, if you have a field in your csv file like this: 0123456, Excel will treat it as a number and lose the leading zero.

Saving it as ="0123456" solves this problem.

Using "0123456" won't help either, because quotes are not there to indicate a text field, but to escape possible delimiters inside fields.

Just like having sep=; on the first line to make Excel use the right seperator, the ="" is also 'non-standard', or better: Excel specific, because there is no real standard for csv files.

Answer 2

Excel isn't ignoring = in ="4" or ="A1 + A2", it is treating it as a constant formula.

If you open the csv file that looks like:

"2"
"3"
="4"
="A1+A2"
"=A1+A2"

in Excel the result looks like:

Note how A3 holds the formula ="4" rather than just the number 4.