Also the "NameX" can be appeared twice for a single contact as follow:

> Name1 (font:Arial size:10 and Bold)
> Address1 (font:Times New Roman size:10 Not Bold)
> Address1.1 (font:Times New Roman size:10 Not Bold)
> City1 (font:Times New Roman size:10 Not Bold)
> Name2 (font:Arial size:10 and Bold)
> Name2.2 (font:Arial size:10 and Bold)
> Address2 (font:Times New Roman size:10 Not Bold)
> Address2.2 (font:Times New Roman size:10 Not Bold)
> City2 (font:Times New Roman size:10 Not Bold)


"Paul" <paul_mak@shaw.ca> wrote in message
news:egoMpZMOGHA.456@TK2MSFTNGP15.phx.gbl...
>I have an Excel workbook with the contact information in it, I need to
>re-arrange it in orfder to to do some data search. The layout of the data
>is in a single column as follow:
>
> Name1 (font:Arial size:10 and Bold)
> Address1 (font:Times New Roman size:10 Not Bold)
> Address1.1 (font:Times New Roman size:10 Not Bold)
> City1 (font:Times New Roman size:10 Not Bold)
> Name2 (font:Arial size:10 and Bold)
> Address2 (font:Times New Roman size:10 Not Bold)
> Address2.2 (font:Times New Roman size:10 Not Bold)
> City2 (font:Times New Roman size:10 Not Bold)
> .
> .
> .
> and so on
>
> The only trick is for some of the contact it does not have the second line
> of the address (i.e. Address1.1) and the contact information is shown as
> follow:
>
> Name1 (font:Arial size:10 and Bold)
> Address1 (font:Times New Roman size:10 Not Bold)
> City1 (font:Times New Roman size:10 Not Bold)
> Name2 (font:Arial size:10 and Bold)
> Address2 (font:Times New Roman size:10 Not Bold)
> Address2.2 (font:Times New Roman size:10 Not Bold)
> City2 (font:Times New Roman size:10 Not Bold)
>
> I need to put the "AddressX" and "AddressX.X" and "CityX" into a column
> and it will take me forever if iI have to copy and paste it one by one. Is
> there a way I can use the vba code to achieve this? Thanks.
>