Help moving data from one column into multiple columns

Dampland

Board Regular
Joined
Jul 25, 2014
Messages
85
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
Joe Smith
123 Road
New York, NY 11111
(123) 456-7890
Bob Johnson
999 Street
Boring, OR 99999
(751) 555-1212
Janet Customer
666 Heaven Road
Las Vegas, NV 88765
(444) 555-6666

<tbody>
</tbody>

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

NameStreetCity, State, ZipPhone
Joe Smith123 RoadNew York, NY 11111(123) 456-7890
Bob Johnson999 StreetBoring, OR 99999(751) 555-1212
Janet Customer666 Heaven RoadLas Vegas, NV 88765(444) 555-6666

<tbody>
</tbody>


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?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Dampland

Board Regular
Joined
Jul 25, 2014
Messages
85
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,477
Messages
5,444,711
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top