Transpose list of address into rows for mailing list

[LizE]

I have a list of addresses that are all in one column but they are not equal
in lenght, some have 3 lines and others have 4 lines, there is no row
separating each record. I would like to use this data for mailing list. So
I'd like to see the information as follows:
Name Address 1 Address 2 City/St/Zip

I know how to break up the City/ST/Zip data.

Transpose works but I have to do each group separately and it's taking me
forever.

Thanks for your help.

 

[LizE]

Sorry- I just check my post b/c I've been gone for a few days. I tried this
but it doesn't work - I get a syntax error from the first formula. It tells
me that
=IF(A2="",100*INT(A1/100)+101,
IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1))) is invalid when I
click on the Fx help.

 

[David Biddulph]

Your formula is not invalid.
Do you have strange unprintable characters (perhaps at the line breaks) in
your version?

 

[LizE]

This is what I hav
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1)))

 

[David Biddulph]

No syntax error for me from that formula.

You don't, by any chance, have your machine set up (Windows/ Regional
Options) to use a semi-colon, rather than a comma, as a list separator, do
you?

 

[thmarine]

Can you please tell me how to make this work for a similar situation. What
you have shown works for me, except I have exactly seven (7) rows followed by
a space for each entry, as follows:
A1=Name
A2=Street
A3=City, State, Zip
A4=phone
A5=fax
A6=e-mail address
A7=web site
A8=<blank row>
then pattern repeats.

I want those to be transposed to colums from rows. Just like the original
poster.

I used your formula and it worked fine for my first six(6) rows, but my 7th
row did not go into my 7th column, it is blank. Is there something about the
formula that needs changing to work for 7 instead of 4 rows?

Thanks!!!

 

[thmarine]

NEVERMIND! I figured it out.

In case anyone is interested, and working with a different number of rows to
transpose, the variable is in this formula that he gave:
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",
IF(TRIM(B4)="",100*INT(A2/100)+4,A2+1)))

I changed the +4 to a +7 and it made it work for a list of 7 rows. I
presume that means you could make that any number that equals the maximum
number of rows you are dealing with.