CSVDataWithEmbeddedFormulas.csv

Just came across your related blog post, and it saved my day! I'm working against ("with" didn't seem quite appropriate here 😉 ) Apple's Numbers spreadsheet app, and I thought I'd share my observations. Perhaps it would work with M$'s Excel, too?

The , (comma) appears to be a pivotal element, since it seems it is what prompted you to embark on the "double-quoting scary ride"? I managed to jump on a slightly less scary ride, by using ; (semi-colon) as the field separator. This allows the , (comma) to be used as the separator of function arguments, without the danger of being mistaken as the field separator. As a consequence, in Apple Numbers the formulas can be used without being surrounded by quotes, and thereby also removing the need for double-double-quoting verbatim strings in function arguments. The following, when imported as a CSV file into Apple Numbers, reads as fomrtulas as expected, and causes the expected calculations to be performed:

Column A; Column B; Column C; Formula
1;2;5;=SUM(A2:C2)
5;3;10;=A3*C3+B3
2;2;4;=COUNTIF(A:C,2)
0;0;No;=IF(and(A5=0,B5=0,C5="Yes"),1,0)
0;0;Yes;=IF(and(A6=0,B6=0,C6="Yes"),1,0)
1;0;1;=IF(AND(A7=1,B7=0),"UPDATE",IF(AND(A7=0,B7=1),"CHECK","No Change"))
0;1;1;=IF(AND(A8=1,B8=0),"UPDATE",IF(AND(A8=0,B8=1),"CHECK","No Change"))
0;0;0;=IF(AND(A9=1,B9=0),"UPDATE",IF(AND(A9=0,B9=1),"CHECK","No Change"))

I also tried pinning row and column specs (i.e. e.g. $B$9 instead of B9) in formulas, which worked as expected, too.

With Excel, as always, your mileage may vary however.

That's a great find -- thanks for sharing!