Values Stored as Text

One of my work colleagues is driving me nuts! On an almost daily basis he drags me over to his desk to correct the exact same problem, he thinks his VLOOKUP isn’t working properly but it is always the case, that in one sheet, his data is stored as text.

I can see the problem from across the room as his spreadsheet is littered with little green arrows informing him “value stored as text”!!!!! The exclamation marks are mine but by golly they are needed. I go across, convert his dodgy column to values and Bobs your Uncle, the VLOOKUP starts spewing out the answer.

This is going to keep rearing its ugly head for him, it seems to be an occupational hazard that data from certain sources will be interpreted as text by Excel. My problem is how to find the simplest technique of converting all these figures back to values so that he can start doing it himself. These are all the tricks I use:

1. Insert an additional (temporary) column to the right of the column containing the values formatted as text. Enter the formula =VALUE(cell ref). Copy this down to the bottom so you have a new column containing the text as values. Copy the new column and paste over the dodgy column using Paste Special As Values. Delete the temporary column. This is my method but my colleague isn’t loving it.

2. Enter the value 1 in an empty cell, select and copy. Now select the range of cells to convert to numbers. Choose Paste Special from the Edit menu, check the Multiply radio buttonand click on OK. This automatically forces Excel to replace the contents of the cell with the numerical equivalent of the text that was previously there. Quite a neat trick I think, perhaps I will try him with this one.

3. Another method is to use the Text to Columns option in the Data menu. This throws up the sort of dialog box used when importing text or csv type data. It works but you have to be careful if your column to be converted has any extraneous spaces or tabs which may result in data to the right of the affected column to be overwritten.

4. Final tip. The liitle green triangles in the top left of the cell, inform you of the format error. They also offer the option of “Convert to Number”. You could go through 1 by 1 clicking this option or if you are a bit nifty you can highlight the affected figures, hover over the error message and then select the “Convert to Number” option. This is definitely the easiest option but I often find it a bit fiddly to get the message to appear.

Value stored as text

You may also like

  • Ronald Hensbergen

    It sounds like your colleague doesn’t know Excel much, so making the formula a bit harder make much difference… You could replace the formula probably with something like this:
    =IF(ISERROR(VLOOKUP(A1+0,$A$6:$B$7,2,FALSE)),VLOOKUP(A1&””,$A$6:$B$7,2,FALSE),VLOOKUP(A1+0,$A$6:$B$7,2,FALSE))

  • Scott Ellern

    I have enjoyed and learned from your examples. Now I think I can offer a simple solution to you.
    Use the Format Painter.
    Start out by finding (or creating) a cell that is formatted as a number (or however you want it). Select the cell and click the little dust brush button on the tool bar. Finally select the cell or column you want to be formatted.
    Note: the format will be transferred to only the first cell or range you select. You can double click the dust brush to keep the effect active till you click it off.

  • Thanks for that, I didn’t know about the dble click thing with format painter and its already proved useful today.