Columnist
Tags:

# Three Ways to Convert Text-Based Numbers to Values

Jun 28th 2013
Columnist baona/iStock

Periodically, you may encounter numbers in Excel that you can't sum or use arithmetically. A common cause for this is numbers formatted as text. Often, reports exported from other programs, such as an accounting package, will be formatted as text or they might contain embedded spaces.

In this article, I'll describe three ways you can convert numbers that appear trapped under glass into a usable format.

First, there are a couple of ways to determine if your numbers are formatted as text. Select one or more of the suspect values and then in:

• Excel 2007 and later or Excel:Mac 2011: Determine if the word Text appears on the Home tab, as shown in Figure 1.
• Excel 2003 and earlier: Choose Format, Cells, and then determine if the Number tab is set to Text. Figure 1: The Text format prevents you from using numbers with mathematical functions.

The Text format in a cell displays the contents of a worksheet cell rather than its result. Thus, if you enter a formula in a cell formatted as text, the underlying formula will appear in your worksheet cell rather than the result.

However, the Text format is not the only way to store values as text. If you look closely within a worksheet cell, you may see that a numeric value is prefaced with a single quote. This is another means to display the contents of a cell rather than the result.

You can categorically determine if a number is stored as text by way of the ISTEXT worksheet function. For instance, if cell A1 contains a value you think may be stored as text, type this formula in a nearby blank cell:

=ISTEXT(A1)

The ISTEXT function will return TRUE if the value in cell A1 is being stored as text, or FALSE if it isn't.

Now that we know how to determine if a number is stored as text, let's look at three ways to convert one or more cells back to numeric values:

One approach is to use the =VALUE function, as shown in Figure 2. If the cell that you reference with the VALUE formula can be converted to a numeric value, you'll see the corresponding number. Otherwise, you'll see a #VALUE! error, which signifies that the referenced cell contains letters or other non-numeric contents. You can then copy the VALUE formula as needed to convert additional values to numbers. Keep in mind that you'll then need to copy these formulas to the clipboard, and then use Paste Special, Values to preserve the numeric results. You might choose to replace the original set of numbers with the results from the VALUE formulas, after which you can clear the VALUE formulas from the spreadsheet. Figure 2: You can use the VALUE function to convert numbers stored as text to usable values.

A somewhat simpler approach involves using the Paste Special, Multiply command. In any version of Excel, enter the number 1 in a blank cell and then copy that value to the clipboard. Next, select the range of values stored as text, right-click, and choose Paste Special, Multiply, and then OK. You can then clear 1 from the worksheet cell. Figure 3: You can also convert numbers stored as text to values by multiplying the cells by 1.

The easiest way to convert a range of values to text, though, involves using the Text to Columns feature. In any version of Excel, select the range of cells that contain numbers stored as text, then choose Data, Text to Columns, and then click Finish. Don't select any choices within Text to Columns; simply launch the wizard and then click Finish, as shown in Figure 4. Your text-based numbers will be usable numbers in Excel. Figure 4: The Text to Columns wizard is the easiest way to convert numbers stored as text to values.

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected]. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Tags:

### Replies (28) By jpb
Jun 26th 2015 01:11

If the cell does contain text, and the methods above don't work, try using the LEN function to count the number of characters in the cell. It may be there are hidden spaces before the number. You can then use something like =VALUE(RIGHT(A1,3)) to convert to numbers

Thanks (0) By David Ringstrom
Jun 26th 2015 01:11

Great suggestion! Another alternative would be =VALUE(TRIM(A1)). The TRIM function eliminates extraneous spaces, but does keep one space between each word when necessary.

Thanks (0) By hunterdk
Jan 27th 2017 20:00

CONVERT CHASE TRANSACTION NUMBERS FROM TEXT TO NUMBERS:
I downloaded Bank Statement Data to Word & Excel. The deposits were calculateable currency. The payments were not. I tried the 3 tricks.
1. Tried all the tricks Internet showed
=isvalue(a3) True if text
=1 in column A2  copy  past special  multiply
Copy text number cells  Data  Text to Columns  OK
NONE OF THESE TRICKS WORKED
3. The opened anything.csv in a new Excel spreadsheet and found ?\$500.00 for each value in the Payment column.
4. Replaced ?\$ with \$ which converted all text payments to currency to \$500.00. QED.

