Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.