Convert Numbers Stored as Text in Excel (2 Ways to Fix All!)

Sometimes when you import (or copy and paste) numbers from a database or another data source into Excel, the values get stored as text.

This can cause big problems. The imported numbers might not be formatted consistently, and Excel can’t perform calculations if numbers are stored as text.

So… is there a way to convert numbers stored as text in an Excel spreadsheet?

I get asked this question all the time in Excel courses. Have no fear… there are a couple of ways to convert numbers stored as text to values you can work with!

How Can You Tell if Numbers Are Stored as Text?

Before we look at how to solve the problem, how do you know that numbers in your spreadsheet are stored as text?

You’ll usually see one or two indications that numbers are stored as text. Look at the screenshot (below). Something seems a little off.

Here’s what you need to look for.

First, the numbers in D2:D3 are left aligned. Values are usually right aligned.

Second, the total in cell D6 only includes values from D4:D5. The sum function encompasses all of the numbers from column D but since the numbers in D2:D3 are stored as text, they’re not included in the total.

While both of these observations tell you that the spreadsheet includes numbers stored as text, the most obvious indicator might be the green triangles in D2:D3.

You can ignore the errors or you can convert numbers stored as text to values you can work with.

Read on for two ways to convert values stored as text.

Method #1: Convert Numbers Stored as Text with Error Checking

A green triangle indicates that there is an error in the formula within that cell. When you select a cell with a green triangle, the Error button appears.

Using the screenshot below as an example, here’s how to convert these numbers stored as text to values.

1. Drag to select D2:D3 then click the Error button.
2. From the drop-down menu, select Convert to Number.

That’s it… problem solved!

Method #2: Convert Numbers Stored as Text with the Format Cells Dialog Box

If the green triangles aren’t visible but you suspect the numbers in your spreadsheet are stored as text, you can use Excel’s ISTEXT function to identify numbers that are stored as text.

Here are the steps, using the spreadsheet (below) as an example.

1. Click in cell E2 and type =IsText(D2) then press Enter.
2. Copy the function to the remaining cells in column E.

If a number has been stored as text, the function will return a result of TRUE. If the cell is formatted as a number, it will say FALSE.

In this example, you can tell that cells D2:D3 contain numbers stored as text because the ISTEXT function returned a result of TRUE for those cells.

To convert numbers stored as text to values using the Format Cells dialog box, do the following.

1. Drag to select D2:D5.
2. Click the dialog box launcher in the Home > Number group (or press Ctrl + Shift + F).
3. On the Number tab, select Number from the Category: box then click OK.

Strictly speaking, you don’t really need to use the ISTEXT function to verify that there are numbers stored as text. You just need to highlight all of the numbers and apply Number formatting to them.

Even if none of the numbers were stored as text, this step doesn’t hurt. At the very least, you’d be certain that all of your numbers were formatted consistently!