The TEXTJOIN function in Excel is a powerful way to combine text strings from different cells or ranges, giving you a lot of options for how to format the output. In order to merge names, addresses, or any other textual data, TEXTJOIN makes the process very easy. In this guide we showed how to Use TEXTJOIN in Excel.
With this function, users can choose a delimiter, like a comma or space, to separate the combined values. This makes the text easier to read and organize. You can also choose how to handle empty cells with TEXTJOIN either by including them in the final output or leaving them out.
Because it can do so many things, it is very useful for making mailing lists, joining data for reports, and making data manipulation easier. Excel users can speed up the process of processing data and improve the way their data is presented with more accuracy and clarity by using TEXTJOIN. For more information go their official website.
How to Use TEXTJOIN in Excel
- Type the opening sentence
- Pick out your delimiter text
- Put in your value for ignore_empty
- Type in the cells you chose
- Setting the cell range
Understanding the Syntax of TEXTJOIN
How do I use TEXTJOIN?
- TEXTJOIN is an Excel function that joins text from different cells or ranges into a single cell, using a delimiter you choose to separate each text value. It gives you more options than CONCATENATE because you can pick the delimiter and deal with empty cells.
The syntax is:
- =TEXTJOIN(newline, ignore_empty, text1, [text2],…)
- Reasons for:
- The delimiter is the space between each text value. It can be any string of characters, like a comma (“,”), a space (” “), or nothing at all.
- Please ignore_empty (required): Whether to include empty cells is set by this logical value (TRUE or FALSE). They are ignored by TRUE, but they are included by FALSE.
- Text1: The first string of text or range of text to join.
- text2,…, text_n (optional): You can add up to 252 more text strings or ranges to join.
Concatenating Text Strings with TEXTJOIN
When you use the TEXTJOIN function,
- The TEXTJOIN function can be used in Excel 2019 and later. It’s more flexible than the older CONCATENATE function because you can choose whether to include empty cells or not and also set a space between each text string.
![](https://www.bollyinside.com/wp-content/uploads/2024/02/1-611-jpg.webp)
This is how TEXTJOIN is written:
- TEXTJOIN(delimiter, ignore_empty, text1, [text2],…) in Excel
- Be careful when you use code.
- The character or string of characters that you want to put between each string of characters, like a comma, space, or hyphen.
- don’t care about empty: Whether to include empty cells in the result is set by this logical value, which can be either TRUE or FALSE. FALSE includes empty cells, but TRUE doesn’t.
- string1: The first text string or group of cells with text strings that you want to join.
- text2…: Extra text strings or ranges of cells to join (not required).
Tips and Best Practices for Using TEXTJOIN Efficiently
- When you use ranges in the range argument, don’t use absolute references ($A$1:$A$5), but use relative references (A1:A5) instead. This lets the formula change when it’s copied to different cells.
- Use dynamic ranges: Instead of setting a fixed range, make dynamic ranges based on criteria using functions like INDIRECT or OFFSET. This will give you more freedom.
- Put TEXTJOIN inside of other functions: You can get rid of extra spaces or formatting before joining by using TEXTJOIN with other functions like TRIM or CLEAN.
- Use array formulas: If you are working with large datasets, you might want to use array formulas (for example, press Ctrl+Shift+Enter after entering the formula) instead of making a separate formula for each cell.
- Think about using different functions. For example, functions like CONCAT or CONCATENATE might work better in some situations for simpler joins. Look at how complicated it is and pick the best fit.
FAQs
You can use the formula =TEXTJOIN(“ “,1,A2,B2) to join the names in cells A2 and B2 together with a space in between them. Notice that here I have used a binary input for ignore_empty. The formula=TEXTJOIN(” ,”,1,A3,B3) will join the names in cells E2 and B2 together with a comma in between them.
error occurs when TEXTJOIN is used in an older version of Excel where this function is not supported (pre-2019) or when the function’s name is misspelled. #VALUE! error occurs if the resulting string exceeds 32,767 characters.
Select the first cell and press Shift while you select the last cell in the range you want to merge. Important: Make sure only one of the cells in the range has data. Select Home > Merge & Center. If Merge & Center is dimmed, make sure you’re not editing a cell or the cells you want to merge aren’t inside a table.