Return Last name from name
This tutorial shows how to return the Last name (Surname) from a name using an Excel formula or VBA
Example: Return Last name from name
|
=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",1000)),1000))
|
This formula uses a combination of the Excel TRIM, RIGHT, SUBSTITUTE and REPT functions to return the last name from a name.
|
Sub Return_last_name_from_name()
'declare variables
Dim ws As Worksheet
Dim val As String
Dim ws As Worksheet
Dim val As String
Set ws = Worksheets("Analysis")
val = ws.Range("B5")
val = ws.Range("B5")
'return the last name from a name
ws.Range("D5") = Right(val, Len(val) - (InStrRev(val, " ")))
ws.Range("D5") = Right(val, Len(val) - (InStrRev(val, " ")))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Name: Select the name from which you want extract the last name by changing the cell reference ("B5") in the VBA code, or changing the name in cell ("B5").
Worksheet Selection: Select the worksheet which captures the name from which you want to extract the last name by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Name: Select the name from which you want extract the last name by changing the cell reference ("B5") in the VBA code, or changing the name in cell ("B5").
Worksheet Selection: Select the worksheet which captures the name from which you want to extract the last name by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Sub Return_last_name_from_name()
'declare variables
Dim ws As Worksheet
Dim lastwrd As String
Dim ws As Worksheet
Dim lastwrd As String
Set ws = Worksheets("Analysis")
On Error Resume Next
'return the last name from a range of names
For x = 5 To 10
ws.Range("C" & x) = Right(ws.Range("B" & x), Len(ws.Range("B" & x)) - (InStrRev(ws.Range("B" & x), " ")))
Next x
For x = 5 To 10
ws.Range("C" & x) = Right(ws.Range("B" & x), Len(ws.Range("B" & x)) - (InStrRev(ws.Range("B" & x), " ")))
Next x
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output column range by changing the column reference ("C") and the row range by changing the row reference which are the values that are assigned x in the VBA code.
Range: Select the range from which you want to return the Last name from each cell, containing a name, by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 10. Therefore, the range that this code will loop through will be ("B5:B10"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures the range of names from which you want to extract the Last name by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output column range by changing the column reference ("C") and the row range by changing the row reference which are the values that are assigned x in the VBA code.
Range: Select the range from which you want to return the Last name from each cell, containing a name, by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 10. Therefore, the range that this code will loop through will be ("B5:B10"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures the range of names from which you want to extract the Last name by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial explains how to return only the First name from a name using an Excel formula and VBA.
The Excel method uses a combination of the Excel TRIM, RIGHT, SUBSTITUTE and REPT functions to return the last name from a name.
Both of the VBA methods make use of the Right, Len and InStrRev functions, with the (" ") criteria to return the Last name from a name. The first method shows how the VBA code can be applied if you are only looking to return the Last name from a single cell that captures one name. The second VBA method can be applied if you want to return the Last name from multiple cells that capture one name in each cell, where the VBA code will loop through each cell in the specified range and return the last word from each cell.
FORMULA
=TRIM(RIGHT(SUBSTITUTE(name," ",REPT(" ",1000)),1000))
=TRIM(RIGHT(SUBSTITUTE(name," ",REPT(" ",1000)),1000))
ARGUMENTS
name: The name from which you want to retrieve the Last name (Surname).
name: The name from which you want to retrieve the Last name (Surname).
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return First name from name | How to return the First name from a name using Excel or VBA | |
| Return first word from a string | How to return the first word from a string using Excel or VBA | |
| Return last word from a string | How to return the last word from a string using Excel or VBA | |
| Combine First and Last name | How to combine the first and surname that are captured in different cells using Excel or VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string | |
| RIGHT Function | The Excel RIGHT function returns the specified number of characters from a specified string, starting from the right side | |
| TRIM Function | The Excel TRIM function removes the spaces at the start and end of the text and also removes the unnecessary spaces between words, leaving only a single space between words and numbers that form part of the text |