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.

**About the author:**

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

### About David Ringstrom, CPA

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

## Replies

## Please login or register to join the discussion.

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

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.

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

2. Saved spreadsheet as anything.CSV

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.

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

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.

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

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 David, this will show the zeros but when I do Vlookup it is not recognised.

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

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.

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!

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

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),""))

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?

See my reply to Joey below.

I like solution number 3. Thanks a lot

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.

Ignore the last one, worked with wrong set of data

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

it is working, thanks!

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

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.

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

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.

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.

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!

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)

## Please login or register to join the discussion.