How to Convert Text to Number in Excel

Many times, like when we copy data from another program, or a text file, or online, Excel may store the numbers as text. If you try to use those text values in calculations and formulas, you will get errors. In situations like these, you need to convert those numbers formatted as texts back to numeric values.

In this article, you’ll learn the different methods you can use to convert text to numbers in Excel.

How to Check If a Value is A Number or Text

In Excel, the values may look like numbers, but they don’t add up, and they don’t work like numbers, as shown below.

There are few ways you can identify, if a value is formatted as text or number in Excel.

In an Excel spreadsheet, numbers are aligned to the left by default while texts are aligned to the right in a cell.
If multiple cells with number values are selected, the Excel’s Status Bar at the bottom will show Average, Count, and SUM values, but if multiple cells with text values are selected, the Status Bar will only show Count.
Sometimes if a cell contains a number formatted as text, you’ll see a small green triangle in the top left corner of a cell (an error indicator) as shown above.
When the cell with an error indicator is selected you’ll see a caution sign with a yellow exclamation point. Move your cursor over that sign, and Excel will show you the possible issue with that cell: “The number in this cell is formatted as text or preceded by an apostrophe”.
Also, when you try to sum up the numbers formatted as text, they don’t show a correct total as shown above.

Methods for Converting Text to Numbers

This article demonstrates the five different ways you can use to convert text to numbers, they are:

Using text to number feature
By changing the cell format
Using Paste Special method
Using Text to Columns wizard
Using formulas

Convert Text to Numbers Using Text to Number Feature

Let’s start with a simple and easy method, but this method is not always available to you.

Text to Number feature is only available when numbers are formatted text as a result of importing the data from external sources or when an apostrophe is added to before a number. In cases like these, you will see a small green triangle at the top left corner of the cell, which is an error indicator.

Select the cell(s) that you want to convert from text to numbers and click on the yellow caution icon that appears next to the selected cell or range. In the drop-down menu select the ‘Convert to Number’ option.

Done! Your numbers are converted.

Change Text into Number by Changing the Format

Another quick and easy way to convert the numbers formatted as text to numbers is by changing the format of the cell.

Here’s how you do this:

Select all of the cells you want to convert. You can select an entire column by selecting the column letter or by pressing Ctrl + Space. Go to the ‘Home’ tab, click on the Number Format drop-down list in the Number group, and choose the ‘General’ or ‘Number’ option.

Selecting the ‘Number’ option will give decimal numbers, so the ‘General’ option is fine. Or you can select any other formats in the drop-down. The selected format will be applied to your data.

Once you select ‘General’, the selected cells would be formatted as numbers and the they would get aligned to the right of the cells.

This method doesn’t always work, in such cases use the following methods.

Change Text to Number Using Paste Special Method

The third method is another efficient method for converting text to numbers. In this method, you’re simply performing an arithmetic operation on the text by Paste Special tool. Here’s how you do that:

Select any empty cell (which Excel interprets as 0) and copy it. To copy a cell, select a cell and press ‘Ctrl + C‘ or right-click and select ‘Copy’ from the context menu. Then, select the cells you’d like to convert to numbers, right-click, and select the ‘Paste Special’ option. Or, press the ‘Ctrl + Alt + V’ shortcut to do the same.

In the Paste Special dialog box, select ‘Values’ in the Paste section (top) and ‘Add’ in the Operation section (bottom), and click the ‘OK’ button.

Doing this will add ‘0’ to each cell in the selected range, and convert the numbers stored as texts to numbers.

The Result:

Convert Text to Number Using Text to Columns Wizard

Yet another method for converting text to numbers is by using the Text to column wizard. This is a bit of a lengthy process, but it is useful for converting an entire column of values. This is how you do it:

First, select the cells that you want to convert from text to numbers, go to the ‘Data’ tab, click the ‘Text to Columns’ button in the Data Tools group. A ‘Convert Text to Column wizard’ will open up.

In step 1 of the Wizard, select ‘Delimited’ under Original data type, and click ‘Next’.

In Step 2 of the Wizard, make sure the ‘Tab’ box is checked and click on the ‘Next’ button.

In the final step of the wizard, make sure ‘General’ is selected under Column data format. For the Destination field, you can either specify a new column where you want the result or leave it as it is and it will replace the original data set. Then, click the ‘Finish’ button.

You probably noticed, you didn’t change an option during the whole process, and we could’ve skipped to ‘Finish’ in step 1. If your data contains any delimiters such as spaces or semicolons, you have to choose them in step 2 of the wizard.

Convert Text to Number using Formulas

The final method to convert text to numbers is by using formulas. It is an easy and straightforward way, which doesn’t require much manual intervention. With a formula, you can automate the conversion.

Convert String to Number Using the VALUE Function

Excel offers a special function the ‘VALUE’ function to convert a text to a number.

What is so special about this function is that it can recognize a number value surrounded by special characters, it accepts both text strings and a reference to a cell that contains the texts, and it creates a clean version of the value in another cell.

The VALUE function Syntax:

=VALUE(text)

For example, to convert a number formatted as text in A1, use the below VALUE function:

=VALUE(A2)

First, select the cell where you want the result and type the above formula.

If you want to convert a column of text values to numbers, insert the formula in the first cell of the range, then use the fill handle to copy the formula to the rest of the cells.

Excel VALUE Function with Text Functions

The VALUE function can be used in combination with the Excel text functions i.e., LEFT, RIGHT, and MID to pull out a number from a text string.

=VALUE(RIGHT(A1,5))

In the above formula, the VALUE function extracts the number value from the text string in cell A1 with the help of the RIGHT function.

Change Text to Number with Arithmetic Operations in Excel

You can also convert a text to a number by performing a simple mathematical operation that doesn’t alter the original value.

For example, to convert the number formatted as text in cell A1, you can add a zero, multiply or divide 1 with that text value. These operations do not change the value but convert them to numbers.