MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Format Existing Numbers as Text

May 29, 2003 - by Bill Jelen

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.

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.