Convert a title name to different format

New Contributor

Hello, I am trying to change a name string to a new format:

If you saw my last post, this is similar where there are three types (has middle name, has middle name letter, or does not have middle name):
Repair & Maintenance Leader - Doe, John Apple
Repair & Maintenance Leader - Doe, John A
Repair & Maintenance Leader - Doe, John
CEO - Doe, John Apple
CEO - Doe, John A
CEO - Doe, John
The job title may be different for each person, but either way, there is always a delimiter "-" in all titles.

I want the end result to be this in all examples:
John Doe

Thank you!

4 Replies

@waterguy05 

If you're on 365, this may be an option for you:

 

=LET(str,TEXTAFTER(A1," - "),last,TEXTBEFORE(str,","),first,TAKE(DROP(TEXTSPLIT(str," "),,1),,1),first&" "&last)

could a job title have a "-" in it? that would mess it up
=TEXTBEFORE(TEXTAFTER(TEXTAFTER(A1, " - "),", "), " ") & " " & TEXTBEFORE(TEXTAFTER(A1, " - "), ", ")

@waterguy05 

Flash Fill was almost an option. It failed on the last one:

Patrick2788_0-1672953101646.png

 

@waterguy05 

=IFERROR(CONCATENATE(MID(A1,SEARCH(", ",A1)+2,SEARCH(" ",A1,SEARCH(", ",A1)+2)-SEARCH(", ",A1)-2)," ",MID(A1,SEARCH(" - ",A1)+3,SEARCH(", ",A1,SEARCH(" - ",A1)+3)-SEARCH(" - ",A1)-3)),CONCATENATE(RIGHT(A1,LEN(A1)-SEARCH(", ",A1)-1)," ",MID(A1,SEARCH(" - ",A1)+3,SEARCH(", ",A1,SEARCH(" - ",A1)+3)-SEARCH(" - ",A1)-3)))

 

An alternative could be this formula.

extract name.JPG