Removing the last name of a cell

Question

I have a table with the full name of users and needed nourish column put the last name, someone help me with the formula?

A2 = Ricardo Simoes

B2 = Simoes

I’ve been trying like this But make a mistake

=EXT.TEXTO(A2;LOCALIZAR(" ";A2;1)+1;NÚM.CARACT(A2)-LOCALIZAR(" ";A2;1)) 

Answer 1

A way using only native Excel functions (the original source is this answer in Soen) that’s how it is:

=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 its original string cannot contain such a character, 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 whole word in the original string)

Also, since you are using Excel in English (where the comma is used as the decimal separator), you need to use the semicolon as the parameter separator in the formula.

The original reply in Soen also has suggestions on how to get around the case where there is no white space in the original string (apart from the use of commas, it may be that this is your "new" error mentioned in comment).

P.S.: In fact, this answer is basically the same solution suggested by @Earendul in a comment, only translated to Excel in English. Except for a big coincidence, the original credit is still from the author of the answer there on Soen.

Answer 2

There is also the option to create a function to take the last name of the cell, as you want. In the following code I use the "For" to go through the cell’s contents until I find a blank "" and, meanwhile, the variable "p" is being incremented, to know what the size of the last name is. Then just use the "Right" function to take the last name and the "Trim" function to remove the white me spaces.

To insert the module: Am - 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

The use of the function in the worksheet would be simple.

=UltimoNome(A2)

I hope it helps.

Answer 3

In English it is best to use the formula SUBST

=DIREITA(A3;NÚM.CARACT(A3)-PROCURAR("|";SUBST(A3;" ";"|";NÚM.CARACT(A3)-NÚM.CARACT(SUBST(A3;" ";"")))))

Answer 4

Based on the code passed by António Vinícius Menezes Below follows a code adapted for database source data:

dim A as Integer 
dim i as Integer
dim Nome as Variant
dim PrimeiroNome as Variant
dim UltimoNome  as Variant


'Aqui alimentamos a variável Nome com o nome vindo do banco de dados
Nome = rs.Fields("nome_estudante")

'Extrair o último nome do nome completo
For A = (Len(Nome) - 1) To 0 Step -1
    If (Mid(Nome, A, 1) = " ") Then
        UltimoNome = Mid(Nome, A + 1)
        Exit For
    End If
Next

'Extrair os Primeiros Nomes do nome completo
For i = 1 To Len(Nome)
    If (Mid(Nome, i, 1) = " ") Then
        PrimeiroNome = Left(Nome, A - 1)
        Exit For
    End If
Next

If you like mark as an important answer! Thanks

Answer 5

Inspired by the reply of Denis Caixeta, 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 it finds 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")

Returns:

Medeiros