Turn first and last names around and remove middle initial

[oam]

Is there a single formula that will turn the first and last names around and remove the middle initial?

Example:
Smith, Joe P. to Joe Smith
Jones, Sue A. to Sue Jones

[afifkhazin]

let say you put "Smith, Joe P." in cells A1 then you can input in cell B1 :

=MyExtract(MyExtract(A1,2,"f",","),1,"f"," ") & " " & MyExtract(A1,1,"f",",")

but before you input this formula, you must insert this function into VBA module


Function MyExtract(MyText As String, ItemNo As Integer, FrontOrBack As String, Optional MySeparator As String) As String

Dim LenText As Integer, n As Integer, CountSpaces As Integer
Dim MySt As Integer, MyFin As Integer, MyStep As Integer, Mk1 As Integer, Mk2 As Integer

If Len(MySeparator) = 0 Then MySeparator = " "
LenText = Len(MyText)
If LenText < 3 Then
MyExtract = "*"
GoTo MyEndBit
End If

If UCase(FrontOrBack) = "F" Then
MySt = 2
MyFin = LenText - 1
MyStep = 1
Else
MyFin = 2
MySt = LenText - 1
MyStep = -1
End If

For n = MySt To MyFin Step MyStep
If Mid(MyText, n, 1) = MySeparator Then
CountSpaces = CountSpaces + 1
If CountSpaces = ItemNo - 1 Then Mk1 = n
If CountSpaces = ItemNo Then Mk2 = n
End If
Next n
If CountSpaces = 0 Then
MyExtract = "*"
GoTo MyEndBit
End If
If UCase(FrontOrBack) = "B" Then
n = Mk1
Mk1 = Mk2
Mk2 = n
End If
If Mk2 = 0 Then Mk2 = LenText + 1
Mk1 = Mk1 + 1
MyExtract = Mid(MyText, Mk1, Mk2 - Mk1)
MyEndBit:
End Function

[snb]

=TRIM(MID(TEXT(LEFT(A1,LEN(A1)-3),"@ @"),SEARCH(",",A1)+1,LEN(A1)-3))


PS. Please use code tags !

[GTO]

Probably longer than needed, but I was thinking that some may not have a middle initial listed.


=IF(AND(RIGHT(A1,1)=".",NOT(ISERR(FIND(MID(A1,LEN(A1)-1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))),NOT(ISERR(FIND(",",A1)))),TRIM(MID(TRIM(LEFT(A1,LEN(A1)-2)),FIND(",",TRIM(LEFT(A1,LEN(A1)-2)))+1,LEN(TRIM(LEFT(A1,LEN(A1)-2)))))&" "&TRIM(LEFT(TRIM(LEFT(A1,LEN(A1)-2)),FIND(",",TRIM(LEFT(A1,LEN(A1)-2)))-1)),IF(NOT(ISERR(FIND(",",A1))),TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&" "&TRIM(LEFT(A1,FIND(",",A1)-1)),"No Comma"))

Hope that helps,

Mark

[Paul_Hossler]

Is there a single formula that will turn the first and last names around and remove the middle initial?


what would happen with names like ...

Smith, John Jr.

Smith, John III

de LaSmith, John Sr.

A single formula maybe, but no simple formula.


I'd Google a user defined function before trying to make a single formula

Paul

[oam]

Thank you for your responses and "snb's" solution worked until I had name Jones, Bill (NMN) which gave me Bill (N Jones. Is there a way to remove the (NMN; No middle Initial) part?

[GTO]

Did you try the formula I posted?

[oam]

Yes, I got similar results; it turned the names around but left the (NMN) in the middle, example: Jane (NMN) Jones.

[GTO]

Please post a workbook with example data. Fake names are okay, but the variances must be reflective.

[00ians]

Is there a single formula that will turn the first and last names around and remove the middle initial?

Example:
Smith, Joe P. to Joe Smith
Jones, Sue A. to Sue Jones

Excel lacks a "Right-Find" function, but a module function will do it:

Option Explicit

Public Function rFind(FindText As String, WithinText As String) As Long
For rFind = Len(WithinText) To 1 Step -1
If Mid(WithinText, rFind, Len(FindText)) = FindText Then Exit For
Next
End Function


Then, your function within the sheet will be:

=MID(A1,rFIND(" ",A1)+1,LEN(A1)) & ", " & LEFT(A1,FIND(" ",A1)-1)

[snb]

seems obvious:

=TRIM(substitute(MID(TEXT(LEFT(A1,LEN(A1)-3),"@ @"),SEARCH(",",A1)+1,LEN(A1)-3),"(N ",""))

[oam]

snb,

This formula worked great and removed all the item except the first and last name. In addition, turned the first and last name around.

Thank you for your help.