String splitting names with salutations just for females

DeeEmmEss

New Member
Joined
Nov 25, 2015
Messages
43
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Give this formula a try...

=MID(A1&" "&A1,FIND(" ",A1,FIND(" ",A1)+COUNTIF(A1,"* * *"))+1,LEN(A1)+1)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
.. 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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
It is always difficult to cover 100% of the possibilities with this sort of textual manipulation, but see if this is closer.

Excel Workbook
AB
1John SmithSmith John
2Miss June JonesJones Miss June
3Mrs Sally Le PageLe Page Mrs Sally
4John Le PageLe Page John
5Ms Jane DoeDoe Ms Jane
6Ms Jane van Doevan Doe Ms Jane
7John van den Burgvan den Burg John
8Ms Mary-Ann van den Burgvan den Burg Ms Mary-Ann
Names
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top