How to delete commas and spaces when not needed ... S&R way, or macro way?
On Mon, 1 Jun 2009 18:22:59 -0700, "Jim Cone"
wrote:
With the list starting in B5...
Fill column C with trimmed data (remove leading/trailing spaces)
Is there a way to automate removing leading/trailing spaces? That
would be so cool. The fields have more than one word in them so that
I can't just do a S&R for spaces.
I'm guessing that the leading/trailing spaces are why this doesn't
work 100% ... (?)
Use this formula in D5 _
and fill down... =IF( LEFT(C5,1) = ",",TRIM(MID(C5,2,255)),C5)
Use this formula in E5 _
and fill down... =IF( RIGHT(D5,1) = ",",TRIM(LEFT(D5,LEN(D5)-1)),D5)
Copy and paste values.
--
Jim Cone
Portland, Oregon USA
(You don't accept Spam, I hope you don't send any)
<lol I sure as heck don't! Never have, in all my years, though it's
just an easy way to put an email address vs. a munged one. <g
"StargateFan"
wrote in message
I was wondering how to clean up a couple of columns. I have
concatenated two fields together that had blank cells in some places.
The results I got after copy-pasting the concatenates formulas into
values show up in these three ways, the first one being okay, the
other two not:
Address1, Address2
(okay)
, Address2
(not okay, need to get rid of comma and space _before_ text)
Address1,
(also not okay, need to get rid of comma and space _after_ the text).
Is there an easier, quicker way to clean up the entries of these
commas and spaces where they're not needed vs. doing the clean up
manually?
Thanks! :oD
|