Please help with Excel!!?

Question

I have a spreadsheet with loads of names in Name, middle name, surname format, BUT i would like it surname, first name, middle name. I have tried the following formula

=CONCATENATE(MID(A2,SEARCH(" ",A2,1) + 1,LEN(A2)),", ",MID(A2,1,SEARCH(" ",A2,1) - 1))

This worked fine, however some people have 4 middle names, so those returned with a middle name first instead of the surname!

Please help, as i have to deal with this spreadsheet on a monthly basis and just want to make life easier for myself.

Answer 1

Ok - try this

Goto Tools->Macro->Visual Basic Editor

Right click Under Project->VBA project (on left hand side of the screen)

Insert->Module

then within module1 that you've just added cut and paste the following code (from under the line)

now just use the function

= ReverseName(A2) within your cell

--------------------------------------...

Function ReverseName(stInputName As String)

    Dim stFirstWord As String

    Dim stLastWord As String

    stFirstWord = Left(stInputName, InStr(1, stInputName, " ", vbTextCompare) - 1)

    stLastWord = StrReverse(stInputName)

    stLastWord = Left(stLastWord, InStr(1, stLastWord, " ", vbTextCompare))

    stLastWord = StrReverse(Trim(stLastWord))

    

    stMiddleName = Mid(stInputName, Len(stFirstWord) + 1, Len(stInputName) - Len(stLastWord) - Len(stFirstWord) - 1)

    

    ReverseName = stLastWord & ", " & stFirstWord

    If (Len(stMiddleName) > 1) Then

        ReverseName = ReverseName + "," + stMiddleName

    End If

    

End Function