Converting Text-Formatted Numbers

Converting Text-Formatted Numbers
You may find that sometimes after you import or copy data from a database or other external source, the numbers in your worksheet are actually stored in cells as text. This can cause problems with calculations and sorting, among other things.

A telltale sign that a number is stored as text is if the numbers are left-aligned in the column instead of right-aligned.

There are several approaches to converting text-formatted numbers to numeric data. My personal favorite is using Text to Columns.

Text to Columns:

1) Select the column of text-formatted data;

2) From the Data tab, click Text to Columns in the Data Tools group;

3) Assuming that the column containing the data is not formatted as Text and you haven’t recently changed the Delimiters in your Text to Columns wizard, at this point you can simply click the Finish button to force Excel to recognize the text-formatted numbers to actual numbers;

If desired, you can now apply a number format to your numbers to change the way they appear in the cells.

Converting Text-Formatted Numbers


Error Checking:

I’ve always found those small green triangles that sometimes appear in the upper-left corner of cells to be an annoyance. They are actually Error Checking indicators.

Sometimes when numbers are entered into cells that are formatted as Text, these small green triangles appear. Since most times I just ignore them, I didn’t realize that they may contain a simple solution to converting text to numbers.

When you select a cell containing one of these small triangles an error icon appears. Point to the icon and you’ll see a small dropdown arrow. When you click on the arrow, one of the options is Convert to Number. Clicking it converts the numbers formatted as text back to numbers. A simple solution that I’ve always overlooked. Now you don’t have to.

Paste Special:

Another option for converting text-formatted values to numbers is to multiply each cell by 1 in order to force the conversion to regular numbers. Since you are multiplying the contents of the cells by 1, the numbers still look the same, however, Excel actually replaces the text-based contents of the cell with a numerical equivalent.

To use this technique…

1) In a blank cell type 1 and press Enter;

2) Press CTRL+C to copy the value 1;

3) Select the cells that contain the numbers stored as text that you want to convert;

4) Press CTRL+ALT+V to open the Paste Special dialog;

5) Select the Multiply option and click OK;

6) Go back and delete the contents of the cell where you typed 1 in the first step.

Leave a Reply

Your email address will not be published. Required fields are marked *