pivot address info to columns

[DWS]

I have 20,000 rows of address info that I would like to
parse into columns...data is in repeating format:

Name
Address
City, ST, Zip
Phone

Suggestions

 

[Frank Kabel]

Hi
is this kind of data in one cell or in 4 cells?

 

[DWS]

Data is in four cells vertically in one column.

-----Original Message-----
Hi
is this kind of data in one cell or in 4 cells?

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Frank Kabel]

Hi
try the following formula in cell A1 on a separate sheet (assumption:
your source data is on sheet 1, starting in cell A1):
=OFFSET('sheet1'!$A$1,(ROW()-1)*4+COLUMN()-1,0)
and copy this formula 4 cells to the right and as far down as required.
After this select the new data range and copy it. Goto 'Edit - Paste
Special' and choose 'Values' to remove the formulas

 

[DWS]

Thank you for the help...works on the first row however
successive rows the formula places the data offset
further to the right in the successive rows then returns
at cell A6.

Appreciate your help and thoughts.

 

[Frank Kabel]

Hi
do you have blank rows in between in your source data?

 

[Guest]

every fifth row is blank...I don't know if the formating
will hold in this email...if it does the data looks like
this:

Name Address Citystatezip phone
0 Name Address Citystatezip phone
0 0 Name Address

ect for four iterations then returns on the sixth row.

Gratefully!

Dave

 

[Frank Kabel]

Hi
then use the following formula:
=OFFSET('sheet1'!$A$1,(ROW()-1)*5+COLUMN()-1,0)

 

[DWS]

THANK YOU! worked perfectly...

-----Original Message-----
Hi
then use the following formula:
=OFFSET('sheet1'!$A$1,(ROW()-1)*5+COLUMN()-1,0)


--
Regards
Frank Kabel
Frankfurt, Germany



.