Oct
28

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.

Tip. In case you want to translate words to digits, like "seven" to 7, please see English words into numbers on our Blogs.

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.
Error indicators may appear if you import numeric data from an external source

Below you'll find the steps helping to get rid of these Number Formatted as Text errors.

  1. 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.
  2. Click on the error button with the Exclamation mark next to the selected cell or range.
  3. Click on the error button with the Exclamation mark next to the selected range

  4. You will see a drop-down menu. Just select the option Convert to Number.
  5. Select the option Convert to Number

Tip. If you just want to get rid of the green triangle in the upper-left corner of cells without turning text to number, select the Ignore Error option.
Select the Ignore Error option

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.

  1. Select the text-formatted numbers you want to convert to number format in your table.
  2. Navigate to the Home tab -> Number group.
  3. Select the necessary format from the Number Format drop-down list.
    Select the necessary format from the Number Format drop-down list
  4. To get the extended set of the number formatting options, click on the Dialog Box Launcher next to Number.
    Click on the Dialog Box Launcher next to Number
  5. You will see the Format Cells window. Select the necessary category in the list under the Number tab to see all possible customizations.
    See the Format Cells window

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.

  1. Select the range with the cells you want to convert to number format and make sure their format is General.
  2. Press Ctrl + C shortcut.
  3. Press Ctrl + Alt + V on your keyboard to display the Paste Special dialog box.
  4. Click on the Add radio button in the Operation section and press Ok.
    See the Format Cells window

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.

Note. If text is not formatted as constant number, date, or time the function returns the #VALUE! error.
  1. Create a helper column and click on its first cell.
  2. In the Formula bar enter =value(range address). In my example the range address is A1:A10.
    In the Formula bar enter =value(range address)
  3. 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.
    Copy the formula across the helper column to see the results

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.

  1. Download and install the fully functional trial version of Cell Cleaner.
  2. Select the necessary range and click on the Convert Text icon under the Ablebits Data tab.
    Copy the formula across the helper column to see the results
  3. 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!

15 Responses to "Convert text-formatted digits to number using Excel VALUE function and other options"

  1. Mohit Singla says:

    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

  2. Ejaz Ahmed says:

    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.

  3. Shivakumar says:

    Superb really very useful man thank you.

  4. Andrew says:

    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!

  5. Dashboarder says:

    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?

  6. Dashboarder says:

    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).

  7. Toli says:

    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

    • Aleksey Zhigar (Ablebits.com Team) says:

      Hello, Toli,
      Please use =N() to convert text to numbers. If it can't convert to a number it will return 0.

      • Toli says:

        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.

  8. suraj kumar says:

    $ Sixty Six Million Three Hundred Sixty Four Thousand Three Hundred Thirty Four Dollars and Twenty Two Cents AND Thirty %

  9. Elizabeth says:

    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.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard