Excel formula: Join first and last name

Generic formula

=first&" "&last

Explanation

If you need to join separate first and last names together into a single full name, you can easily do so with concatenation using the ampersand (&) character. In the generic version of the formula above, first is first name, and last is last name.

In the example, the active cell contains this formula:

=B4&" "&C4

How this formula works

The ampersand character (&) is a special operator in Excel that is used for concatenation. Concatenation is just a fancy word for "join".

On the formula, the text in B4 "Susan" is being joined to a space character " " and then to the text in cell C4, "Chang".

Whenever you use concatenation with literal text entered directly inside the formula, make sure to enclose the text in double quotes (""). The ampersands are never enclosed in quotes unless you want an ampersand to appear in the result of a formula as literal text.

With CONCATENATE

You can also use the CONCATENATE function to join text. For this example, the formula would be:

=CONCATENATE(B4," ",C4)

The CONCATENATE function keeps things tidy, but the ampersand creates shorter, more flexible formulas.

Author

Dave Bruns

Related formulas

Get first name from name

If you need extract the first name from a full name, you can easily do so with the FIND and LEFT functions. In the generic form of the formula (above), name is a full name, with a space separating the first name from other parts of the name. In the...

Get last name from name

If you need extract the last name from a full name, you can do so with this rather complex formula that uses several functions. In the generic form of the formula (above), name is a full name, with a space separating the first name from other parts...

Get first name from name with comma

To extract the first name from a full name in "Last, First" format, you can use a formula that uses RIGHT, LEN and FIND functions. In the generic form of the formula (above), name is a full name in this format: LAST, FIRST Jones, Sarah Smith, Jim...

Get last name from name with comma

If you need extract the last name from a full name in LAST, FIRST format, you can do so with a formula that uses the LEFT and FIND functions. The formula works with names in this format, where a comma and space separate the last name from the first...

Get name from email address

If want to extract the name part of an email address, you can do so with a formula that uses the LEFT and FIND functions. In the generic form above, email represents the email address you are working with. In the example, we are using this formula...

Join cells with comma

To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. In the example shown, the formula in G5 is: =...

Related functions

Excel CONCATENATE Function

The Excel CONCATENATE function concatenates (joins) join up to 30 text items together and returns the result as text. The CONCAT function replaces CONCATENATE in newer versions of Excel.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.