Text to rows within a list

[jodillee]

Hi you amazing excel minds. I have been having problems with a rooming list and I know there must be a simple solution. I'm not really good with code and wouldnt really know how to apply it but my problem is this:

I have a spreadsheet which pulls from our system and imports to excel with names backwards and all merged into one cell as per the "capture" attachment.
Ideally I would like to split the names at the comma but have the text after the comma inserted in a new row below.

I ultimately want the list to show the clients names in the proper order. Currently I have to split the text by inserting new empty columns in firstly and using the Text to columns function with the comma delimited. I then have to insert a new row on every other line below and paste the second client name below the first. Once they are all lined up I then use the text to columns function again with the delimiter by space so it separates them out with the title, surname and first name in separate columns. I then have to cut and paste the Mr/Mrs title column to the front of the name so it shows in the correct order. I ideally want the list to look like the second attachment. Is there an easier way of doing this because it is ridiculously labour intensive for what it is!

Can any body help?

[daffodil11]

Any chance you could attach a sample of your work so we don't have to create one from scratch? That would also be labor intensive.

[jodillee]

Any chance you could attach a sample of your work so we don't have to create one from scratch? That would also be labor intensive.

I will tomorrow. Thank you sorry I thought there might be an easy answer. Your help would be much appreciated.

[jodillee]

An example of the original spreadsheet is now attached. Sorry and thank you!

[oeldere]

with text to column the data ared in U6 and U7. I showed only 2 rows since you have merged cells in your file. The result is OK?

[jodillee]

Thank you for your help - I can already do this with the 'text to columns' function.

I know the cells are merged - I don't do that myself this is how it comes out on our system hence my question. Ideally Id like to be able to create a row
below for the second name so I can manipulate them but it doesn't seem to be as easy as that sadly.I have to insert rows in between manually and paste the name below.

If anyone has any ideas on this I would really appreciate it.

Jody

[oeldere]

Then maybe like this.

First unmerg all data.

After that text to column and add the data directly in column U and V (no columns added or removed).

[jodillee]

Hiya,

Thank you for your suggestion unfortunately with the names I can't just unmerge them -
the two names are both within the same cell. If you unmerge it splits the cells but with both names just in the one cell
so still requires splitting out. Seems like this is more difficult than I thought.

Thank you for trying though!