Address list in Excel

I have a list of addresses in Excel and the street and city are in the same column.  How do I move them to separate columns?

116 Dogwood Rd
Roslyn
 
288 Maple Hill Drvie
Hackensack
 
33 Howard Street
Reading
 
3000 N Ocean Drive #5A
Singer Island 
 
P.O. Box 970062
Boca Raton

Answer
Answer

A solution using Excel Formulas. Refer below image:

Enter below formula in cell C2 & copy to the right & to the bottom - presumed that a blank cell / row separates addresses in column A:

=IF($A1="",IF(MATCH(TRUE,INDEX(ISBLANK($A2:$A$100),0,0),0)-ROW($A$1)>=COLUMNS($C$1:C$1),OFFSET($A2,MIN(COLUMNS($C$1:C$1)-1,MATCH(TRUE,INDEX(ISBLANK($A2:$A$100),0,0),0)-ROW($A$1)-1),0),""),"")

You may also download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/RowsToColumns_1.xlsx

Regards,

Amit Tandon

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated October 5, 2021 Views 112 Applies to: