How to Use the TEXTJOIN Formula in Excel

Concatenating cells together using a delimiter to separate them. This is especially more efficient to use versus a manual method of concatenating cells together if you are working with a large data set and just need to concatenate them together with a common delimiter.

• Delimiter (required): The delimiter is the character that separates the text joined together. For instance, the cat could be separated as the-cat with a hyphen.
• Ignore_empty (required): There is an option as to whether you’d like to ignore empty cells or still join them.
• Text1 (required): The text syntax refers to the text cell that you’d like to join. There are options for joining multiple cells.

Example #1:

When a car is sold, the Financial Analyst is supposed to create a receipt ID for the sale and input the Receipt ID into the ERP system for the dealership. Receipt ID is supposed to be equal to the concatenation of the car brand, car model ID, and year of sale. Each of these characteristics is supposed to be separated by a hyphen (“-“).

Solution:

There are a couple of ways of achieving this as shown below. The TEXTJOIN method achieves the desired result with the hyphen as the delimiter. The year formula is used to extract the year from the date of the sale field.

On the other hand, a manual process could have also been used for creating the receipt ID by joining cells with &”-“& one at a time.

While this seems intuitive, it is not scalable. Let’s presume you had another task where you needed to join hundreds of rows of data with a common delimiter for upload to software or for record-keeping. The TEXTJOIN formula only requires the user to enter the delimiter once while the manual method of joining cells requires it to be performed manually between each and every cell.

Example #2:

A Financial Analyst wants to summarize which vehicle brands are sold by the dealership sales reps. The Analyst wants to have a table created that is automatically refreshed each time the ERP system’s excel export is loaded into a workbook. The excel report from the ERP system is a sales ledger showing sales by sales rep with the fields populated with the vehicle type.

Solution:

Next note, that we are ignoring empty cells by indicating “TRUE”. The following part of the formula is the most important which does a lookup to the source table and determines whether it contains the car type and if it does it returns the header name of the table which is the sales rep. If there is no match found, then it inserts a blank hence why we put “”. You can see that there are no sales reps who have sold a Jeep and therefore the Jeep row in the below table is blank.

The final thing to note on this formula is that doing a lookup to another table like this while using the TEXTJOIN formula requires the use of an array formula. Therefore, when you are complete when formula, you must hold control + shift and then hit enter in order for the formula to work. You can click on one of the formulas below and see that they are arrays as they contain the { } brackets around it. Once you create the formula once, you can drag it down (see sales reps column below).

Formula

Result

This is a great use case of the TEXTJOIN formula as it allows you to apply this to different circumstances and in complex scenarios where you need to summarize data similar to this.

You can use this formula to save a lot of time and effort as it eliminates manual work rounds like pivoting data or just manually filtering and writing down people’s names for certain results. Instead, the TEXTJOIN formula will be able to help you automate it!

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!