The easy way to join text in Excel
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).
— 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.
