Excel has a Text format that can be applied to cells. When a cell is formatted as Text, anything you type in the cell will appears in the cell. That may seem obvious, but it applies to numbers and formulas too. For instance, if you type a formula in a cell formatted as Text, you see the formula rather than the result.
To remedy this, change the format to General (or something else), go into Edit mode in that cell, then exit the cell. Changing the format alone won’t fix the problem, you have to re-enter the cell. The easiest way to do this is to press F2 then Enter. F2 to edit the cell and Enter to commit the changes, even though you didn’t make any changes. This causes Excel to reevaluate the cell and show the proper result.
Kathy, however, does it differently. Instead of F2/Enter, she uses Data – Text to Columns, which also causes Excel to reevaluate the cell contents and formatting. When I asked her why, she said that while F2/Enter may be faster for one or two cells, TTC is better for a larger range.
By using Data – Text to Columns and specifying a delimiter that doesn’t exist (in this case, Tab is the delimiter and there are no tabs in the cells), each cell is written back to itself.
If you have a large range of formulas in Text formatted cells, remember the Text to Columns trick.
For formulas I prefer to select the range and then Replace all equal signs to equal signs (Ctrl-H etc).
Good tip, but I wonder how someone could get into a predicament that requires this tip. When you type a formula and you see the formula rather than the result, you know something is wrong. So you don’t keep doing it, and you don’t copy the formula until you fix the problem.
You can do this on purpose. Suppose you need to have a series of formulas like this:
=IF(Sheet1!A55=”X”,C1,D1)
…
thru
=IF(Sheet25!A55=”X”,C1,D1).
Obviously, filling down won’t work. You can create the same effect by using INDIRECT, but you can also get the simpler formulas without indirect by building the formula elsewhere, like this:
=”=IF(Sheet”&ROW(A1)&”!A55=””X””,C1,D1)”
and filling it down 25 rows. Then copy/paste special values, but you’ll still have the text version of the formula. Then either replace = with = or data/text-to-columns.
Another option:
1. Reformat the cells in the range as the number format you want. This will not convert the formula, but it does allow the next step to work.
2. Do a “Find and Replace All” on the range, changing all “=” to “=” (just because that will exist in any formula). EXCEL then changes the cell contents to be the formula instead of being text.
For an odd result, try skipping step 1. You end up with the results of the formulas, left-justified and looking like text. However, if you do an F2/Enter, it returns to the text display version of the formula. At least in EXCEL 2007. I would call that a bug, actually.
As Bob says, such “text formulas” can be pretty useful for things like:
-Creating links to external sheets or workbooks,
-Simplifying formulas and speeding up recalculation (in place of indirect/offset)
-Navigating a hierarchy (eg 1,2,3,4,2,3,4,…) by creating formulas that refer to the parent rows and then using shortcut keys: ctrl + [ ,{, }, or ] to select rows.
Edit|Replace “=” with “=” also works for evaluating them. It has the advantage of working on more than one column but you need to change the number format after. Another difference is that Edit-replace will try to complete the formula with missing brackets if possible and will prompt you at the first invalid formula in the selection whereas text-to-columns converts all formulas and ignores invalid ones. If using on external references this can be troublesome as you can get stuck in a repeated loop of prompts.
A second alternative is to format the number as general and then copy and paste the range by activating the office clipboard. Choose paste-special as text after if necessary to evaluate (which is the default method for larger ranges anyway). This method can be quicker as you don’t need to copy-paste values before. A similar comment is in “numbers stored as text”. lhm
John,
Do you think the average user has gotten smarter, or at least more observant, since you left the corporate world? Let’s face it, you’re going to be writing Bibles for the next 50 years.
Anyway, the correct answer for the average user is to click the little green triangle in the cell and select the convert to numbers option. I would venture to guess that answer is too obvious for anyone here because turning off error checking was second on the list of changes to make on a new Excel install (it comes right in between disabling macro security at #1 and turning off that ridiculous getpivotdata function at #3).
I enjoyed seeing this usage of Data Text To Columns — especially since it uses Tab as the delimiter which will not mess up your Excel session therafter (whenever any other delimiter is used it thereafter affects all pasted data, seemingly).
Isn’t it Replace “=” to “=” does not work when formulas are longer than 256 characters?
For that matter, I find it impossible to read (in the sheet) a formula in a Text-formatted cell which is longer than 255 characters. I can see it in the formula bar interestingly enough.
– select an empty cell and write the number ‘1’
– copy the cell
– select the range that you want to be revaluated by excel
– use ‘paste special’ and select ‘multiply’
All cells in the range is being mulitiplied by ‘1’ and thereby revaluated.
Thomas –
Even better is to select an empty cell, and use Paste Special – Add. This way you don’t have to clutter up a cell with “1?. But these paste special tips are for regular numeric cells that are formatted as text, while the topic of the post is formulas in cells that are formatted as text.
Hi!
I have a spreadsheet that gives me the employee number (a unique identifier), the name of the employee, and the supervisor’s name. Now, its easy to find out the number of people reporting in to every supervisor. However, if I want to find out the span of control of a supervisor, how do I go about finding that? E.g. Emp #111 & #222 report to Emp #333; Emp #333, in turn, reports to Emp #444, ergo, Emp #444’s span of control is 3 (Emp #333 and the employees reporting to #333 i.e. #111 and #222). Any assistance will be greatly appreciated.
Thanks!
@Split Infinitive –
One way is to add another column that links to the row containing the manager:
_A_ _B_ _C_
1| 444 444 =1
2| 333 444 =1+C1
3| 222 333 =1+C2
4| 111 333 =1+C2
Assume Employee no. in column A and Manager is in Column B.
Put C1=1 and C2=”=1+C”&MATCH(B2,A:A,0) and fill down as shown in the table.
Then copy/paste special values and evaluate the resulting formulas in column C using one of the methods described above.
You can then select C1 and press Ctrl+Shift+} (Goto|Special|Dependents) to get rows of all employees refering to C1.
I know I’m late to the party here, but I wanted to point out that when using “Text to Columns”, it is possible (and, in this case, desirable) to uncheck all “Delimiters”. Seems counter-intuitive that they would allow this, but it works. Aside from the desired recalculation, it is essentially a no-op.
I also use this trick to “turn off” automatic Text to Columns when pasting text into a spreadsheet after I have already used Text to Columns. Before learning this trick, I used to exit excel to turn off the automatic text to columns “feature”.
Kathy your trick with TTC is a lifesaver!!!
thank you for posting this tip. Very useful!
Another solution that I have found to work for Macintosh, and I’m sure for Microsoft as well;
Copy the desired cells into either TextEdit or Notepad for Mac and PC, respectively. Convert to plain text. Re-copy the plain text and paste it into excel.
This should convert all of the “unconvertible” to formidable numbers.
Best,
Kevin