blank spaces in front of a number prevents formatting cell

[Guest]

I was sent an email with a spreadsheet. The columns with numbers cannot be
reformatted or summed up because there is a blank space in front of the
number and two blank spaces after the numbers. When I try to reformat the
cells to currency, text or anything else it does not work. When I retype the
number in the cell , then it works but I have 600 cells with these #'s and I
hate to retype. I have tried =trim and =clean but these work for text and not
numbers. I have searched and searched but cannot find a way to take out the
blank spaces so that these cells can be formatted to currency so I can sum
them up. I am just going to retype them but in the future I am curious if
anyone else has come up with a solution to this without retyping.

 

[Guest]

Are you sure they are blanks or just non-printable characters? TRIM will work
if they are blanks.

Try this for one cell:

=VALUE(SUBSTITUTE(A1,CHAR(160),""))

 

[Guest]

Thank You Toppers,,,
That worked beautifully.

Teri