Join text in Excel

Q. I have a long list of usernames that I need to make into email addresses by adding “@mtsu.edu” to the end of each username. Is there a simple way to do this, or will the ending have to be typed in manually?

A. You definitely do not need to do this manually. In fact, I usually tell my students that if you could show a reasonably intelligent fifth-grader how to do something, Excel can probably do it for you. You just have to know how to give it instructions.

There are several ways you can join text in Excel. We will go through four methods to join the user-names to the @ symbol and domain. The screenshot below shows a snippet of the usernames we need to join with “@mtsu.edu” to create the email addresses. I have made an Excel spreadsheet for this demonstration.

Let’s explore four methods to join these usernames with “@mtsu.edu”. We will look at using an & (ampersand) symbol and three built-in Excel functions, CONCATENATE, CONCAT, and TEXTJOIN, to join the text.

& symbol

You can join text in Excel using a simple “&.” For this question, this method is sufficient. In this example, the first username, which is in cell A2, is joined with “@mtsu.edu“. Use an equal sign (=) to instruct Excel that you are creating a formula, and include the & between the cell reference and the text. Note that any text should be surrounded by quotes. The formula entered into cell C2, as shown below, is Submit a = A2&”@mtsu.edu“. Drag this formula down to create all of the email addresses.

CONCATENATE

CONCATENATE, an Excel function used to join various text strings into one, is available in Excel 2007 and later versions.

The syntax for the CONCATENATE function is CONCATENATE(text1, [text2], …), where text1, which is required, is the first item to join. In our example, this is cell A2, the cell that contains the first username. The second argument, text2, which is optional, is the second item to join. In our example, this is the text “@mtsu.edu“. Our example only has these two things to join, but you can have as many as 255 things joined. Note that text1, text2, etc. can contain cell references, numbers, or text.

The formula entered into cell E2, as shown below, is =CONCATENATE(A2,”@mtsu.edu“). This formula joins the username with “@mtsu.edu” in cell E2. Drag this formula down for all usernames to create the email addresses.

CONCAT

CONCAT works identically to CONCATENATE and was created by Microsoft to replace CONCATENATE. CONCATENATE will still be available in earlier versions of Excel but may not be supported in future versions. CONCAT works in Excel 2016 and later versions. The syntax for CONCAT is CONCAT(text1, [text2], …). The first argument, text1, is A2, and the second argument, text2, is the text “@mtsu.edu“.

The formula entered into cell G2, as shown below, is =CONCAT(A2,”@mtsu.edu“). Drag this formula down to create all of the email addresses.

TEXTJOIN

TEXTJOIN, the newest Excel function of the three discussed here, is available in Excel 2019 and later versions.

The syntax for the TEXTJOIN function is TEXTJOIN(delimiter, ignore_empty, text1, [text2], …), where delimiter is the character to be inserted between each text item. You can also leave this argument blank if you do not want a delimiter, as will be the case with our example. The second argument ignore_empty allows you to instruct Excel whether to ignore any empty cells included in your cell range (if you are using one) or to include those empty cells. We will not be using any cell ranges, only a single cell, so I will leave this blank, which will default to TRUE-ignore empty cells. The third argument, text1, is the first item to join. This is the first username, A2 in our example. These three arguments are required. You can optionally continue to add other text to join, up to 252 items. Note that text1, text2, etc. can contain cell references, numbers, text, and ranges of cells. In our example, we will insert the text “@mtsu.edu” for the argument text2.

The formula entered into cell I2, as shown below, is =TEXTJOIN(,,A2,”@mtsu.edu”). The two commas in the formula preserve our blanks that we chose for the first two required arguments. Drag this formula down for all usernames to create the email addresses.

TEXTJOIN is certainly capable of completing the task here, but it can also easily join text in situations that would be far too time-consuming to use &, CONCATENATE, or CONCAT. If you are interested in learning more about TEXTJOIN, you can view this article.

The last step you should take, no matter which method you use, is to paste your formulas as values. To do this, select all of the cells that contain the formulas and copy (Ctrl+C on a PC or Cmd+C on a Mac). If you are using a PC, click the dropdown arrow under Paste in the Clipboard group under the Home tab. Click Paste Special, choose Values, and click OK. If you are using a Mac, you can find Paste Special on the Edit tab.

All four methods discussed take approximately the same amount of time to complete a task like the one in our example, so if you already use one of these methods, you might feel more comfortable sticking with that one. If you don’t have a preference, I suggest using TEXTJOIN since it can be used for a task such as this, but so many other types of joining tasks as well.

Below is a video I made joining text using &, CONCATENATE, CONCAT, and TEXTJOIN for the example above.

About the authors

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University. 

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.