12

I have a large spreadsheet in which many of the cells contain formulas.

How can I produce a delimited-text version of this spreadsheet such that all formulas appear as the text of their source code?

For example, for a spreadsheet in which column A is

14
59
27

cell B1 is set to =SUM(A:A), and all remaining cells are empty, if we did "Save As" CSV, the resulting *.csv file would look like this (last two cells of second column are empty):

14,100
59,
27,

In contrast, what I want would look this:

14,=SUM(A:A)
59,
27,

In fact, the non-formula values are unimportant (except as placeholders), so the alternative below would be fine too. Here the only non-empty cells are those corresponding to the formula-containing cells in the original sheet; in this case, the leading '=' can can be omitted from the non-empty cells:

,SUM(A:A)
,
,

Thanks!

PS. I'm VBA-illiterate, so I'd like to avoid it if at all possible.

2 Answers 2

15

I just tested it and it's a lot easier than I thought to get output like the 2nd case you specified.

Do the following:

  1. Input all your data/formulas
  2. Select the Show Formulas button (via Formulas > Formula Auditing in Excel 2007)
  3. Save As filetype CSV(MS-DOS)
  4. Open resultant file in Notepad to verify.

I'll play around with it for a bit more to see if I can get it to pare down the formulas like you mentioned in case 3.

3

The same applies for LibreOffice Calc (Version: 6.0.6.2):

  1. Show formulas (Ctrl+`),
  2. Select "Save as" (Shift+Ctrl+S),
  3. Select "Text CSV (.csv)" from the list
  4. Tick "Save cell formulas instead of calculated values"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .