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!
207 comments
how to convert area to numbers
exp.area= 79.84 convert into number?
please explain
Hi,
How do I recode a drop-down list in Excel, in other words assign a code number for each of the drop down responses? I've tried several of the options but they haven't worked.
Thanks
Hello Ellen!
You can use the Combo Box (menu Developer - Insert - Form Controls - Combo Box). It returns the number of the selected item from the drop-down list to the cell you designated
I have
AA 8881/17
BR 078/16
BR 078/18
BR 078/19
CZ 458/18
EK 148/15
EK 148/15
EK 9287/18
KL 0427/15
KL 0427/18
and i need only the last 2 digits or to be sorted from the last 2 digits how can I do it?
Hello!
To extract the last 2 digits from the text, use the RIGHT function
Text Result -
=RIGHT(A1,2)
Number result
=--RIGHT(A1,2)
I hope it’ll be helpful.
How we can data in number when some data copied with table and pasted in excel now that numbers are in text and before starting numbers many spaces are there
how we can do convert such cases in number format
Hello!
I recommend studying the manual How to remove spaces in Excel
We have a tool that can solve your task in a couple of clicks: Ablebits Data - Trim Spaces.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Hi,
I cannot use vlookup formula when numbers are presented as text.
Example I have:
0456
1247
1234/1,
and I need to have the values as text, so I can sort them A to Z. I always get the error massage, when use vlookup formula.
Any help?
Hello!
I recommend to study this manual.
I have a column on the left that goes from 0-60. The column on the right contains a formula that reads: =COUNTIF(CY$4:CY$66,0
I put $ so that those inputs don't change but I'd like for the 0 to change to 1, 2 and so on.
So going down the column it should look like this:
=COUNTIF(CY$4:CY$66,0
=COUNTIF(CY$4:CY$66,1
=COUNTIF(CY$4:CY$66,2
=COUNTIF(CY$4:CY$66,3
And so on.
That way I don't have to go into each cell to change each number individually. The reason I have to change it is because I have to add and delete rows which causes for there to be a gap which I then have to fix. I'm sure there's a way. Please help!
I recommend using the ROW function as a counter.
If your formula
=COUNTIF(CY$4:CY$66,0)
is in cell D2 (i.e., line 2) then change the formula
=COUNTIF(CY$4:CY$66,ROW()-2)
After that you can copy this formula down along the column.
Hope this is what you need.
Hi all.
Need help on formula.
E.g.
In cell A2, there is 4 digit no. "1234"; cell A3, "2345".
In cell B2, there is a text "M" and cell B3 text "C".
How to form a formula in cell C2 and C3 that the output are "MM-181234-01" and "CC-182345-01" respectively.
Hello Judith!
If I got you right, the formula below will help you with your task:
=CONCATENATE(B2,B2,"-18",A2,"-01")
Hope this is what you need.
I have a number 328,345 in General format
Somehow I looked some of the hidden characters it looks like "328,345 " notice the space
I used clean, trim and value in all the nine orders but to no avail can i get that number to allow me to do any number functions like add.
Any ideas
Hello Fran!
If I understand your task correctly, the following formula should work for you:
=VALUE(SUBSTITUTE(A1,",",""))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Please, kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:
$3,51,670
$4,24,980
$2,31,650
$5,29,810
$3,12,980
$2,47,360
$9,23,840
$1,26,120.
I have tried all the suggestions stated above but it still doesn't recognize it as a number when I want to calculate or find the maximum of some numbers and it is included.
Hello!
If I understand your task correctly, the following formula should work for you
=--SUBSTITUTE(SUBSTITUTE(C21,",",""),"$","")
I hope this will help
Hello,
I'm working with Geodesy and obviously excel is a part of it since i insert my points in measurement device. So in one of the columns i have list of numbers 145,987 etc. and it appears to be correctly written number, until i select that cell and in the function bar it appears 145987. I've tried changeing to text, customize format cells general, even entered formulas, and in options changed that thousands be marked as , and I manage to change number with a comma 145,987 but in function bar it says 145987.
Could you please help me.
Thank you,
Regards!
Hello Enisa!
I’m sorry but your task is not entirely clear to me.
For me to be able to help you better, please describe your task in more detail.
What exactly do you enter in the cell - 145987 or 145,987 or 145 987? Do you want to change cell format (appearance) or cell content?
It’ll help me understand it better and find a solution for you. Thank you.
Hey everyone anyone help me my some project. How a number replace in to next column.
Column 1 1234567
Column 2 Your Sr xxxxxxx will be closed against column 1 number.i want to show a number in column 2 where xxxxx will be shown.
Which formula used in excel
Hello Jay!
If I understand your task correctly, the following formula should work for you:
="Your Sr xxxxxxx will be closed against "&A1&" number"
or
=CONCATENATE("Your Sr xxxxxxx will be closed against ",A1," number")
I hope it’ll be helpful.
None of this optinons worked for me. What a waste of time. FY
Hello!
Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Great article, very technical, detailed and simple all at once.
IMPORTANT!!! Please, review the method for converting Text to Numbers ("Change text to number with Paste Special"). I believe your instruction "Paste Special" should be followed by "Formats" and not by "Values". Otherwise the existing data in the selected cells will be lost....
With all due respect,
Keep up the good work,
Michael
Hello Michael,
Thank you for your feedback. The instructions are correct. The idea is to convert numbers stored as text to numbers by performing an arithmetic operation, addition in this case. For this, you add an empty cell (i.e. zero) to the copied Values, not Formats.
How do I convert a number stored as text (1 hour 30 minutes 45 seconds) to time format (01:30:45) in Excel?
Hello Angela!
Please try the following formula:
=TIME(LEFT(A1, SEARCH(" ", A1)-1), MID(A1, SEARCH(" ", A1, 3)+1, SEARCH(" ", A1, SEARCH(" ", A1, 3)+1)-SEARCH(" ", A1, 3)),MID(A1, SEARCH(" ", A1, 15)+1, SEARCH(" ", A1, SEARCH(" ", A1, 15)+1)-SEARCH(" ", A1, 15)))
Hi,
New to excel, but wondering if there is a way to convert a place value for a point score (sporting event spreadsheet) across multiple cells
Eg. 1st place(1) = 4 points, 2nd = 3, 3rd =2 & 4th =1
So that when you type 1 it automatically converts to 4 etc
Thanks
Thanks for the tips, they were very helpful, easy to follow and you provided more than one way to complete the task at hand. Big KUDOS to you
if i convert 3108101010000191 text in to numeric by using value function, answer comes to 3108101010000190 why?
1,19,583
87,61,525
I am unable to convert these into numbers as well as unable to sum of them. Kindly give solution.
how to convert 2,66,020 into text