Split Address Correction
 

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.

Split Address Correction
 

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

Sub ConcatenateNext()
With Selection
.Value = .Value & " " & .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)

"Carleton" wrote in message
oups.com...
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.