Removing spaces from cells not possible?

[Robert M. Gary]

If I copy/paste a table from a browser into Excel it seems I can never
remove the spaces before/after the numbers in the cells. I've used
=value(trim(clear(B1))) and then copy/pastespecial(values) but still I
get "10 " (with the end space). Its very frustrating because I cannot
sum the column. I see there is add on software that you can buy to
remove spaces from cells but it seems so very ridiculous that Microsoft
hasn't solved this yet.
The only valuable suggestion I can find on the MS site is to retype the
numbers. I believe they think copy/paste may be overrated.

-Robert

 

[Biff]

Try the macro at this site:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

It's free! (and it works!)

Biff

 

[Ron Rosenfeld]

If I copy/paste a table from a browser into Excel it seems I can never
remove the spaces before/after the numbers in the cells. I've used
=value(trim(clear(B1))) and then copy/pastespecial(values) but still I
get "10 " (with the end space). Its very frustrating because I cannot
sum the column. I see there is add on software that you can buy to
remove spaces from cells but it seems so very ridiculous that Microsoft
hasn't solved this yet.
The only valuable suggestion I can find on the MS site is to retype the
numbers. I believe they think copy/paste may be overrated.

-Robert

Usually that space is CHAR(160) and can be removed using the SUBSTITUTE
function.

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

or

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

If that doesn't do it, there are other methods.
--ron

 

[Robert M. Gary]

Usually that space is CHAR(160) and can be removed using the SUBSTITUTE
function.

Your right. After doing a find replace for 0160 replace with "", it
worked. I can't believe Microsoft hasn't addressed this with =clean()
yet. Isnt' think was =clean is for?

-Robert

 

[Ron Rosenfeld]

Your right. After doing a find replace for 0160 replace with "", it
worked. I can't believe Microsoft hasn't addressed this with =clean()
yet. Isnt' think was =clean is for?

-Robert

I would have thought so.

Glad it worked.
--ron