Friday, January 17, 2014

Numbers as text??

I was asked the other day about data exported from an access data base where the numbers where displayed as text values in Excel.  If you have ever run into this, you know that it's not a super simple task to change the format of those cells to "Number", because after you change the format type of the cells to "Number", you then have to activate the cell (F2) and exit the cell (Enter/Tab) for every cell.  So how do you get around this?

Let's use this example below:

Column A has Ages that are in a text format (notice the green triangle at the top-left of each cell - if you hover the cursor over this, excel will display a message that the numbers are in text format)

Simply add a new column next to the column you're working on (column B). In cell B2, type the formula "=A2*1".  Auto-fill or copy the formula down the column.

The result of the formula will provide you with the numeric format of the age.  Now you can simply copy the values in column B and "Paste Special > Values Only" in column A.  The age column values are now Numeric and no longer Text.

Delete the new column (Column B) to restore your data to it's original structure.

No comments:

Post a Comment