Excel formula: Join first and last name

Generic formula

=first&" "&last

Summary

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

Explanation

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

The FIND function finds the first space character (" ") in the name and returns the position of that space in the full name. The number 1 is subtracted from this number to account for the space itself. This number is used by the LEFT function as the...

Get last name from name

At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions which make up the complex part of this formula just do one thing: they calculate how many characters need to be extracted. At a high...

Get first name from name with comma

At a high level, this formula uses RIGHT to extract characters from the right side of the name. To figure out the number of characters that need to be extracted to get the first name, the formula uses the FIND function to locate the position of...

Get last name from name with comma

At a high level, this formula uses LEFT to extract characters from the left side of the name. To figure out the number of characters that need to be extracted to get the last name, the formula uses the FIND function to locate the position of ", " in...

Get name from email address

At the core, this formula is extracting characters starting from the left, and using the FIND function to figure out how many characters to extract. First, FIND looks for the "@" character inside the email address " achang@maaker.com ". The "@"...

Join cells with comma

Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (&) and a single space between each value: B5 & " " & C5 & " " & D5 & " "...

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.