MrExcel.com - Your One Stop for Excel Tips & Solutions

Changing Numbers to Text

November 08, 2001 - by

Janice writes, looking for a way to change text that looks like numbers to real text. She imports data each day and since the key field in her lookup table contains numeric values, the lookup will not work. She says:

I can get around this by formatting the column the same as the lookup, but then I have to edit, replace all the cells to get the format to take. For example, I will have to edit, replace 44 with 44; then edit, replace 55 with 55. This is the only way I can get it to work.

Is there any way to get the formats to take without edit, replacing? Or is there a way to edit, replace without changing a specific cell. I've tried blanks & spaces, but it says that nothing is found.

Yes, there are a lot of ways around this. The first approach is to simply change your VLOOKUP formula a little bit. If your VLOOKUP formula is normally something like this:

=VLOOKUP(A2,$AA1:$AB100,2,FALSE)

then add a 0 to the first term:

=VLOOKUP(A2+0,$AA1:$AB100,2,FALSE)

When A2 contains text that looks like a number, adding zero to the text field will cause Excel to evaluate the text 44 as 44.

The second approach is to use the =VALUE() function. This function will convert text that looks like a number to a number. Any application of this approach is generally made obsolete by the following trick, which is the best answer to your question.

The commonly accepted trick to solve this problem is as follows:

  1. Find an out of the way cell and enter a 1 in that cell
  2. Highlight the new cell and Ctrl + C to copy the cell
  3. Highlight your range of text that looks like number in A2:A46
  4. Edit - PasteSpecial. In the 2nd section of the PasteSpecial dialog, select Multiply. Click OK

All of your text will change to numbers. I suspect that the PasteSpecial - Multiply feature was designed to multiply ranges together, but someone discovered this use for changing text to numbers and is a real time saver.

A tip of the MrExcel cap and a free Salt Lake City 2002 Olympic calendar to Mark R. who wrote in with the other solution to this problem that works very well: Highlight the entire column, select Data, Text to Columns, Finish.

This is excellent. A single mouse click to highlight the column the Alt + d + e + f, Three keystrokes and one mouse click. Excellent tip!