Explanation

One of the key skills you need to be good with Excel formulas is concatenation . Put simply, concatenation is just a fancy name for joining text together. In Excel formulas, the primary operator for concatenation is the ampersand (&). A good example of a simple concatenation task is the creation of an email address using a first and last name. There are many ways to create an email address, but the core problem is to join together a name and a domain, as seen in the worksheet shown. The formula in E5, copied down, is:

=LOWER(LEFT(C5)&B5)&"@"&$E$2

Background study

  • How to concatenate in Excel - article
  • LEFT function - overview
  • LOWER function - overview
  • How to change case with UPPER, LOWER, and PROPER - video

How the formula works

In the example shown, the formula in E5 is:

=LOWER(LEFT(C5)&B5)&"@"&$E$2

Working from the inside out, the LEFT function is used to get the first character of the first name like this:

LEFT(C5) // returns "T"

LEFT extracts text from the left side of a text string. Normally, we would also give LEFT the number of characters to extract as num_chars argument. However, in this case, we only want the first character and it turns out that num_chars defaults to 1, so there is no need to provide a number. With “Tom” in cell C5, the LEFT function returns “T”. We then use the concatenation operator (&) to combine the result from LEFT with cell B5:

=LEFT(C5)&B5
="T"&"BROWN"
="TBROWN"

The result is the text string “TBROWN”, which is returned directly to the LOWER function. Simplifying, at this point we have the following:

=LOWER("TBROWN")&"@"&$E$2

The LOWER function converts any uppercase characters in a text string to lowercase characters. In this case, LEFT converts “TBROWN” to “tbrown”:

=LOWER("TBROWN") // returns "tbrown"

Simplifying again, we now have:

="tbrown"&"@"&$E$2

The remaining formula concatenates the text “tbrown” to the “@” character, and the result is then concatenated to cell E2, as shown below:

="tbrown@"&$E$2
="tbrown@"&"abc.com"
="tbrown@abc.com"

With “abc.com” in cell E2, the final result is “tbrown@abc.com”. Notice that the reference to cell $E$2 is an absolute reference to prevent this cell from changing as the formula is copied down the column.

Alternate email address schemes

In a work environment, there are many different schemes for creating an email address. For example, a name like “Tom Brown” may appear as tbrown@abc.com, tom.brown@abc.com, brown_tom@abc.com, tombrown@abc.com, etc. The attached worksheet contains formulas for these alternatives:

=LOWER(LEFT(C5)&B5)&"@"&$E$2 // tbrown@abc.com
=LOWER(C5&"."&B5)&"@"&$E$2 // tom.brown@abc.com
=LOWER(B5&"_"&C5)&"@"&$E$2 // brown_tom@abc.com
=LOWER(C5&B5)&"@"&$E$2 // tombrown@abc.com

Notice all variations use concatenation in different ways to create a different email address. They also use the LOWER function to force all parts of the name to lowercase characters only.

CONCAT function

If you prefer, you can also use the CONCAT function to solve this problem like this:

=LOWER(CONCAT(LEFT(C5),B5,"@",$E$2))

CONCAT joins all four values with concatenation without the need for the & operator. The result from CONCAT, “TBROWN@abc.com”, is returned directly to the LOWER function:

=LOWER("TBROWN@abc.com") // returns "tbrown@abc.com"

The final result is “tbrown@abc.com” as in the original formula above. Note that in this formula, we run the entire text string through the LOWER function to keep things simple.

Note: In an old version of Excel without CONCAT, you can use the CONCATENATE function with the same result.

Explanation

Some applications show email addresses together with a “display name”, where the name appears first, followed by the email address enclosed in angle brackets (<>). The goal in this example is to create a format like this based on an existing name and email address.

In the worksheet shown, column B contains a name, and column C contains an email address. The formula in column D uses the ampersand character (&) to join the name and email address together:

=B5&" <"&C5&">"

This is an example of concatenation . On the right side of the formula, the email address in C5 is wrapped in angle brackets:

" <"&C5&">"

Notice the angle brackets are text and must be enclosed on double quotes (""). Also notice that opening bracket (<) begins with a space character. This allows us to join the name directly to the left side:

=B5&" <"&C5&">"

The result is the name followed by a space character, followed by the email address in angle brackets.

Concatenation functions

In the example shown, the ampersand operator (&) is used to concatenate the name, email, and angle brackets manually. The ampersand is a flexible way to concatenate text strings, because it can be used in a formula anywhere. However, Excel also has three functions dedicated to concatenation: CONCATENATE , CONCAT , and TEXTJOIN . Both CONCATENATE and CONCAT can be used to solve the same problem like this:

=CONCATENATE(B5," <",C5,">")
=CONCAT(B5," <",C5,">")

Note that the CONCATENATE function is now technically replaced by the CONCAT function , which was first released in Excel 2019. The TEXTJOIN function is primarily designed to concatenate ranges.