This tutorial shows how to convert text to numbers in Excel using text to column, Formulas, Paste Special, error checking, or VBA.
What if you want to work with numbers, but the formula returns errors? First, you should check the given cell: is the value in a number format? Or is it a text? The problem: in some cases, numbers are formatted as text. We will show you how to avoid issues!
How to check numbers formatted as text in Excel
Excel has a built-in error-checking feature. If you see a green marker (small triangle) in the top left corner of a cell, it is a number that is stored as text. Okay, that sounds good, but we sometimes do not have any visual alerts.
Let us see how to identify numbers formatted as text in Excel:
Numbers
- The numbers are aligned in the cell right by default.
- Select multiple cells and check the status bar on the bottom-right section. You will see the average, count, and sum values if you have numbers.
Text
- If the cell contains the text, you will see a right-aligned value in the cell.
- If you select a range that contains text, the status bar shows only the count value.
- Click on the formula bar: it is a text value if the first character is an apostrophe.
How to convert text to numbers in Excel
In Excel, we have many options to convert text to numbers. This guide will cover easy and fast methods.
Convert text value to number in Excel with error checking
Let’s talk about the small green triangle at the top-left corner of cells.
Click inside the cell! A small floating window will appear, and the description will indicate the reason for the error and gives solutions. The one-step conversion is a user-friendly feature!
Use the drop-down menu and select the “Convert to Number” command.
After clicking it, Excel will convert the text to a number, like in the result below:
Everything looks fine; see the status bar’s average, sum, and count results.
How to convert text to numbers using the “Paste Special” command
Here are five simple steps to convert text to number:
- Select a cell and type 1 into it, then press Enter.
- Use the CTRL + C shortcut to copy the cell content into the Clipboard.
- Select the cells or range of cells containing numbers but stored as text.
- Right-click, then choose Paste special.
- Under the Operation Group, click multiply, and then click OK.
In the picture below, you can follow the steps:
Convert Text to Numbers using multiply by 1
If you don’t use the ‘Paste Special” method yet, there is a workaround with the text-to-number conversion method:
- Insert a helper column
- Type =1 into all necessary cells
- Enter a simply formula =(Text value) * 1
Here is an example:
The result is the same; now we have numbers, not numeric characters, stored as text.
Use a function to convert text to number
We love built-in Excel functions. For example, the VALUE function converts a text string representing a number to a number.
We generated text values in the “Sales” column; some string contains extra spaces and apostrophes. First, in column E, use the LEN function to get the length of the text.
After that, use the VALUE function:
=VALUE(D3)
The result looks great! Without using CLEAN, TRIM, or other data cleaning functions, the VALUE function converts text to numbers quickly.
(*In the example, we use Microsoft365, the latest Excel version for subscribers.)
Convert string to number with Text to Columns
Last but not least, we have to talk about a well-known solution. Excel’s Text-to-column function uses a wizard that is useful for beginners. Good to know that the function works with a single column only.
Let’s see the steps:
First, select the range of cells containing the text you want to convert to numbers. After that, select the Data Tab on the ribbon. Finally, choose the Text To Columns command.
A new window will appear; select “Delimited“, then click Next.
Under the “Delimiters” group, select “Tab“, then click Next.
Select “General“. The “General” option converts numeric values to numbers, date values to dates, and all remaining values to text. In the example, we want to replace the column. If you need to move the converted cells to another Worksheet or range, change the destination using the “Destination” option. Finally, click “Finish“.
Excel converted text to numbers!
If you want to learn all about Excel Formulas, visit our definitive guide.
Additional resources:
