Removing spaces from cells not possible?
 

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

Removing spaces from cells not possible?
 

Try the macro at this site:

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

It's free! (and it works!)

Biff

"Robert M. Gary" wrote in message
ups.com...
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

Removing spaces from cells not possible?
 

On 10 Oct 2006 19:46:18 -0700, "Robert M. Gary" wrote:

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

Removing spaces from cells not possible?
 

Ron Rosenfeld wrote:

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

Removing spaces from cells not possible?
 

On 11 Oct 2006 11:46:10 -0700, "Robert M. Gary" wrote:


Ron Rosenfeld wrote:

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

I would have thought so.

Glad it worked.
--ron