Move text into columns from rows

Hello All,

I have a set of data that has a store number in cell A1, the store name in B1 and then the store address in C1, C2, and C3. This pattern repeats for all ~300 stores. I am trying to get all of the store data on one row per store.
I have a set of data that looks like this:

.......A........|........B..............|.....C..........................D....................E.................F
1 STORE#..|..STORENAME...|..ADDRESS
2 1024.......|..ONTARIO1.....|..2138 Ellwyn Drive
3 ..............|....................|..Markham ON
4 ..............|....................|..Canada, H4A 3L2
5 1025.......|..ONTARIO2.....|..95 Dunberry Street
6 ..............|....................|..Windsor ON
7 ..............|....................|..Canada Q1A 4T6[/CODE]
etc.
There are over 300 stores.
What I am trying to do is get the second and third lines from the address onto the same row as the store number in columns D, E, and F. Is there a way to do this without doing it by hand? I have very limited Excel programming skill but am very willing.
Thanks!

Post a workbook.

Hello clacka,

Will the address always be 2 lines following the store number?

Hello clacka,

This macro has already been added to the attached workbook. This will take the 2 address rows and put them into cell "E" and "F" of the row with the store number and name. The blank rows are then deleted.

Please Login or Register  to view this content.