Combine First and Last name
This tutorial shows how to combine the first and surname that are captured in different cells, using Excel formulas and VBA
Example: Combine First and Last name
|
=B5&" "&C5
|
This formula uses the & sign to combine the first and last name captured in cells B5 and C5, respectively. The &" "& represents a single space that is added between the first and last name.
|
METHOD 2. Combine First and Last name using CONCATENATE function
EXCEL
|
=CONCATENATE(B5," ",C5)
|
This formula uses the Excel CONCATENATE function combine the first and last name that are captured in cells B5 and C5, respectively. The ," ", is used to add a space between the first and last name.
|
METHOD 1. Combine First and Last name
VBA
Sub Combine_first_last_name()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
ws.Range("F4") = ws.Range("B5") & " " & ws.Range("C5")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
First name: In this example the first name is captured in cell ("B5"). To change the first name either change the cell reference in the VBA code to the cell that captures the first name or change the name in cell ("B5").
Last name: In this example the last name is captured in cell ("C5"). To change the last name either change the cell reference in the VBA code to the cell that captures the last name or change the name in cell ("C5").
Worksheet Selection: Select the worksheet which captures the first and 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 ("F4") in the VBA code.
First name: In this example the first name is captured in cell ("B5"). To change the first name either change the cell reference in the VBA code to the cell that captures the first name or change the name in cell ("B5").
Last name: In this example the last name is captured in cell ("C5"). To change the last name either change the cell reference in the VBA code to the cell that captures the last name or change the name in cell ("C5").
Worksheet Selection: Select the worksheet which captures the first and 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 about how to combine first and last name
EXPLANATION
EXPLANATION
his tutorial shows how to combine the first and surname that are captured in different cells, using Excel formulas and VBA.
This tutorial provides two Excel methods. The first method doesn't use any Excel functions, it uses the & sign and the second method uses the CONCATENATE function to concatenate the first and last name. It also uses " " to insert a space between the first and last name.
The VBA code in this tutorial uses the & sign to combine the values in cells B5 and C5 that capture the first and last name, respectively.
FORMULA (using & sing)
=first_name&" "&last_name
=first_name&" "&last_name
FORMULA (using CONCATENATE function)
=CONCATENATE(first_name&" "&last_name)
=CONCATENATE(first_name&" "&last_name)
ARGUMENTS
first_name: The value or cell reference that represents the first name.
last_name: The value or cell reference that represents the last name.
first_name: The value or cell reference that represents the first name.
last_name: The value or cell reference that represents the last name.