How to Convert Text to Number in Excel (Step-by-Step)

Often you’d populate numbers in Excel, but they won’t act as numbers.

They might not add up, multiply, or just throw errors your way. This can be because those numbers are formatted as text. In most instances, Excel automatically identifies numbers and formats them as numbers.

However, in other limited instances, they are left formatted as text. This tutorial is meant to teach you how to convert text strings to numbers in Excel 🔢

We will cover various methods of how to connect text to numbers in Excel so download your free practice workbook for this guide here and read along with me till the end.

Table of Contents

Convert text to number changing the Cell Format

If you have numbers formatted as text strings in Excel, they will by default be left aligned.

Whereas numbers will generally be right aligned in a cell (unless you change its alignment manually) 👀

The first way that clicks one’s mind when it comes to converting text to numbers in Excel is to change their format to Numbers.

And that’s right.

Step 1) Select the cells where you want to change the cell format.

Step 2) Go to the Home tab > Number group > Formats > Number Format.

You will see the cells, and their content will be formatted as numbers 🧠

However, some numbers from the list are still left aligned (although their format is now Number).

By changing the cell format, we can convert mostly text-formatted cells to numbers. But if your data has cells in between (that have a green triangle flap on the top left) will not be appropriately converted to numbers with this method 🔰

To convert the format for such cells, see the next section.

Convert text to number using the error-checking option

When you see cells flagged with a green triangle on the top-left, it is Excel’s way of saying that there’s some error with this cell 🚩

As you select any of these cells, you’ll see a yellow error icon pop next to it.

For such cells, here’s what you do:

Step 1) Go to the cell flagged by Excel.

Step 2) Click on the error icon next to it to launch the error drop-down menu.

From this list, Excel tells you that the error with this cell is that it contains a number but is stored as text ⚡

Step 3) Click on the option to Convert to Text.

Excel will convert this text-stored number to text.

Step 4) To do this to multiple cells at once, select all such cells together and choose the option to convert them to numbers.

All these numbers will be converted into numbers, and the green flag will also go away ⌨

Using Text to Column

The Text to Column feature of Excel is primarily meant to split data in a cell into multiple columns based on a delimiter or fixed width.

However, if you do not want to use the error-checking option explained above, you can tweak this tool to convert text to numbers in Excel 🤯

It allows an option to choose how you want the split numbers to be formatted. That is where you can choose the General format to have the numbers stored as text to be converted to Numbers format.

Here we have some numbers stored as text in Excel.

Step 1) Select the cells containing text-formatted numbers.

Step 2) Go to the Data tab > Data Tools > Text to Column button.

Step 3) In the Convert Text to Column Wizard, select Delimited.

Step 4) Press the Next button.

Step 5) Select Tab as the Delimiter and click on Next.

Step 6) From the next Convert Text to Column Wizard screen, select the Column data format as General and hit Finish.

You will see the resulting numbers will no longer be in Text format 📚

Convert text to number using Arithmetic Operations

A list of numbers formatted as text in Excel can be converted into numbers by performing an arithmetic operation on them.

Step 1) Activate a new cell.

Step 2) Write any simple arithmetic operation on it that results in the number. For example, add 0 to it.

Step 3) Drag this list down until the end of the list.

You will see the resulting numbers are numbers, and not text strings (see the alignment) 📃

Once you have converted the text to numbers, you can copy and paste them as values to get rid of the formulas and have simple values.

Convert text to number using Paste Special

This method works on the same science of performing an arithmetic operation to text-based numbers.

However, a plus point of this method is that the converted values will stay in their place, and you will not have to copy and paste them as values later to get rid of any formulas running behind them 🥽

Step 1) Activate a blank cell and write 1 in it.

Step 2) Copy this cell by pressing the Ctrl + C key.

Step 3) Select the range of cells containing numbers formatted as text.

Step 4) Right-click to launch the context menu and choose Paste Special.

Step 5) From the Paste Special dialog box, select Values and Multiply.

Step 6) Press okay, and the returning cell values will be formatted as numbers.

Convert text to number using the VALUE function

This method is the genuine method offered by Excel to convert a text string that represents a number into a number – the VALUE function.

The purpose of this function is to convert a given value into a number (if it’s already not) 🙋‍♀️

Let me show you here.

Step 1) Activate a cell.

Step 2) Write the VALUE function and supply the cell formatted as text to it.

The converted value will be a number.

Step 3) Drag this formula down to all the text-formatted numbers.

Once you have the text converted to numbers, you can copy and paste them as values to retain the numbers only and delete the formulas running behind.

There you go! 🚴‍♀️

Conclusion

We have discussed numerous methods to convert those stubborn text-formatted values in your Excel sheet into numbers.

Hope you enjoyed learning, and if you did, here are some other interesting Excel tutorials on conversions in Microsoft Excel. Give them a read here.

• Convert Time Into Decimals in Excel (Hours/Minutes/Seconds)
• How to Convert Positive Numbers to Negative in Excel
• How to Convert Excel Formula to Value (Quickly)