Data Designs Publishing

Converting Numbers Formatted as Text Back to Being Formatted as Numbers

Have you ever imported data or received a spreadsheet from someone in which you have a column of numbers that is formatted as text? It's pretty irrelevant until you try to apply a mathematical formula to it. Nothing happens. First, you have to convert the data from being formatted as text to being formatted as a number.

No problem, right? The logical thing to do would be select the column, right click, select "Format Cells..." and then apply a number style. Yeah, that's the logical thing to do. It doesn't work. Your data will still be formatted as text. You see, you've told the spreadsheet to format the cell as a number, but the spreadsheet still thinks that the data in the cell is text! (Can you hear the scarecrow singing "If I only had a brain?")

Well, there are several ways to fix the problem, but both start with the above procedure. Let me repeat it here:

Step One - Reformat the Cells

• • Select the column
• • Right click and select "Format Cells..."
• • Click on the "Number" tab if it's not selected
• • Select "Number" from the list at the left
• • Select the style you want and the number of decimals on the right side of the dialog box

Step Two - Reformat the Data (For Short Columns of Data)

• • Select the first cell with data in it
• • Hit the F2 key - this places the cursor at the end of the data in the cell
• • Hit the enter key - this enters the (non)changes you made and advances the cursor to the next cell

Voila! Excel now recognizes the data in the cell as a number.

If you have a short column of numbers, you can get in a pretty fast rhythm - F2 with the left hand, Enter with the right hand, F2 with the left hand, Enter with the right hand, etc.

But if your column of data is long, that's pretty tedious. Try a different Step Two!

Step Two - Reformat the Data (For Long Columns of Data)

• • Type the number 1 in any cell formatted as a number
• • Select the cell and copy (^c) it
• • Select the column of data or specific cells you want to reformat
• • Right click and select "Paste Special" (or hit alt-E, then S)
• • Select "Multiply" from the "Operation" area of the dialog box (it's in the center)

Whala! Excel now recognizes the data in the cells as numbers. Oh, you can delete that number 1 you typed in any cell

Trust me, the process is much simpler than explaining it is! You'll have your poorly formatted numbers reformatted as numbers in no time. You're welcome!