software

I'm using the Text to Columns wizard to separate out a comma delimited
address field. Works great, but afterwards the data doesn't always
match with the correct column, i.e. city, state, zip.

For Example, after the split I end up with the following scenario:

ADDRESS 1 | ADDRESS 2 | CITY | STATE | ZIPCODE
----------------------------------------------------------
1234 Street | Los Angeles | CA | 90028 | BLANK CELL

What's the best way to go about removing the blank cell or cells and
shifting to the right until the zip code lines up with the zip code
column? I figured I could write out a conditional clause to search the
range for 1 blank cell and insert and shift to the right or search for
2 contiguous blank cells and insert and shift to the right twice, but I
don't have the time to research the code.

Any help is greatly appreciated.

Thanks.I created this little macro to tidy up after this sort of situation

Sub ConcatenateNext()
With Selection
.Value = .Value amp; quot; quot; amp; .Offset(0, 1).Value
.Offset(0, 2).Resize(1, 100).Copy .Offset(0, 1)
End With
End Sub

I store it in Personal.xls, and assign it to a button on the format toolbar.
After splitting it I can quickly tidy up.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Carletonquot; gt; wrote in message oups.com...
gt; I'm using the Text to Columns wizard to separate out a comma delimited
gt; address field. Works great, but afterwards the data doesn't always
gt; match with the correct column, i.e. city, state, zip.
gt;
gt; For Example, after the split I end up with the following scenario:
gt;
gt; ADDRESS 1 | ADDRESS 2 | CITY | STATE | ZIPCODE
gt; ----------------------------------------------------------
gt; 1234 Street | Los Angeles | CA | 90028 | BLANK CELL
gt;
gt; What's the best way to go about removing the blank cell or cells and
gt; shifting to the right until the zip code lines up with the zip code
gt; column? I figured I could write out a conditional clause to search the
gt; range for 1 blank cell and insert and shift to the right or search for
gt; 2 contiguous blank cells and insert and shift to the right twice, but I
gt; don't have the time to research the code.
gt;
gt; Any help is greatly appreciated.
gt;
gt; Thanks.
gt;