Jun 28th 2013

34

Advert Advertise with us

*By David Ringstrom, CPA*

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.

Read more articles by David Ringstrom.

**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* *david@acctadv.com** or follow him on **Twitter**. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner **CPE Link**.*

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

Hi David,

I have a situation with imported data to a large table. The numbers in one column represented as text. I used the TRIM function and realized that it has a problem. In my case all the text numbers have trailing spaces which are not eliminated by TRIM. What could be used instead or is this a bug in the TRIM?

Thanks.

It's not a bug in TRIM, but rather an issue with your data. Try using the CLEAN function in conjunction with TRIM because what looks like spaces are likely non-printable characters. Or, if that doesn't work, copy one of the "spaces" to the clipboard and do a find and replace to replace that "space" with nothing. TRIM eliminates spaces, but what you have is some sort of non-printable character masquerading as a space.

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

Thank you for the outstanding addition to my article. The entire formula doesn't appear to be displaying in the comments, but I'll add it as a new comment below for anyone that needs it.

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

It's my personal favorite as well. Thank you for the feedback!

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

Thanks for the great addition to my article, Christo. I should have included that technique in my original run down. Non-printing characters masquerading as spaces can be maddening. Another approach that sometimes works is to use the CLEAN function to strip them out, but Find/Replace as you used it is always a good approach as well as long as you limit the area where you're replacing, as you can accidentally cast a wider net than expected.

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

I'm sorry that I'm just now seeing your comment. #N/A in a SUM is not an indication of numbers stored as text. Instead one or more of the formulas that you're trying to sum is returning #N/A. Very often this is due to a VLOOKUP that can't find a match. Use the IFERROR function in Excel 2007 and later to have the formula that's returning #N/A display a zero or something else when a match can't be found.

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

Fantastic! Thank you so much for sharing your experience! Text to Columns is absolutely one of my favorite Excel features.

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.

You are describing a different scenario than the other readers. It would appear that you need an IF statement. Assuming that cell A1 will either contain the words Hourly or Daily, and that your hourly amounts are in F3:AJ3 and daily amounts are in F4:AJ4 then a formula like this should do what you want:

=IF(A1="Hourly",SUM(F3:AJ3),SUM(F4:AJ4))

It's tough to accurately explain scenarios like this without actually seeing your spreadsheet.

David, you truly are a genius! I have been searching for this problem to be solved for a long time and you have finally done it. I understand it's hard when you can't see it. The Hourly and Daily amounts are based upon the same cell range (Hourly is SUM of F3:AJ3 whereas Daily is COUNTA F3:AJ3). I just made the necessary changes to your formula above and it has worked.

Thank you ever so much

My pleasure. Thank you for letting me know that my solution solved your issue!

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.