Using Power Query to split a column at a variable position

Question

My data looks like this...

Column A
Dr. Sam Karta Jr., CEO, Tallis Partners
Fred Sampson, III, Director, Aspen Institute
Ruth Kaufmann, PhD, Executive Director
Marci Cheng, CHRO

I need Power Query to split this column so that job titles — but not the suffixes (e.g., PhD, Jr., III, etc.) — transfer to a new column. Not everyone has a suffix, and each row entry has a different number of commas, so I cannot reliably split using a delimiter.

Instead, I want PQ to look for the existence of a suffix (e.g., PhD, Jr., III, etc.) then split any remaining text (i.e., text after the suffix) into a new column. If a suffix does not exist (see Marci Cheng), PQ splits at the first comma.

So, the data would look like this...

Column A	Column B
Dr. Sam Karta Jr.	CEO, Tallis Partners
Fred Sampson, III	Director, Aspen Institute
Ruth Kaufmann, PhD	Executive Director
Marci Cheng	CHRO

Can you help? Thank you so much!

Answer 1

Hi,

You may download my solution workbook from here.

Hope this helps.

Answer 2

Great solution. Thanks, Ashish! What courses or online resources do you recommend to learn these advanced features?

Answer 3

Hi,

If you want a formula-based solution, then enter this formula in cell B2

=IFERROR(TRIM(CHOOSECOLS(TEXTSPLIT(A2:A5,$D$2:$D$4),2)),TEXTAFTER(A2:A5,", "))

Hope this helps.

Answer 4

I'm hoping to keep this transformation in PQ, but I appreciate you demonstrating this in formula notation as well.

Answer 5

Are you accept formula?

=TEXTSPLIT(IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))=1,SUBSTITUTE(A2,", ","|"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Jr., ","Jr.|"),"III, ","III|"),"PhD, ","PHD|")),"|")