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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John13 - 30 Nov 2007 17:37 GMT

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

Reply to this Message

Ron Rosenfeld - 30 Nov 2007 17:53 GMT

>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
[quoted text clipped - 10 lines]
>
>John13

It may be an NBSP. Try:

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

--ron

Reply to this Message

John13 - 30 Nov 2007 18:11 GMT

> >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
[quoted text clipped - 16 lines]
>
> --ron

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

John

Reply to this Message

Ron Rosenfeld - 30 Nov 2007 18:41 GMT

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

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

Reply to this Message

Dave Peterson - 30 Nov 2007 18:00 GMT

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

> 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
[quoted text clipped - 10 lines]
>
> John13

Signature

Dave Peterson

Reply to this Message

Rate this thread: