Remove the last name from a cell

There is also the option to create a function to get the last name of the cell, just the way you want it. In the following code I use "For" to scroll through the contents of the cell until I find a blank space, and meanwhile the variable "p" is incremented to know the size of the last name. Then just use the "Right" function to get the last name and the "Trim" function to remove the white spaces.

To insert the module: Alt-F11 Insert module

Function code:

Function UltimoNome(Nome As Variant) As String
Dim p As Integer
For i = 0 To Len(Nome) - 1
p = p + 1
If Mid(CStr(Nome), Len(CStr(Nome)) - i, 1) = " " Then
Exit For
End If
Next
UltimoNome = Trim(Right(Nome, p))
End Function

Using the function in the worksheet would be simple.

=UltimoNome(A2)

I hope it helps.

A form using only native Excel functions (the original source is this answer in SOen ) looks like this:

=DIREITA(A2;NÚM.CARACT(A2)-PROCURAR("|";SUBSTITUIR(A2;" ";"|";NÚM.CARACT(A2)-NÚM.CARACT(SUBSTITUIR(A2;" ";"")))))

Detailing how it works, from left to right:

• NÚM.CARACT(A2)-NÚM.CARACT(SUBSTITUIR(A2;" ";"")) - counts the total number of spaces in the original string
• SUBSTITUIR(A2;" ";"|"; ... ) - Changes only the last space for the character | , to differentiate it from the others (note that your original string can not contain a character of this, otherwise the formula does not work correctly).
• PROCURAR("|"; ... ) - Finds the absolute position of that character | added (and that was the last space in the original string)
• DIREITA(A2;NÚM.CARACT(A2) - ... )) - Returns all characters after that character | (that is, the last integer in the original string)

In addition, because you are using Excel in Portuguese (where the comma is used as the decimal separator), you need to use the semicolon as the parameter separator in the formula. p>

The original answer in SOen also has suggestions on how to circumvent the case where there is no white space in the original string (other than the use of commas, this may be your "new" error mentioned in comment).

  

P.S .: Incidentally, this response is basically the same solution suggested by   @Earendul in a comment, just translated into Excel in Portuguese.   Except for a big coincidence, the original credit is still the author of the reply there in SOen.

Inspired by Denis Caixeta's response, I wrote a function:

Function UltimoNome(Nome As Variant) As String
    For Posicao = (Len(Nome) - 1) To 0 Step -1
        If (Mid(Nome, Posicao, 1) = " ") Then
            UltimoNome = Mid(Nome, Posicao + 1)
            Exit For
        End If
    Next
End Function

It scans the name from the end to the beginning until you find a blank space. Found the space, it returns whatever is after it (the last name).

This function can be used in the same way as Denis:

=UltimoNome(A2)

Example:

=UltimoNome("Antônio Vinícius Menezes Medeiros")

Return:

Medeiros