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

Format Existing Numbers as Text

May 29, 2003 - by

Jandy asks,

Hi, I’ve searched Excel Help, the net (including MS Knowledge Base & several user groups) and your wonderful site without success – is there a way to format existing numbers as text without having to tap F2, Enter for every blessed cell? I often need to reformat hundreds of numbers but can no longer stand the boredom, not to mention the risk of repetetive stress injury.

I suspect that Jandy has a column of numbers. After she is given the spreadsheet, she formats the column as text using Format > Cells > Number > Text.

Then, by editing each cell with F2, the cell will change from numeric to text. This can be a very mundane solution to the problem.

The workaround is a little complicated, but worth learning. For this example, let’s assume that you have numbers in cells A2:A1000.

  • Insert a temporary blank column B.
  • In cell B2, enter this formula: =TEXT(A2,"0")
  • Copy the formula in B2 down to B3:B1000
  • We need to change these formula to values in order to have them become text. Highlight cells B2:B1000. Use Ctrl+C to Copy, then Edit > PasteSpecial > Values > OK. The entries in column B will now be text versions of the numbers in column A.
  • Copy column B back to Column A. Delete the temporary column A

The key to this technique is the =TEXT() function. The 2nd parameter describes how the number should be formatted before being converted to text. You may need to adjust this based on your numbers. The result of =TEXT(123.25,"0") will be 123. The result of TEXT(123.25,"0.0") will be 123.3. The result of TEXT(123.25,"0.00") will be 123.25. To always keep only the decimals as entered, use =TEXT(A2,"General").

This function is also great for converting dates to formatted dates. If you have 5/29/2003 in a cell, then using =TEXT(A2,"d mmmm, yyyy") will give you 29 May 2003.

Another method is to highlight the column of numbers and use Data > Text to Columns. Indicate that you have formatted text. In step 2, remove any column markers that may have shown up. In step 3, indicate that this column is text.