Forum Discussion

waterguy05's avatar
waterguy05
Copper Contributor
Jan 05, 2023

Convert a title name to different format

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!

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 05, 2023

    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)
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 05, 2023

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

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 05, 2023

    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.

Share

Resources