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
• Apply a number format to digits stored as text
• Use the Paste special functionality to convert text to number in Excel
• Turn text to number using the Excel VALUE function
• Grab a VBA macro to convert text to numbers
• Cell Cleaner - turn numbers stored as text to numbers

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.

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. You will see a drop-down menu. Just 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.

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.
   
4. To get the extended set of the number formatting options, 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.
   

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.
   

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.

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

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