Help moving data from one column into multiple columns

[Dampland]

MS Office 2016 / Window 10 PC


So today I was given a excel spreadsheet with one column of data

Column A has the the following info in descending rows

Name
Street Address
City, State, Zip Code
Phone #


Then the next row down starts over the process with the next name, etc.



So I have a 16,000 row spreadsheet that looks like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]Joe Smith
[/TD]
[/TR]
[TR]
[TD]123 Road[/TD]
[/TR]
[TR]
[TD]New York, NY 11111[/TD]
[/TR]
[TR]
[TD](123) 456-7890[/TD]
[/TR]
[TR]
[TD]Bob Johnson[/TD]
[/TR]
[TR]
[TD]999 Street[/TD]
[/TR]
[TR]
[TD]Boring, OR 99999[/TD]
[/TR]
[TR]
[TD](751) 555-1212[/TD]
[/TR]
[TR]
[TD]Janet Customer[/TD]
[/TR]
[TR]
[TD]666 Heaven Road[/TD]
[/TR]
[TR]
[TD]Las Vegas, NV 88765[/TD]
[/TR]
[TR]
[TD](444) 555-6666[/TD]
[/TR]
</tbody>[/TABLE]

I'm sure there has to be a way to make a formula to convert each address to multiple columns moving left to right

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Street[/TD]
[TD]City, State, Zip[/TD]
[TD]Phone[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe Smith[/TD]
[TD]123 Road[/TD]
[TD]New York, NY 11111[/TD]
[TD](123) 456-7890[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob Johnson[/TD]
[TD]999 Street[/TD]
[TD]Boring, OR 99999[/TD]
[TD](751) 555-1212[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Janet Customer[/TD]
[TD]666 Heaven Road[/TD]
[TD]Las Vegas, NV 88765[/TD]
[TD](444) 555-6666[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


or even better would be to break up the City, State an d Zip into their own columns.

As I said, the list given to me is 16,000 rows of data, If I do this by hand it will take me a week. Any suggestions?

 

[Dampland]

Well I finally found a macro in my ASAP Utilities (paid for) add on. It took care of the first part, and then I did some Text To Columns work for the rest.

Thanks ASAP Utilities!