Need to transpose one column into multiple with varying data

I have names and addresses in one column with about 15000 rows. Need the information transposed into 4-5 columns. Some addresses are 2 rows and others are 3 rows. Any help would be great.

Example of data:

Bob Smith
123 South St.
Anytown, NY 12345
George Jetson
5438 Main St.
Apt. 2
Smallville, GA 87654

The data needs to be transposed into:
Name/address1/Address2/City, ST Zip

Thanks,
Topher

Do you always have a comma in the row which has the city and state, and is that the only place where a comma can appear?

Pete

Pete,

Thanks for your help! Yes, this is the only place a comma will appear.

You say that you need the data transposed into 4-5 columns, but then your examples only need 3 and 4 and you state: Name/address1/Address2/City, ST Zip for 4 columns. I assume you will want the City state and zipcode aligned in the same column throughout, so what is the maximum number of columns you are likely to need?

Pete

Sorry about that. 4 columns would be the most I need.

The attached file shows how you can do this with a few formulae. I've put your example data (and some more made-up ones) in column A starting from A1, then inserted a new row 1 and put a single comma in the new cell A1. Then in B2 I put this formula:

=IF(A2="","-",IF(ISNUMBER(FIND(",",A1)),INT(MAX(B$1:B1)/10)*10+10,B1+1))

and copied this down beyond your data (the hyphens indicate how far it has been copied). This sets up a sequential numbering for each record (which will be identified by a number in increments of 10, i.e. 10, 20, 30 etc., and then each subsequent field will be identified by the record number plus 1, 2 or 3. (I did initially use integers for the record numbers and fractions for the field numbers, but I had to keep using ROUND( ...,1) in the exact matches, so it became very messy).

Then in E2:H2 I have various formulae based around INDEX/MATCH which bring the appropriate fields across and take account of only 3- or 4-line records. These formulae can be copied down as far as you need them (until you start to get hyphens in column E).

If you are not interested in retaining the original data in this file, then fix the values in columns E to H, delete columns A to D, then use File | Save AS to save the file with a different name.

Hope this helps.

Pete

Pete,

Thank you for your assistance. That worked perfectly!