Three Ways to Convert Text-Based Numbers to Values

computertype_baona
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:

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

About David Ringstrom, CPA

David Ringstrom

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.

avatar
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 (1)
avatar
By David Ringstrom
to peonysox
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 (1)
avatar
to jpb
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
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.

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

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

Thanks (0)
avatar
By David Ringstrom
to SanjayDarji
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 (1)
avatar
By Naveen
to seth fineberg
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)
avatar
By David Ringstrom
to bltaxes
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)
avatar
By Naveen
to bltaxes
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
to JamieEllisCOO
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)
avatar
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)
avatar
By David Ringstrom
to Nordstromtalent
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)
avatar
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)
avatar
By David Ringstrom
to Rowan Webb
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)
avatar
By Gary
to sb96
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)
avatar
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)
avatar
By David Ringstrom
to Smita Ashok
Jun 26th 2015 01:11

See my reply to Joey below.

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

I like solution number 3. Thanks a lot

Thanks (0)
avatar
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)
avatar
By Christo
Jun 26th 2015 01:12

Ignore the last one, worked with wrong set of data

Thanks (0)
avatar
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)
avatar
By David Ringstrom
to FidelLindamood
Jun 26th 2015 01:12

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.

Thanks (0)
avatar
to Christo Snyman
Dec 11th 2017 09:55

it is working, thanks!

Thanks (0)
avatar
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)
avatar
By David Ringstrom
to BennettGoldrick
Jun 26th 2015 01:12

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.

Thanks (0)
avatar
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)
avatar
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)
avatar
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)
avatar
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)
avatar
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)
avatar
Oct 30th 2017 11:56

In excel we can do all these tasks in an effective manner. Excel provides lots of mathematical formulae in order to calculate the values. Thank you for sharing details regarding the different ways to convert text based numbers to values.cheap shared office spaces

Thanks (0)
avatar
Dec 6th 2017 05:18

I've been struggling with this for some time with some of my spreadsheets and came across this article. Thank you for using pictures and simple steps that even this electrician can understand!

Thanks (0)