Thanks (0) By CC
Jun 26th 2015 01:11

How do I do this if I have hundreds of columns....

Thanks (0) By David Ringstrom
Jun 26th 2015 01:11

It's hard to give you a precise answer without seeing your data, but the Paste Special technique I described above will work with any number of columns. You can only use Text to Columns on a single column at a time, so if it's the best fit for your needs, you'd need a macro that could loop through the columns and convert them one at a time. Paste Special will probably work, but you may have to select dozens of columns at once and do it in batches.

Thanks (0) By Naveen
Jun 26th 2015 01:11

Hello David,

I have part number starting with 0090134325 stored as text. If I convert to number then starting 00 will be missing. pls help

Thanks (0) By David Ringstrom
Jun 26th 2015 01:11

If you need leading zeros, then you probably want to keep it as text. But, if you do need to perform arithmetic on the number, convert it to a value, and select any cells that contain said numbers. Press Ctrl-1 (Ctrl-One) to display the Format Cells dialog box, and choose Custom on the Number tab. Enter 0000000000 (that's 10 zeros) in the Type field. This will give you the leading zeros back, but the cell will contain a numeric value.

Thanks (0) By Naveen
Jun 26th 2015 01:11

Thanks David, this will show the zeros but when I do Vlookup it is not recognised.

Thanks (0) By David Ringstrom
Jun 26th 2015 01:11

That's correct. Your VLOOKUP is looking at a text-based version of the number. You either need to convert the number that VLOOKUP is referencing in the same fashion, or leave all of the numbers as text. One more alternative is use the VALUE function in VLOOKUP, so that =VLOOKUP(A1,C1:D10,2,FALSE) becomes =VLOOKUP(VALUE(A1),C1:D10,2,FALSE). My recommendation is to leave all of the numbers with leading zeros as text unless you need to sum the numbers or do something else arithmetic related to them.

Thanks (0) By Chris
Jun 26th 2015 01:11

thanks but none of these methods worked for me.
If you want to help people, why not start with the most simple method at the top of the list?

Thanks (0) By David Ringstrom
Jun 26th 2015 01:11

This article represents my take on the Socratic method, where options are considered, and then discarded due to lack of suitability. If you care to explain your Excel situation, I'll be glad to elaborate on why the above techniques might not have worked for you.

Thanks (0) By Joey
Jun 26th 2015 01:11

David,

I am working with an income statement I imported from Yahoo Finance as an HTML file and have saved as a .xlsx file. On many numbers, there are two spaces to the right of the number that are causing numbers to be saved as text. None of the three methods work. =VALUE(RIGHT(A1,2)) and =VALUE(TRIM(A1)) deliver the #VALUE error as well. Any recommendations?? Thank you!

Thanks (0) By David Ringstrom
Jun 26th 2015 01:11

Joey,

You could try =CLEAN(A1) to see if that helps. Most likely though you'll want to copy one of the "spaces" to the clipboard and then paste it into this formula =SUBSTITUTE(A1,"x","") where x is the "space" character that you copied and pasted from your text. I use "space" in quotes because I've encountered characters that masquerade as a space, but aren't since TRIM doesn't eliminate them.

David

Thanks (0) By Gary
Jun 26th 2015 01:12

If you copying numbers from Sales Force or other web sites that display numbers as text you can use this formula where A2 is where you paste in the copyied text. This formula removes all the \$, Spaces, commas, and the CHAR(160) characters (which Sales Force uses everywhere??) to turn a number into a real NUMBER in excel.

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CLEAN(A2)),"\$",""),",","")," ",""),CHAR(160),""))

Thanks (0) By deshay
Jun 26th 2015 01:11

Hey guys,

So none of this worked for me. I feel like my entire Excel program is just broken. Trim is refusing to remove the spaces; if I check it with len afterwards, it still has all of the spaces. If I try to value it, it gives an error (or just spits the text back at me...). The text to column just spits it back out as it is, spaces and all.

The only one that kind of worked was the =value(right(a1,3)), but I had to change the # because my values range from single digit to triple digit. However, that isn't working now on number that has a decimal, and unfortunately the decimal is important.

