In this article, I'll show you how to convert text digits to number using several options in Excel 2010-2013. You'll see how to make Excel treat digits as numbers with the help of the VALUE function, error checking, Paste Special and applying the number format. Those who want to automate the task will find a helpful VBA macro and a special add-in.
In my previous article, I described how to convert number to text. Today I'll show you this process reversed. You may have at least three reasons to convert text-formatted numbers in Excel.
Unwanted formatting of numbers may occur if you imported data into Excel from an external source, or if you created a file in a different program (dBASE or Lotus 1-2-3). Most of the time Excel would recognize numbers in such cases as text.
If text-formatted numbers prevent from the further work with your data, you can convert them back to number format using the Excel VALUE function, the standard Format Numbers option, Paste Special window or Numbers Stored as Text error indicator. If you need to convert string to number in Excel on a regular basis, have a look at the parts where I write about a special VBA macro and the Cell Cleaner add-in.
Have you ever noticed small green triangles in the upper-left corner of cells containing numbers? These error indicators may appear if you import numeric data into Excel from an external source, or if you enter numbers into cells formatted as text. These green triangles tell you that the number is stored as text.
Below you'll find the steps helping to get rid of these Number Formatted as Text errors.
All the digits stored as text will convert back to number format and the content will be aligned to the right. As soon as they become regular numbers, you can choose to apply or customize number format.
Before selecting the correct number format, please make sure the cells with the values you want to convert don't contain any leading or trailing spaces. You can use the Trim function to remove them.
This option is rather for the numbers formatted General. As with the previous option, please make sure the cells to process don't have any extra spaces. In this part you will find out how to convert numbers to the number format using the Excel Paste Special dialog.
You will see the cells alignment change to the right, which means now your numerical values are treated as numbers.
Using the VALUE function in Excel is the best option to convert text to number if you need to process the data further or if there are excess leading and trailing spaces that need to be removed. For a better result, you can process the cell contents using the clean and text function that will delete non-printing characters and extra spaces correspondingly. Here is the function you can copy across: =value(trim(clean(A2)))
The Excel VALUE Function converts a text string into number. The syntax is: VALUE( text ). Here text is a text string that can be translated into a number.
#VALUE!
error.=value(range address)
. In my example the range address is A1:A10.If you need to convert text to number really often, it's better to automate this task. In the article Do you hate the numbers stored as text error?, you will find a beautiful piece of code that will help you convert text to number format in a flash. This VBA macro will trim the string; handle dates well and change case if necessary.
In this part I'd like to introduce you the quickest and the easiest solution. Cell Cleaner add-in for Excel 2003-2013 will convert text to number is a flash. All you need is to install it and click its icon every time you need to translate text to numbers.
Using this feature will not only format your numbers correctly, but it will remove spaces and apostrophes.
Besides converting text to numbers this helpful tool can trim extra spaces, change case in Excel, replace or remove unwanted characters and much more. You can visit the Cell Cleaner webpage to explore all its possibilities.
Hope this article helped you choose the best solution for converting text to number format in Excel. Stay tuned for more helpful articles about Excel and feel free to leave your questions in the comments below. Be happy and excel in Excel!
21 responses to "Convert text-formatted digits to number using Excel VALUE function and other options"
Very Helpful article.
Most of the times i use to prepare depreciation charts and many other similar things in excel and i did it manually. which was very time consuming and haphazard manner. But now its works wonders for me.
Thank you so much
Thank you, Mohit!
Hi, I am Ejaz Ahmed, the author of Struggling to Excel. Thanks for calling my code beautiful. Much appreciated.
Great article.
The text to column feature in excel can also be used to convert text to numbers, by the way. You may want to include that in your list too.
Thank you, Ejaz! And thanks for the helpful details.
Superb really very useful man thank you.
Fantastic, thanks. This issue has been in excel since day 1. Why on earth MS have not got round to providing a simple one click solution is beyond me.......... if a plugin can do it, so can MS!
My issue with Numbers formatted as Text is that they are in a place where I can not convert them. I am forced to use Lookup or CountIf functions blind because the huge .csv files are closed - I can access them to read, but can not manipulate (nor am I motivated to slow an already cumbersome macro by copying massive ranges to a sacrificial spreadsheet to manipulate them).
The classic case is a part lookup where the parts used spreadsheet output is all Numbers as Text. If I want to query parts used for a vehicle type for a period, I need to use a CountIf to see how many (which is used as an input to a later loop to extract them using an index query).
Ultimately, how do we query external databases with this issue?
Hello, Dashboarder,
For me to be able to help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.
My issue with Numbers formatted as Text is that they are in a place where I can not convert them. I am forced to use Lookup or CountIf functions blind because the huge .csv files are closed - I can access them to read, but can not manipulate (nor am I motivated to slow an already cumbersome macro by copying massive ranges to a sacrificial spreadsheet to manipulate them).
The classic case is a part lookup where the parts used spreadsheet output is all Numbers as Text. If I want to query parts used for a vehicle type for a period, I need to use a CountIf to see how many (which is used as an input to a later loop to extract them using an index query).
Hello, Dashboarder,
For me to be able to help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.
Hi Maria,
I have a sheet called "Master", the values are coming from external data. Column U is text formatted, how can automatically convert this column values to Number Format after each update?
THanks
Hello, Toli,
Please use =N() to convert text to numbers. If it can't convert to a number it will return 0.
Hi Aleksey, thanks for your reply. But when I write ur formula in every cell of Column U, they are deleted after the external data is updated, so the values are still text formatted. Anyhow, i solved my Problem, I just changed the Format of the data in that external source (Access in this case), and then linked again to that souce, everything is working perfect. Thanks anyway.
$ Sixty Six Million Three Hundred Sixty Four Thousand Three Hundred Thirty Four Dollars and Twenty Two Cents AND Thirty %
I have a xls which tracks days off in a team. The "value" (which relates to a "key") entered into a given cell is a text eg A (=annual leave); C (=course) etc.
As I use Excel 2003, the conditional formatting doesn't work for more than 3, and I have 9, so I have VBA code running to manage the cell colour change for the key entered.
I need to total the number of staff in the team who are absent on a given day. If they were all whole days, the formula is simple enough as it is merely counting a cell with a value entered, in even if its not numerical.
However, sometimes the absence will be a half day so I need Excel to recognise where the "key" letter is a whole (counted as 1) or a half (counted as 0.5). The total therefore, needs to recognise the letters and automatically convert and do the calculation.
I suspect this will require VBA code but not sure how to do that.
Any ideas or pointers would be gratefully received.
Many thanks.
Elizabeth.
Dear Mam,
I have a name stored as text in a cell in excel. Say Peter.
I want to convert this to normal word in another cell so that i can use this in the formulsa.
All talks about converting text to numbers but not text to normal format.
Regards
S.Narasimhan
Dear Mam,
I have a name stored as text in a cell in excel. Say Peter.
I want to convert this to normal word in another cell so that i can use this in the formulas
Regards
S.Narasimhan
Super really highly thankful
Dont have to do this whole procedure just put formula "=A2*1"
Thats it
Hi
I can't understand why mine is not working ... i've tried the value formula and did everything else as instructed . Im so frustrated as im really struggling over here.
Hello!
Your task is not completely clear to me. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result. Specify what formula you used and what problem or error occurred.