=trim(mid(substitute(a1," ",rept(" ",99)),100,99))&" "&left(a1,search(", ",a1,1)-1)
I'm trying to figure out a way to remove the middle initial and period mark. Right now the names are formatted like "Jones, Jack A." but I'd like them formatted like "Jack Jones".
Jones, Jack A. Jack Jones Williams, Bryan K. Bryan Williams
I have found a few formulas to swap the first name and last name but none that effectively remove the middle initial and the period.
Any Help would be appreciated!
=trim(mid(substitute(a1," ",rept(" ",99)),100,99))&" "&left(a1,search(", ",a1,1)-1)
Rizky's formula won't work on double barrelled surnames. e,g. Van der Walt, Koos J.
Need something like
=LEFT(TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))),FIND(" ",TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))))) & " " & TRIM(LEFT(A1,FIND(",",A1)-1))
1: Split the surname at the comma with =TRIM(LEFT(A1,FIND(",",A1)-1))
2: Split the names of with TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))
3: Split the names off on the spaces as in step 1 but replace A1 with step 2
4: Rejoin in the desired order: A & " " & B
Bookmarks