Getting Excel to interpret string as formula
March 29, 2008 11:39 AM Subscribe
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.
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.
Response by poster: Hey, that works perfectly! You are an officer and a gentleman.
posted by found missing at 12:14 PM on March 29, 2008
posted by found missing at 12:14 PM on March 29, 2008
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, 2008
posted by Octoparrot at 12:56 PM on March 29, 2008
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, 2008
posted by rasputin98 at 4:40 PM on March 29, 2008
Response by poster: 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, 2008
posted by found missing at 8:29 PM on March 29, 2008
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, 2008
posted by selfnoise at 1:54 PM on March 30, 2008
Response by poster: are you on the same version/OS as I am?
posted by found missing at 2:03 PM on March 30, 2008
posted by found missing at 2:03 PM on March 30, 2008
This thread is closed to new comments.
There's probably a better way to do it, but...
posted by selfnoise at 12:10 PM on March 29, 2008 [1 favorite]