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.