Data stripping, from rows to columns, and more

[Jodain]

I have about 300+ data entries in rows like:
Row 1: "Doe, John M. - Company Name"
Row 2: "(555) 555-5555 111 Street"
Row 3: "Anywhere, US"
Row 4 is empty
Row 5 is like Row 1, etc.

What I need is to create another spreadsheet with
Column 1: "Doe"
Column 2: "John"
Column 3: "M"
Column 4: "(555) 555-5555"
Column 5: "111 Street"
Column 6: "Anywhere"
Column 7: "US"

I was thinking at first I might be able to make a macro to separate rows (1 +
4x, x=1 to 300) into one column of Row X, and rows (2 + 4x, x=1 to 300) in
another column of Row X, and so on and so forth.

Then, maybe I could condition a script to separate text values following a
comma or space or combination into other columns.

Not really sure how to execute this. Do you have any advice?

 

[JE McGimpsey]

I have about 300+ data entries in rows like:
Row 1: "Doe, John M. - Company Name"
Row 2: "(555) 555-5555 111 Street"
Row 3: "Anywhere, US"
Row 4 is empty
Row 5 is like Row 1, etc.

What I need is to create another spreadsheet with
Column 1: "Doe"
Column 2: "John"
Column 3: "M"
Column 4: "(555) 555-5555"
Column 5: "111 Street"
Column 6: "Anywhere"
Column 7: "US"

I was thinking at first I might be able to make a macro to separate rows (1 +
4x, x=1 to 300) into one column of Row X, and rows (2 + 4x, x=1 to 300) in
another column of Row X, and so on and so forth.

Then, maybe I could condition a script to separate text values following a
comma or space or combination into other columns.

Not really sure how to execute this. Do you have any advice?

See one answer in response to your later post...