The easy way to join text in Excel

By Kelly L. Williams, CPA, Ph.D.

Q. I have a list of email addresses that I need to join in one cell, with each address separated by a semicolon. I use the formula =A1&";"&A2&";"&A3 or CONCATENATE to create the email group. However, this takes a long time, and my current list of emails spans many rows. Is there a quicker way?

A. If you have Excel 2019 or Excel 365, you can use the function TEXTJOIN to quickly join text in one cell and separate the text with any delimiter, even if you have many rows or columns of data to join. Please note that TEXTJOIN is not available in earlier versions of Excel, including Excel 2016.

To do this, follow the instructions below. You can view a video demonstrating this technique at the bottom of this page and follow along with this Excel workbook

The first step is to place your cursor where you would like the joined text to appear. Click the Insert Function button located immediately to the left of the formula window. That will open the Insert Function dialog box.

While the Search for a function: area is highlighted, type TEXTJOIN and press the Enter key. Select TEXTJOIN from the Select a function: area.

The resulting Function Arguments dialog box, shown in the screenshot below, will appear. Fill it in as follows:

  • For Delimiter, enter the character to be inserted between each text item. For this example, enter a semicolon (you do not have to include the quotes around the delimiter because Excel will add those).
  • For Ignore_empty, enter True to ignore empty cells in the text, or enter False to include the empty cells in the text. For this example, enter True because including two semicolons with nothing between them in this string of emails would cause the email editor to not properly create our email group.
  • For Text1, enter the text string or range of cells to be joined. For this example, choose the range A1:A29. Excel will provide an option for Text2, Text3, and so on if you have additional text strings and/or ranges of cells to include. Click OK. The ­Formula result (circled in red in the screenshot) will show all text in one cell, separated by the delimiter chosen (a semicolon in this example).
techqa-4


— By Kelly L. Williams, CPA, Ph.D.
   


About the authors

Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University. Byron Patrick, CPA/CITP, CGMA, is senior applications consultant at botkeeper.

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.

SPONSORED REPORT

Get your clients ready for tax season

These year-end tax planning strategies address recent tax law changes enacted to help taxpayers deal with the pandemic, such as tax credits for sick leave and family leave and new rules for retirement plan distributions, as well as techniques for putting your clients in the best possible tax position.

RESOURCES

Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.