Cell with a lot of names. need to populate in one column based on another column

[jason79121]

Hi everyone,

I have data that is like the below

Month	Name
January	Joe Smith | Jane Doe | Elias Name
February	Jane Doe
March	Joe Smith | Elias Name

Ideally, I would like it to show like this:

Month	Name
January	Joe Smith
January	Jane Doe
January	Elias Name
February	Jane Doe
March	Joe Smith
March	Elias Name

I have been playing around with the transform and transpose fields, with first doing a text to columns to create many columns each with individual names - but am struggling to then get it to the ideal state. Does anyone have any suggestions?

Thanks!

[MilindG · Accepted solution]

@jason79121 Use Text to column tool and select 'Split to rows' option with the '|' delimiter

[jason79121]

That helps separate the columns but does not help me with creating new columns based on the month. Do you have a workflow that can help do that?

[FinnCharlton]

Hi @jason79121 , you can do it with the Text to Columns tool, choose the split to rows option:

Hope this helps!