Cleaning blank spaces before and after text in a copy & paste problem

[John13]

If I cut and paste this (see below) from an email body to Excel and do
a Text to Columns, I can't remove the blank spaces before and after
each of the three columns. I have tried =TRIM(cell) and =CLEAN(cell)
but to no avail. I made sure the columns were in the Text format.
This is just a small sample, otherwise I would retype it. I tried
Paste Special and tried all the optional pasting types. I am missing
something, so could someone point me in the right direction? Thank
you.

Jones Foley, AL 12/18/07
Smith Harriman,NY 1/15/08
Thomas Secaucus, NJ 1/15/08


John13

 

[Ron Rosenfeld]

If I cut and paste this (see below) from an email body to Excel and do
a Text to Columns, I can't remove the blank spaces before and after
each of the three columns. I have tried =TRIM(cell) and =CLEAN(cell)
but to no avail. I made sure the columns were in the Text format.
This is just a small sample, otherwise I would retype it. I tried
Paste Special and tried all the optional pasting types. I am missing
something, so could someone point me in the right direction? Thank
you.

Jones Foley, AL 12/18/07
Smith Harriman,NY 1/15/08
Thomas Secaucus, NJ 1/15/08


John13

It may be an NBSP. Try:

=SUBSTITUTE(cell,char(160),"")


--ron

 

[Dave Peterson]

Grabbing stuff from a web page or from an HTML/RTF email may mean that the
"space" characters aren't really spaces. They could be those HTML non-breaking
spaces (=char(160)).

You may be able to use:
=trim(substitute(a1,char(160)," "))
to clean them

Or you could use a macro:

David McRitchie has a macro to clean this kind of stuff:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[John13]

It may be an NBSP. Try:

=SUBSTITUTE(cell,char(160),"")

--ron

Thanks Ron, that worked. Thanks for the quick reply on a Friday!

John

 

[Ron Rosenfeld]

Thanks Ron, that worked. Thanks for the quick reply on a Friday!

You're welcome. Glad to help.
--ron