Convert text-formatted digits to number using Excel VALUE function and other options
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.
- To make such functions as LOOKUP, VLOOKUP and MATCH work correctly.
- To perform math operations like counting, summing, etc.
- Apply a custom number format.
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.
Use Error Checking to convert text-formatted numbers
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.
- Select any cell in your table that contains a green error indicator in the upper-left corner.
Tip. You can use one of the following keyboard shortcuts to select a range.
- Ctrl + A - selects the entire contiguous range.
- Ctrl + A Twice - picks the entire sheet.
- Ctrl + Space Bar - highlights the entire column.
- Shift + Left/Right Arrow - selects multiple columns.
- Shift + Space Bar - picks the entire row.
- Shift + Up/Down Arrow - selects multiple rows.
- Click on the error button with the Exclamation mark next to the selected cell or range.
- You will see a drop-down menu. Just select the option Convert to Number.
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.
Apply a number format to digits that are stored as text
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.
- Select the text-formatted numbers you want to convert to number format in your table.
- Navigate to the Home tab -> Number group.
- Select the necessary format from the Number Format drop-down list.
- To get the extended set of the number formatting options, click on the Dialog Box Launcher next to Number.
- You will see the Format Cells window. Select the necessary category in the list under the Number tab to see all possible customizations.
Use the Paste special functionality to convert text to number in Excel
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.
- Select the range with the cells you want to convert to number format and make sure their format is General.
- Press Ctrl + C shortcut.
- Press Ctrl + Alt + V on your keyboard to display the Paste Special dialog box.
- Click on the Add radio button in the Operation section and press Ok.
You will see the cells alignment change to the right, which means now your numerical values are treated as numbers.
Turn text to number using the Excel VALUE function
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.- Create a helper column and click on its first cell.
- In the Formula bar enter
=value(range address). In my example the range address is A1:A10.
- Copy the formula across the helper column using the fill handle to see the alignment automatically change to the right and the green error indicators disappear.
Grab a VBA macro to convert text to numbers
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.
Cell Cleaner - turn numbers stored as text to numbers
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.
- Download and install the fully functional trial version of Cell Cleaner.
- Select the necessary range and click on the Convert Text icon under the Ablebits Data tab.
- Press the Convert numbers stored as text to numbers radio button and click Convert.
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.