Convert numbers stored as text to numbers in Excel
Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel Web App

Numbers that are stored as text can cause unexpected results, like an uncalculated formula showing instead of a result.   

test 

Most of the time, Excel will recognize this, and you'll see an alert next to the cell where numbers are being stored as text. If you see the alert: 

  1. Select the cells you want to convert, and then select the error notification  test.test

  2. Select Convert to Number from the menu.test

  3. You will see the values left aligned and green triangle warning removed.Image of the worksheet with the values left aligned and green triangle warning removed

Check out Format numbers to learn more about formatting numbers and text in Excel.

Notes: If the alert button is not available, you can enable error alerts by doing the following:

  1. In Excel, select File, then Options.

  2. Select Formulas, then under Error Checking, select the Enable background error checking check box.

Use a formula to convert from text to numbers

You can use the VALUE function to return just the numeric value of the text.

  1. Insert a new column next to the cells with text.Insert a new column in Excel

    In this example, column E contains the text stored as numbers. Column F is the new column.

  2. In one of the cells of the new column, type =VALUE() and inside the parentheses, type a cell reference that contains text stored as numbers.test In this example it's cell E23.

  3. Next, fill the cell's formula down, into the other cells. To do this, hover your cursor on the lower-right corner of the cell until it changes to a plus sign.Rest your cursor here.

  4. Click and drag down to fill the formula to the other cells. You can now use this new column, or you can copy and paste these new values to the original column.test

    Notes: To copy and paste these new values to the original column

    1. Select the cells with the new formula.

    2. Press Ctrl + C. Select the first cell of the original column.

    3. On the Home tab, select the arrow below Paste, and then select Paste Special > Values ​​​​​​​or use the keyboard shortcut Ctrl + Shift + V.

    ​​​​​​​

Numbers that are stored as text can cause unexpected results, like an uncalculated formula showing instead of a result.

test

Use a formula to convert from text to numbers

You can use the VALUE function to return just the numeric value of the text.

  1. Insert a new column Insert a new column in Excel

    Insert a new column next to the cells with text. In this example, column E contains the text stored as numbers. Column F is the new column.

  2. Use the VALUE function test In one of the cells of the new column, type =VALUE() and inside the parentheses, type a cell reference that contains text stored as numbers. In this example it's cell E23.

  3. Rest your cursor here Rest your cursor here.

    Next, fill the cell's formula down, into the other cells.

    To do this, rest your cursor on the lower-right corner of the cell until it changes to a plus sign.

  4. Click and drag down test

Click and drag down to fill the formula to the other cells. You can now use this new column, or you can copy and paste these new values to the original column.

To do this:

  1. Select the cells with the new formula.

  2. Press Ctrl + C. Select the first cell of the original column.

  3. On the Home tab, select the arrow below Paste, and then select Paste Special > Values. or use the keyboard shortcut Ctrl+ Shift + V.

Related topics

Replace a formula with its result Top ten ways to clean your data CLEAN function

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.