How to pull out names from a Comma Separated list in box?

Graham Rimell

I've got a Name column that gets populated with either one name or several names separated by commas.

eg. John Smith, Michael Jones, Harry Hill

What I'd like to be able to do is have a set of columns off to the right with Name 1, Name 2, Name 3, Name 4 etc. All pulling the individual names if there is more than one. If there isn't more than name one I assume I could use an IFERROR to make it blank.

Thanks.

Ramzi K

@GrahamR

Here's one way to do it:

First column contains names separated by commas

Column Name 1 formula:

=IF(FIND(",", Names@row) = 0, Names@row, LEFT(Names@row, FIND(",", Names@row) - 1))

Columns Name 2 to Name n formula:

=IF(FIND(",", IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000))) = 0, IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000)), LEFT(IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000)), FIND(",", IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000))) - 1))

I hope this helps,

Cheers,

Ramzi

Ramzi K

@GrahamR

Here's one way to do it:

First column contains names separated by commas

Column Name 1 formula:

=IF(FIND(",", Names@row) = 0, Names@row, LEFT(Names@row, FIND(",", Names@row) - 1))

Columns Name 2 to Name n formula:

=IF(FIND(",", IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000))) = 0, IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000)), LEFT(IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000)), FIND(",", IF(ISBLANK([Name 1]@row), "", MID(SUBSTITUTE($Names@row, JOIN($[Name 1]@row:[Name 1]@row, ", "), ""), 2, 1000))) - 1))

I hope this helps,

Cheers,

Ramzi

Graham Rimell

Thanks!

I've got the first two names working, but I can't quite work out what I have to change to get the names 3 and onwards to work. Which part of the formula do I have to change?

Graham Rimell

I've almost got it working

I realised I'm being really stupid just needed to drag the formula along and it auto changes to the next name. Sorted!

Thanks for your help.

Matt Kirby

This solution was helpful for my use case as well. Is there a way to make the formula valid for column formula usage? I get an error message when trying to apply it as a column formula.