by The tutorial shows many different ways to turn a string into a number in Excel: Convert to Number error checking option, formulas, mathematic operations, Paste Special, and more.
Sometimes values in your Excel worksheets look like numbers, but they don't add up, don't multiply and produce errors in formulas. A common reason for this is numbers formatted as text. In many cases Microsoft Excel is smart enough to convert numerical strings imported from other programs to numbers automatically. But sometimes numbers are left formatted as text causing multiple issues in your spreadsheets. This tutorial will teach you how to convert strings to "true" numbers.
How to identify numbers formatted as text in Excel
Excel has an inbuilt error checking feature that alerts you about possible problems with cell values. This appears as a small green triangle in the top left corner of a cell. Selecting a cell with an error indicator displays a caution sign with the yellow exclamation point (please see the screenshot below). Put the mouse pointer over the sign, and Excel will inform you about the potential issue: The number in this cell is formatted as text or preceded by an apostrophe.
In some cases, an error indicator does not show up for numbers formatted as text. But there are other visual indicators of text-numbers:
| Numbers | Strings (text values) |
|
|
In the image below, you can see the text representations of numbers on the right and actual numbers on the left:
How to convert text to number in Excel
There are a handful of different ways to change text to number of Excel. Below we will cover them all beginning with the fastest and easiest ones. If the easy techniques don't work for you, please don't get disheartened. There is no challenge that cannot be overcome. You will just have to try other ways.
Convert to number in Excel with error checking
If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a two-click thing:
- Select all the cells containing numbers formatted as text.
- Click the warning sign and select Convert to Number.
Done!
Convert text into number by changing the cell format
Another quick way to convert numerical values formatted as text to numbers is this:
- Select the cells with text-formatted numbers.
- On the Home tab, in the Number group, choose General or Number from the Number Format drop-down list.
Note. This method does not work in some scenarios. For example, if you apply the Text format to a cell, enter a number, and then change the cell format to Number, the cell will remain formatted as text.
Change text to number with Paste Special
Compared to the previous techniques, this method of converting text to number requires a few more steps, but works almost 100% of time.
To fix numbers formatted as text with Paste Special, here's what you do:
- Select the text-number cells and set their format to General as explained above.
- Copy a blank cell. For this, either select a cell and press Ctrl + C or right-click and choose Copy from the context menu.
- Select the cells you want to convert to numbers, right-click, and then click Paste Special. Alternatively, press the Ctrl + Alt + V shortcut.
- In the Paste Special dialog box, select Values in the Paste section and Add in the Operation section.
- Click OK.
If done correctly, your values will change the default alignment from left to right, meaning Excel now perceives them as numbers.
Convert string to number with Text to Columns
It is another formula-free way to convert text to number in Excel. When used for other purposes, for example to split cells, the Text to Columns wizard is a multi-step process. To perform the text to number conversion, you click the Finish button in the very first step :)
- Select the cells you'd like to convert to numbers, and make sure their format is set to General.
- Switch to the Data tab, Data Tools group, and click the Text to Columns button.
- In step 1 of the Convert Text to Columns Wizard, select Delimited under Original data type, and click Finish.
That's all there is to it!
Convert text to number with a formula
So far, we have discussed the built-in features that can be used to change text to number in Excel. In many situations, a conversion can be done even faster by using a formula.
Formula 1. Convert string to number in Excel
Microsoft Excel has a special function to convert a string to number - the VALUE function. The function accepts both a text string enclosed in quotation marks and a reference to a cell containing the text to be converted.
The VALUE function can even recognize a number surrounded by some "extra" characters - it's what none of the previous methods can do.
For example, a VALUE formula recognizes a number typed with a currency symbol and a thousand separator:
=VALUE("$1,000")
=VALUE(A2)
To convert a column of text values, you enter the formula in the first cell, and drag the fill handle to copy the formula down the column:
For more information, please see VALUE formula to convert text to number.
Formula 2. Convert string to date
Apart from text-numbers, the VALUE function can also convert dates represented by text strings.
For example:
=VALUE("1-Jan-2018")
Or
=VALUE(A2)
Where A2 contains a text-date.
By default, a VALUE formula returns a serial number representing the date in the internal Excel system. For the result to appear as an actual date, you just have to apply the Date format to the formula cell.
The same result can be achieved by using the DATEVALUE function:
=DATEVALUE(A2)
For more information, please see How to convert text to date in Excel.
Formula 3. Extract number from string
The VALUE function also comes in handy when you extract a number from a text string by using one of the Text functions such as LEFT, RIGHT and MID.
For example, to get the last 3 characters from a text string in A2 and return the result as a number, use this formula:
=VALUE(RIGHT(A2,3))
The screenshot below shows our convert text to number formula in action:
If you don't wrap the RIGHT function into VALUE, the result will be returned as text, more precisely a numeric string, which makes any calculations with the extracted values impossible.
For more information, please see How to extract number from string in Excel.
Change Excel string to number with mathematic operations
One more easy way to convert a text value to number in Excel is to perform a simple arithmetic operation that does not actually change the original value. What can that be? For example, adding a zero, multiplying or dividing by 1.
=A2+0
=A2*1
=A2/1
If the original values are formatted as text, Excel may automatically apply the Text format to the results too. You may notice that by the left-aligned numbers in the formula cells. To fix this, be sure to set the General format for the formula cells.
Tip. If you'd like to have the results as values, not formulas, use the Paste Special feature to replace formulas with their values.
That's how you convert text to number in Excel with formulas and built-in features. I thank you for reading and hope to see you on our blog next week!
198 comments
Hi
I want to copy and paste specific number from a PDF to Excell 2013. Currently when I use the Copy and Paste option, the copied selection stay as text format in Excel 2013, even if I change the cell format to number. I can't do any calculations in the spreadsheet.
2010.00
2010.00
0,00
However the copy and paste function worked perfectly in the Excel 2010 version. Do I need to make a change in the settings in 2013 version?
Hi! I can't see your Excel settings. Note which decimal separator you are using. To convert text to a number, use the recommendations in the article above.
Don't know if this has been discussed but...
If you're new to converting numbers, one of the first things you should do is look at the "text" in the cell. I found, when first confronting the notion of converting numbers represented as text, that frequently you may find that there is a "leading space" in front of the text string. And you cannot see the blank space if the cell has "center" justification of another alignment.
So my tip here is to check the text string in the cell, as a first step. Check for leading blank space, or trailing spaces, as these extra spaces will prevent easy conversion. Remove the leading or trailing blank spaces with =RIGHT() or =LEFT() functions (essentially you will extract the number from the text string), then excel will allow you to easily convert the string to a number.
One way to check is to select the cell with the text string, then put the cursor in the Formula Bar and use "HOME" key to move the cursor all the way to the left. Note if there is a space between the cursor and the text. You can perform a similar function to check for trailing blank spaces.
Finally after exhausting the other options unsuccessfully, I tried the Text to Columns/Finish instructions and my string was converted to a number , thank you!
Excellently helpful
Hi there, I'm trying to create a spreadsheet that auto calculates my monthly business mileage depending on which location I work at each day (4 different locations) using "IF" with multiple text data representing the return mileage to each location (hope that makes sense):
=IF(B3="GRV", "36.8", IF(B3="CTM", "9.4", IF(B3="RTR", "11.8", IF(B3="GLM", "10.4", "RD"))))
If I am not working at any of the locations, this is recorded as Rest Day (RD).
I have created the formula to calculate the mileage in each row, but now need to add up the total mileage. However, as the mileage in each row is showing as Text, Autosum doesn't work, so I need another way to do this. When reading all your solutions above, none of these seem to apply as I already have the IF formula, and can't see a way to extract just the numbers from the mileage column.
Would appreciate any advice you can offer, thanks, Carlos, UK.
Scratch that, I've managed to resolve it from another post in your comments!!
I removed the quotes around the numbers, formatted the cells to Numbers, and hey presto!!
=IF(B2="GRV", 36.8, IF(B2="CTM", 9.4, IF(B2="RTR", 11.8, IF(B2="GLM", 10.4, "RD"))))
Thank you for your solution, even if it wasn't directly for me lol. Really do appreciate people like you who put so much effort into helping others.
Regards, Carlos.
Hello. Suppose I have categories in the column
ex: Sex: M or F. How do I encode that so that it shows as 1 or 2. or any other number? thank you
Hi! You can't replace a letter with a number using formatting. You can set the value to 1 or 2 in another cell using the IF function.
I have a column with "Emp No. and Description". I used this formula " =LEFT(B25,FIND(" ",B25)-1) " to separate Emp No only. Now which formula should I add in this " =(LEFT(B25,FIND(" ",B25)-1)) " to change it's data type to numerical value which right now is text type.
Read carefully the last two paragraphs in the article above.
Great
I am having an issue when attempting to do a sum total of a column of numbers.
The figures in the column are the result of a formula =IF(C3="Full", "£88", IF(C3="Half", "£44",))
The corresponding £88 & £44 do not then appear in the =SUM(D3:D33)
Can you recommend a work around?
Hi! Your formulas return text, not a number. Therefore, the SUM function does not work. You can return numbers from an IF formula and apply a financial format to the cell to show the currency symbol.
=IF(C3="Full", 88, IF(C3="Half", 44,))
Read more about number formats here: Custom Excel number format.
This resolved my issue, Thank you!!
When I have a range of numbers that are entered as Text Data in Excel. I select the range, Hold "Ctrl" while pressing "D", then "E", then "F". Converts the range to Numerical Data. Been extremely useful when pulling data from DBs into a spreadsheet.
i used the MS365 app to scan and then "image to TABLE" function to pull in some text and numbers from a hardcopy sheet. this has worked however the numbers in the cells seem to be in accountancy format (maybe a red herring) but none of the above solutions will convert them into real number so I can manipulate them. Only way is to overwrite the number in the cells, however I have hundreds of numbers to convert, is there another solution that i can try?
Thanks
Hi! To convert your text to a number, try to use Convert text tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Thank you so much! None of the other ways worked, only the "convert" from Ablebits Data
I have a attendance sheet where i have given 30days attendance to all my employees. and the attendance are marked as letter "P".Now I want to convert all P to ascending numbers.
Is is possible?
Please clarify your problem or provide additional information to understand what you need.
how to convert 05.30 to 0530?
Hi! Here is the article that may be helpful to you: How to remove characters/text from string in Excel.
The formula might look like this:
=SUBSTITUTE(A1,".","")
The article provides clear and concise step-by-step instructions on how to effectively convert text into numerical values, saving time and reducing errors. The inclusion of screenshots and examples ensures that readers can easily follow along and implement the techniques.
I have a number like ex.
4512
652
1843
4756
8543
1549
7643
94785
and the total of 1st, 3rd and 8th Number is 101140
But the is converted in Value (No use of Trace Precedents Option) There is no same Total and come by any other number.
Show the formula which help me to to know that 1st 3rd and 8th Option are included in 101140 Total
Hi! Maybe this article will be helpful: How to find all combinations of numbers that equal given sum in Excel.