Removing Spaces

[jpxsjpxs]

Hi, I'm trying to copy an income statement from sec.gov and dump it into Excel (newest version). As I bring over the numbers and paste them into excel the numbers have some spaces in front of them and they are not registering as numbers. I read somewhere that if you put =code(a1) you can see what type of space it is. I get back the answer 160 which I think is char160 which seems to be a pian to work with. Is there a formula I can use to get rid of the char 160 in order to get the numbers in the cells to show up as numbers where I can then create formulas? I already tried =trim(substitute(a1,char(160)," ")) which didn't seem to work? Any help would be greatly appreciated. Thanks!!

 

[jmthompson]

=substitute(A1,char(160),"")

 

[Joe4]

Here are a few ways to get rid of that shown here: http://www.excelforum.com/excel-programming/520859-getting-rid-of-char-160-a.html

 

[jpxsjpxs]

hi, the formula you gave me worked to get rid of the space, but the number in the cell still seems not to be a number. For instance, I couldn't take the result after the sub formula and add them together to get a result? Thanks!!

 

[jmthompson]

=1*(substitute(e15,char(160),""))

 

[jpxsjpxs]

Thanks Jen, that worked. That's a great help!!!!!!