Any thoughts?

Thanks (0) By David Ringstrom
Jun 26th 2015 01:11

See my reply to Joey below.

Thanks (0) By Nancy
Jun 26th 2015 01:11

I like solution number 3. Thanks a lot

Thanks (0) By Christo
Jun 26th 2015 01:12

I had text in the following format "120 120,53" for arguments sake. Multiplying by 1 did not work but multiplying by 1 and then dividing by 1 in the same formula fixed the problem.

Thanks (0) By Christo
Jun 26th 2015 01:12

Ignore the last one, worked with wrong set of data

Thanks (0) By Christo Snyman
Jun 26th 2015 01:12

Ok Guys, I managed with The Find and Replace function.
Highlight the range you want to convert from text to number format. Launch the function or CNTRL+F .
The value of the first cell in your selected range will display in the formula bar e.g. as 1 123,45. Now the space between the two 1's is not really a space derived from pressing the space bar. Copy this "space" in the formula bar by highlighting it with your mouse, copy with CNTRL C and then paste it with CNTRL V in your "Find what ?" box. Enter nothing in the "replace with?" box. Launch the "Find and Replace all" button. Sim sala bim. Chris

Thanks (1) By jacoblkp
Dec 11th 2017 09:55

it is working, thanks!

Thanks (0) By Marie
Jun 26th 2015 01:12

what if the cells in question contain a formula? i.e., i am trying to sum a column in excel that are all formulas, however the sum is coming up #N/A.. I need to keep these as formulas as this is part of a dynamic model..

Thanks (0) By David Ringstrom
Jun 26th 2015 01:12

Here's the full formula for Gary's solution listed below:

=VALUE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(TRIM(CLEAN(A2))
,"\$",""),",","")," ",""),CHAR(160),""))

To piece this together in a worksheet cell, type an apostrophe in a cell before you paste the first line. Paste in the second two lines and then remove the apostrophe. The commenting system here truncates part of the formula when posted all in one line.

Thanks (0) By SR
Jun 26th 2015 01:12

Oh my gosh thank you, I have an excel sheet where this problem occurs in 3 columns with 15,000 rows...the text to column method worked like a charm

Thanks (0) By Ben
Jun 26th 2015 01:12

Hi David, I'm not sure I'm in the same situation as this forum discusses but I desperately need some help and I'm not that savvy with Excel. I am trying to convert a cell which is text ("Hourly") but this is a drop down list of another cell which represents the sum of F3:AJ3. The cell next to the drop down, I want to represent the numerical value of Hourly but when I insert the formula =VALUE, it gives me the text "Hourly" (expectedly so). Can I give the cell the numerical value of what "Hourly" represents? I need the "Total" to be able to calculate depending on if "Hourly" or "Daily are selected from the drop down list. Hope this makes sense and I appreciate your help in advance.

Thanks (0) By Parvez Aalam
Jun 26th 2015 01:12

Just Type +0 in any cell where you wanted to convert your text Number to Number and then click the cell value where the Number is in text format, That's it you will have the Number in Number format.

Thanks (0) By josgros
Oct 18th 2016 08:27

Hi, I just copied a simple 1 column, 12 rows piece of table containing only numbers and none of the above, nor what´s in your comments like using TRIM or VALUE RIGHT worked. If I use =VALUE(RIGHT(A1,3)) it said "We find a problem with this formula, if i only put (A1) it will work but it will only show the last digit. After the instruction =VALUE(TRIM(A1)) it shows "#VALUE!" but no number. All of the above resulted in similar issues, what´s wrong with this? I can´t believe it´s so difficult to just copy and paste some numbers from a website and try to work on them with Excel. I´m using Excel professional 2013. Any help welcome, thanks!

Thanks (0) By hmyers
Mar 23rd 2018 20:25

We download lots of client's Bank Statements from bank accounts, with debits and credits in textual format with all the handling problem this produces. The easiest and quickest way to handle this is to export the downloaded spreadsheet into an Access database, change the data structure of the imported table from text to numeric (integer or double). Then create a new spreadsheet from this table using the Access export function. And there you are! (Did one earlier today - took 5 minutes)

Thanks (0)