How to concatenate text in Excel 2016 using the TEXTJOIN and CONCAT functions

Learn about two Excel techniques to concatenate strings using the brand new TEXTJOIN and CONCAT functions.

For a long time CONCATENATE has been the first function we thought of when we needed to combine text in Excel. A couple of new players has entered the game recently and has shaken the things up a bit. Yes, I'm talking about TEXTJOIN and CONCAT designed to help you combine a range of strings. Use these simple functions whenever you need to merge parts of names, addresses, or phrases, combine numbers and words. Currently they are available in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.

The TEXTJOIN function - description and syntax

The TEXTJOIN function in Excel concatenates text from multiple ranges or strings. You can specify a delimiter to include between each text value and ignore empty cells. Even if the delimiter is an empty string, TEXTJOIN will successfully join text in Excel.

Here's how the function looks like:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

It has 3 required and 1 optional arguments:

  • The delimiter lets you specify any character for separating your text. This may be a comma, space, ampersand, or anything you like. The characters must be enclosed by double quotes, or a reference to a valid text string. Any numbers will be regarded as text.
  • Ignore_empty is a required argument. If it's TRUE, empty cells will be ignored. You can include blank cells by setting this value to FALSE.
  • text1 lets you specify a range of cell values to concatenate rather than having to enter each one.
  • [text2, …] is an optional argument that allows specifying further ranges to include in if your range is not continuous.

There can be a maximum of 252 arguments for the text items. If the resulting string exceeds the cell limit of 32767 characters, the TEXTJOIN function returns the #VALUE! error.

The CONCAT function in Excel - description and syntax

Introduced as part of the update in February 2016, CONCAT replaces the CONCATENATE function in Excel. It works exactly the same way. Looks like it has been added to reduce the length of the function name. Also, CONCAT is the standard function used by Google Sheets. CONCATENATE continues to be supported to provide compatibility with the previous versions of Excel.

The structure of the function is as follows:

CONCAT(text1, [text2],…)

CONCAT has just two arguments one of which is optional:

  • text1 is the text entry to be joined. It can be a string, or a range of strings.
  • [text2, …] stands for additional text items to be combined.

There can be a maximum of 253 arguments for the text values.

Excel - concatenate strings using the TEXTJOIN and CONCAT functions

Joining text is one of the most common tasks in Excel. In the previous versions if you needed to concatenate text from several cells, it was necessary to specify each one individually. With the new TEXTJOIN and CONCAT functions, you can simply refer to a range and combine text strings from cell ranges with or without a delimiter.

The main limitation of the CONCATENATE() function that CONCAT() has addressed is that we can now select a range of cells to join while previously were limited to listing cells separately. Say, you want to combine parts of telephone numbers. With the Concatenate function, you will need to enter the cell addresses one by one.

=CONCATENATE(A1,B1,C1,D1,E1,)
Combine strings using the CONCATENATE function]

In this case, CONCAT looks much more compact. All you need to do is just pick the range with the Excel strings to concatenate.

=CONCAT(A1:E1)
Join numbers with the help of CONCAT]

At the same time, both CONCAT and CONCATENATE look the same when you need to join text using delimiters.

=CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2)
How to use the Concatenate function in Excel]

=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)
Use the CONCAT function to combine text in Excel]

As it can be easily seen, the functions don't process empty cells which results in extra spaces.

In this case, TEXTJOIN is destined to be a really popular choice for users working with large amounts of text data. You simply specify the space delimiter, choose to ignore empty cells and define the range. This is by far a smarter solution.

=TEXTJOIN(" ",TRUE,A2:E2)
Use the TEXTJOIN function to concatenate text in Excel]

If you have Excel 2016, do explore the TEXTJOIN and CONCAT functions to see how helpful they are and say bye-bye to concatenated and nested if statements when combining strings.

You may also be interested in:

3 Responses to "How to concatenate text in Excel 2016 using the TEXTJOIN and CONCAT functions"

  1. Ritabrata Bhattacharya says:

    Hi Maria,

    Recently I had made an upgrade to my office suite from Office 2013 Professional Plus to Office 2016 Professional Plus.

    As advertised I am unable to see the following functions

    TEXTJOIN
    CONCAT
    SWITCH
    MAXIFS
    MINIFS
    IFS

    Please let me know whether anything needs to be configured to get this function available into Excel 2016.

    Regards
    Riv

  2. KARTHIK says:

    HOW TO APPLY TEXTJOIN FORMULA IN EXCEL 2007,

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard