Fix Numbers Stored as Text in Excel

As already noted, a lot of problems that people come across in Excel are related to formatting. One of the most common issues is when Excel keeps your numbers stored as text, or when you download data and it simply will not convert to numbers. This tends to be a large issue, as you cannot type formulas with this format.

In the example below, we will show how to resolve this issue.

Fix Numbers Stored as Text by Converting Range

If we happen to copy some data in our sheet, or we have our numbers formatted as text for any other reason, Excel will usually tell us so by displaying an error message in a triangle. These cells will also have a little green triangle in a top left corner of a cell, as shown in our example below:

A picture containing chart

Description automatically generated

When we hover over the triangle, we will see a couple of options that Excel provides us with:

Graphical user interface, application, table

Description automatically generated

There are many options, such as Ignore Error, Edit in Formula Bar, etc. but to change the formatting of the first cell, we would have to click simply on the Convert to Number option. However, that would only apply the changes to one cell. This would make the process of changing the entire range pretty painful.

What we need to do to change the entire scope is to select it, and then click on this option on any triangle at our disposal:

Graphical user interface, application

Description automatically generated

Once we click on this option, we should have our cells formatted to numbers:

Table

Description automatically generated with medium confidence

Fix Numbers Stored as Text with Multiplier

Sometimes, however, we will not have the triangles (nor the green one nor the yellow one) that could be seen in the first example as an indicator that we have an error.

Table

Description automatically generated with medium confidence

If we would go on and click any of these numbers, we could easily inspect that they are formatted as Text. What we need to do now is input number 1 in any blank cell (make sure that that cell is not formatted as text!). We will store ours in cell H2 and then right-click on it, and copy this cell. Once we do, we will select the entire range that we want to format, right-click and then go to Paste Special option as in the picture below:

Graphical user interface, application

Description automatically generated

On the window that appears, we will choose the Multiply under Operations:

Graphical user interface, application

Description automatically generated

We will click OK and our range will be formatted:

A screenshot of a phone

Description automatically generated with medium confidence

When we use a multiplicator, that makes the contents of our cells transform into their numerical values.

Fix Numbers Stored as Text with Text to Columns

The options that have been presented above are good and practical when dealing with smaller ranges. The problem that might happen with dealing with big data is that Excel can take longer to process, and it will, in some instances, crash. Luckily, there is a simple solution to this problem as well.

Suppose that we have the following range of numbers:

Graphical user interface, application, table

Description automatically generated

You will notice that our range is formatted as General, not Text but we still somehow have the same issue.

To resolve the issue, all you need to do is select the range, then go to Data >> Text to Columns:

When we click on it, we will choose Next for the first two options, and then simply click Finish. We do not need to change anything in our steps.

Graphical user interface, application

Description automatically generated

The result is as follows:

A screenshot of a phone

Description automatically generated with medium confidence

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.