How to shift/offset cells

[ehesh]

I am trying to create a spreadsheet from contact information I was handed in a text file and I have noticed that I have “missing spaces” and cannot find a method of shifting the cells to properly store the details.

For example the information is listed

Company, SKU, Phone number 1, Phone number 2, Email
Co. 1, 0000, 800-xxx-xxxx, 800-xxx-xxxx, co.1@email.com
Co. 2, 1111, co.2@email.com
Co. 3, 2222, 800-xxx-xxxx, co.3@email.com

I am trying to format it:

Company, SKU, Phone number 1, Phone number 2, Email
Co. 1, 0000, 800-xxx-xxxx, 800-xxx-xxxx, co.1@email.com
Co. 2, 1111, , , co.2@email.com
Co. 3, 2222, 800-xxx-xxxx, , co.3@email.com

I have so far managed to place a delimiter that will allow me to split the cells based on the comma as a delimiter. But I end up with, as indicated in my example above, with the email on the telephone 1 column etc.

[Edited to add formatting tags]

[LeroyG]

@ehesh,

Seeing no other answer… a simple one (surely there are better ways). No macros, but a simple formula, after which you will need to sort and move by hand. See sample file.

Sample file.

[ehesh]

I am trying to avoid moving by hand since they are about 300 contacts

[LeroyG]

Once sorted by column F, only three moves are needed.

[EarnestAl]

I have used @LeroyG 's sheet because I’m lazy, ReAlignFields.ods. For my solution I am using “@” to check what is in columns CD&E so there can’t be other fields than those defined but it kinda works except sometimes you’ll get a 0 turning up. So it returns the table of data in 5 new columns, G to K.
Cheers, Al

[LeroyG]

It’s OK to reuse (and to improve). I reused @ehesh data with @robleyd format.