Categories
Excel

Convert Text to Numbers in Excel (3 Ways)

Sometimes when you import – or copy and paste – numbers from 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 on numbers formatted as text.

So… is there a way to convert text to numbers in Excel?

I get asked this question all the time in our Excel courses. Have no fear… you’ve come to the right place for help converting numbers formatted as text to values you can work with!

The Problem with Numeric Values 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?

Numbers stored as text

In the screenshot (above), there are some signs that numeric values are stored as text:

  • 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 adds all of the numbers from column D but since the numbers in D2:D3 are formatted as text, they’re not part in the total.

While both of these conditions 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 3 ways to convert text to numbers in Excel.

1) Convert Numbers Stored as Text with the Error Indicator

Excel error checking feature displays a green triangle in a cell any time there is an error in a formula. When you select a cell with a green triangle, the Error button appears.

Here’s how to convert text values to numbers with the error indicator:

  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!

Convert to Number

2) Use the Excel Value Function to Convert Text to Numbers

You can use the VALUE function to convert text to numbers in Excel. Here’s an example:

  1. Click in an adjacent cell, type =VALUE(D2), then press Enter.
    The text value from cell D2 is converted to a number.
  2. Copy the formula in cell F2 to F3:F5.
Value Function
  1. Copy the values in F2:F5 and overwrite the figures in column D by selecting cell D2 and pressing Ctrl + Shift + V to paste values.

3) Convert Numeric Values Stored as Text With Text to Columns

Excel’s Text to Columns feature is typically used to separate date stored in one column to additional columns but it can also be used to convert a single column of text to numbers.

  1. Select a column in your spreadsheet.
  2. Click the Text to Columns button in the Data > Data Tools group.
  3. Click Finish.

After converting text to numbers you may need to use the Format Cells dialog box so all of the numeric values are formatted consistently.

  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.
The Format Cells dialog box

Even if some of the numbers were already formatted or the range contained a blank cell, this step will ensure that a consistent cell format has been applied.

The Best Way to Convert Text to Numbers in Excel

So what is the fastest text to number conversion? Should you just using number formats or is an Excel function better?

I would use the simplest method. If you can highlight cells with a green triangle and easily convert text to numbers, that’s obviously the best option.

Using Text to Columns to convert text to a number is another option, and it’s nice to know you can leverage the Excel VALUE function if you need to.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.