Return First name from name
This tutorial shows how to return the First name from a name using an Excel formula or VBA
Example: Return First name from name
|
This formula uses a combination of the Excel LEFT and FIND functions to extract the First name from a full name. The FIND function is used to return the position of the first occurrence of a space, which is identified by " ", in the string. The position (as a number) that is returned by the FIND function, minus one, is then applied in the LEFT function as a within_text parameter.
The LEFT function then returns the number of characters that is determined by the position of the first space minus one from the left of the string. |
Sub Return_first_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")
On Error Resume Next
'return the First name from a name
ws.Range("D5") = Split(val, " ")(0)
ws.Range("D5") = Split(val, " ")(0)
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Name: Select the name from which you want to only extract the First 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 First 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 ("D5") in the VBA code.
Name: Select the name from which you want to only extract the First 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 First 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_first_name_from_name()
'declare variables
Dim ws As Worksheet
Dim firstwrd As String
Dim ws As Worksheet
Dim firstwrd As String
Set ws = Worksheets("Analysis")
On Error Resume Next
'return the First name from a range of names
For x = 5 To 10
ws.Range("D" & x) = Split(ws.Range("B" & x), " ")(0)
Next x
For x = 5 To 10
ws.Range("D" & x) = Split(ws.Range("B" & x), " ")(0)
Next x
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output column range by changing the column reference ("D") 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 First 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 First 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 ("D") 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 First 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 First 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 LEFT and FIND functions to return the first word in a string. The FIND function, combined with the (" ") criteria returns the position of the first occurrence of a space (as a number), from which a number one is removed to return the position of the last character of the first word in a cell. This position is then applied inside the LEFT function to return the first word in a string.
Both of the VBA methods make use of the Split function, with the (" ") criteria to return the First name from a name. The first method shows how the VBA code can be applied if you are only looking to return the First name from a single name that is captured in one cell. The second VBA method can be applied if you want to return the First name from multiple names that are captured in a range of cells, where the VBA code will loop through each cell in the specified range and return the first word from each cell.
FORMULA
=LEFT(name,FIND(" ",name)-1)
=LEFT(name,FIND(" ",name)-1)
ARGUMENTS
name: The First and Last name from which you want to retrieve the First name.
name: The First and Last name from which you want to retrieve the First name.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return Last name from name | How to return the Last name (Surname) 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 |
|---|---|---|
| LEFT Function | The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side | |
| FIND Function | The Excel FIND function returns the position of a specific sub-string within a string |