Data transpose from row's to columns - HELP PLEASE

[UllrichNumber7]

Hey I need some help with some data reformatting via excel. I believe I need to create a set of macros and IF statements for this, but I definitely need some help and do not know where to start.

Here is my situation:

I have a excel document 521,165 Rows in it, all in column A. The issue with this is, its full of data. The entries need to be elongated into 1 row per entry, not anywhere from 3-6 rows per. This is a list of contact information, including company names, address, city state zip, email, and phone number. Each comma is a new row on the spread sheet. That being said, not every entry has a email, phone, or all of the information. What I need to do with it is turn it from:

Jane Doe inc.
Example lane
City, state, 81346
example@gmail.com
(808) 064-2654

But some of the entries may be
Jane Doe inc.
Example lane
City, state, 81346
(808) 064-2654

(missing some, you get the point)

Into 1 row, with each data in its own separate cell. We also need to break the city, state, and zip into their own cells respectively so that we can continue to sort the data by state, zip, city, etc. Can someone please help? I have no idea where to go with this and it is simply not feasible in anyway to copy and past + transpose to reformat this. There must be a different way!

Help is greatly appreciated!

Daniel

 

[UllrichNumber7]

Essentially I think I would need a logic statement macro, That says select phone number and up, if phone number isnt there, select email and up, if email isnt there select,city state and up, if city state isnt there select street and up, and then copy, special paste transpose to a new sheet then delete selection rows and then repeat.

 

[Slimm]

In a new worksheet I would map back to this worksheet, if the email option is the only difference i'd use a logic statement looking for the "()" or "@" symbol. Then use Text to column on the City, State, Zip to separate them into their own columns

 

[UllrichNumber7]

In a new worksheet I would map back to this worksheet, if the email option is the only difference i'd use a logic statement looking for the "()" or "@" symbol. Then use Text to column on the City, State, Zip to separate them into their own columns

There is no way for me to know if email is the only inconsistency without combing through thousands of rows though? I know email is for sure one inconsistency, but I do not know if some of them may be missing address, phone, etc.

 

[UllrichNumber7]

In a new worksheet I would map back to this worksheet, if the email option is the only difference i'd use a logic statement looking for the "()" or "@" symbol. Then use Text to column on the City, State, Zip to separate them into their own columns

What would this logical statement be?

 

[UllrichNumber7]

I am still really struggling with this. Someone please help!