Getting Excel to interpret string as formula

March 29, 2008 11:39 AM

Getting Excel to interpret text string as formula

I'm in MS Excel 2003. I created a column of formulas by concatenating strings from other cells, so in any given cell I now have a string that looks like this:

'=SUM(E20:E31)/4*25

Note the apostrophe, b/c that is forcing Excel to interpret the cell as a text string rather than as a formula.

In older versions of Excel, I could simply globally replace = with =

Somehow, that wiped out the apostrophe. But, that doesn't work for me now.

Now, I'm stumped. Without opening each cell individually and deleting the damn apostrophe, I can't figure out how to get Excel to view these cells as formulas.

I tried to write a macro to do that, but I'm inept at macros.

Please hope me.

posted by found missing to computers & internet (7 comments total)

Try using the Text to Columns command, and use the apostrophe as the column delimiter. It should pull it out and your formulas should function.

There's probably a better way to do it, but...
posted by selfnoise at 12:10 PM on March 29 [1 favorite]

Hey, that works perfectly! You are an officer and a gentleman.
posted by found missing at 12:14 PM on March 29

Good old text to columns. One of the most useful tools in Excel along with Pivots and Vlookups that your average user isn't aware of.
posted by Octoparrot at 12:56 PM on March 29

Another option would be to globally replace "'=" with =. Note the use of the double quotes. I don't have access to Excel right now, but recollect that this worked when I had to work across multiple columns.
posted by rasputin98 at 4:40 PM on March 29

rasputin98: In the interest of science I tried your solution, but to no avail. The message I get is that "Microsoft Office Excel cannot find any data to replace."
posted by found missing at 8:29 PM on March 29

Huh. I tried Rasputin's method and it worked for me. Put '= in the find box, = in the replace box, hit "replace all".
posted by selfnoise at 1:54 PM on March 30

are you on the same version/OS as I am?
posted by found missing at 2:03 PM on March 30