String splitting names with salutations just for females

[DeeEmmEss]

I have a table of sports teams of which part of it has Captain, Player 1, Player 2, Player 3 etc. and currently has salutation (female only), first name, surname, I have duplicated this part of the sheet onto another sheet where I wish to have the names reversed i.e. surname first, and then first name. That's the easy bit which I have achieved with the following formula:
=IFERROR(MID(Table2[@[Player 1]],FIND(" ",Table2[@[Player 1]])+1,256)&" "&LEFT(Table2[@[Player 1]],(FIND(" ",Table2[@[Player 1]],1))),""). This works perfectly for the males, but the females have first name, surname, salutation.

What I would like is to include salutions just for the females - Mrs, Miss and Ms, so that the result would look like Smith Mrs Michelle, Jones Ms Jane, but Smith John, and Jones Jim. I'm stumped.

 

[Rick Rothstein]

Give this formula a try...

=MID(A1&" "&A1,FIND(" ",A1,FIND(" ",A1)+COUNTIF(A1,"* * *"))+1,LEN(A1)+1)

 

[Hjemmet]

I have this code on a sheet

Function DelNavn(cel As String, del As String) As String
If IsEmpty(cel) Then Exit Function
If del = 1 Or del = 2 Or del = 3 Then
Select Case del
Case Is = 1
If InStrRev(cel, " ") = 0 Then
DelNavn = cel
Else
DelNavn = Mid(cel, 1, InStr(1, cel, " ", 1) - 1)
End If
Case Is = 2
If InStr(1, cel, " ", 1) = InStrRev(cel, " ") Then
DelNavn = Mid(cel, InStrRev(cel, " ") + 1, Len(cel))
Else
DelNavn = Mid(cel, (InStr(1, cel, " ", 1) + 1), InStrRev(cel, " ") _
- InStr(1, cel, " ", 1) - 1)
End If
Case Is = 3
DelNavn = Mid(cel, InStrRev(cel, " ") + 1, Len(cel))
End Select
Else
DelNavn = CVErr(xlErrValue)
End If
End Function

and in cell where you want the First name enter this "=delnavn(B2;1) B2 where all Name are

 

[DeeEmmEss]

Rick Rothstein thankyou very much works exactly as requested. Just had to put in an iferror to exclude any blanks. Hjemmet thankyou for your prompt reply - haven't tried it as above works perfectly.

 

[Peter_SSs]

.. thankyou very much works exactly as requested.

Perhaps it is not important to your situation but are you aware that the suggested formula results in a space at the end of each name (eg "Smith John ")?
Perhaps you might want to consider removing the highlighted +1 to avoid that extra space?

=MID(A1&" "&A1,FIND(" ",A1,FIND(" ",A1)+COUNTIF(A1,"* * *"))+1,LEN(A1)+1)

 

[DeeEmmEss]

Gentlemen, I have just noticed I have a slight problem with the result of some of the formulas. My fault I neglected to mention some of my surnames are double barrelled i.e Le Page, Le Cheminant, De Garis, Le Lievre etc. (I live in Guernsey, Channel Islands and a lot of our names are of French origin). So the female names now appear as I wish, and the signle barrelled male surnames are correct, but the Le's and the De's are not. Sorry to be a pain in the proverbial.

 

[Peter_SSs]

It is always difficult to cover 100% of the possibilities with this sort of textual manipulation, but see if this is closer.

Excel Workbook
	A	B
1	John Smith	Smith John
2	Miss June Jones	Jones Miss June
3	Mrs Sally Le Page	Le Page Mrs Sally
4	John Le Page	Le Page John
5	Ms Jane Doe	Doe Ms Jane
6	Ms Jane van Doe	van Doe Ms Jane
7	John van den Burg	van den Burg John
8	Ms Mary-Ann van den Burg	van den Burg Ms Mary-Ann
Names

 

[DeeEmmEss]

ThankyounPeter works a dream, much appreciated.

 

[Peter_SSs]

ThankyounPeter works a dream, much appreciated.

You are very welcome.