4 quick and easy ways to convert numbers stored as text to real numbers in Excel

Numbers stored as text in Microsoft Excel aren’t necessarily wrong. For instance, a street address that begins with numbers—555 Town Street—is stored as text. On the other hand, we often receive data from a foreign source that should be stored as a number, but it stored as text at the source. When this happens, we must convert that text into numbers before we can use the data.

SEE: 83 Excel tips every user should master (TechRepublic)

In this Microsoft Excel tutorial, I’ll show you four easy ways to convert numbers stored as text to real numbers. All four methods have their own pros and cons, so knowing them all will come in handy.

I’m using Microsoft 365 on a Windows 10 64-bit system. I recommend that you hold off on upgrading to Windows 11 until all the kinks are worked out. For your convenience, you can download the demonstration .xlsx and .xls files. The first method doesn’t work in Excel Online, but the other three do.

1. Use SmartTag

Figure A shows a short list of text values. You can tell right away that they’re text because the numbers are left aligned, and they have leading zeros. If you check the Format setting in the Numbers group (on the Home tab), you can see that these values are text and not stored as real numbers. I set the Text format myself to simulate the problem. You can do so by selecting a small range and choose Text from the Number Format dropdown in the Number group.

Figure A

For my money, I like the SmartTag method the most because it converts the text values in place. The only real disadvantage is that selecting a long list of values might be a bit awkward, so I’ll show you a shortcut to make that easier.

To select the values quickly, select the first cell (B2) and press Ctrl + Shift+ Down Arrow. Doing so will select all the contiguous values. (You must accommodate any blanks in the range.)

With the values selected, click one of the SmartTag indicators—the green triangle in the top-left corner of the cell—and do the following:

1. Click the resulting SmartTag (if the SmartTag is already visible, click it without clicking the triangle). Doing so displays a list of possible problems, as you can see in Figure B. These SmartTags are indeed “smart.” The first item tells you that the number is stored as text—that’s the problem. The next item is the solution: Convert to Number.
2. Select the second item, Convert to Number, to instantly convert all selected values to real numbers, and in place, as shown in Figure C.

Figure B

Figure C

For better or worse, the conversion converts the values to General, not Number.

With this easy method mentioned first, you might be wondering why I’m going to show you more. Being able to select all the values at once is fairly new—originally, you couldn’t convert them all at once. So, if you’re using an older version, you need another solution. In addition, there’s no one-size-fits-all-situations solution.

2. Text to Columns

The Text to Columns feature is incredibly easy. It leaves the values in place and removes the leading zeros, but the values remain text, so you have to reset the format. Select the values as you did before and then do the following:

1. Click the Data tab and then click Text to Columns in the Data Tools group. Doing so launches a wizard.
2. In the first pane, click the Fixed Width option—there are no delimiters.
3. Click Finish—it’s that quick.

Unfortunately, the values are still stored as text. With the values selected do the following:

1. Click the Home tab.
2. From the Number Format dropdown (in the Number group), choose Number, as shown in Figure D. This one tacks on a few decimal places, which you might not want.
3. If you don’t want those decimal places, choose More Number Formats from the dropdown.
4. Choose Number in the Category list.
5. Enter 0 in the Decimal Places option.
6. Click OK to format the selected values.

Figure D

This one takes a bit of work. You’d think you could change only the format, right? Unfortunately, it doesn’t work; you can change the format, but it leaves the leading zeros.

It’s possible that you might need to retain the source values, so the next two methods involve an expression and a function.

3. An expression

It isn’t unusual to need to retain the source data. When that’s the case, you won’t want to convert the original values in place. That’s when a simple expression such as

=textvalue * 1

comes in handy. Figure E shows the result of entering the expression

=B2*1

into cell C2 and copying it to the remaining cells.

Figure E

As you can see, the resulting values have no leading zeros, but are still stored as text, so do the following to change the format to Number:

1. Click the Home tab.
2. From the Number Format dropdown (in the Number group), choose Number. This one tacks on a few decimal places, which you might not want.
3. If you don’t want those decimal places, choose More Number Formats from the dropdown.
4. Choose Number in the Category list.
5. Enter 0 in the Decimal Places option.
6. Click OK to format the selected values.

If you’re thinking that there ought to be a function that will do both, there is.

4. Value()

You can skip a step by using the VALUE() function because it not only removes the leading zeros, but formats the resulting values as General, which has no specific format. You can go the extra step (shown above) to format the resulting values as Number, but in many cases, it won’t be necessary.

Figure F shows the result of entering

=VALUE(B2)

it to C2 and copying the function to the remaining cells.

Figure F

These four methods provide quick and easy ways to convert text to real numbers. Knowing your data will help you decide which to use. You might use them all at some time or another.