Name Splitting and moving

[sx200n]

Hello,

I hope someone can help. I have a really rubbish system extract that needs work doing to the names of clients. Basically some policies can have multiple clients and this really poor extract gives all names within the same cell. I need them spliting up and then moving into the correct cells.

I have attached an example. I am not sure if it can be done, but I am sure someone on here can tell me if not.

On the example - I have the exact format from the extract on the left, and ideally on the right how I would like it to end up looking.

Any thoughts? The Find and replace (to remove & signs) and then the text-to-columns works fine when separating them. But then they are all in a horizontal line.

Kind Regards

[JohnTopley]

After the initial text to columns, copy/past the other columns to follow those from the first column and re-do "text to columns" with space as delimiter, This worked fine on your sample,

You may have to "TRIM" the data to remove any extraneous blanks before the "text-to-column"