TEXTJOIN Excel Formula

In this tutorial, you will learn how to use excel TEXTJOIN function. TEXTJOIN excel formula is useful to combine data into a text using a separator. You also have the option to disregard empty cells in TEXTJOIN excel formula if you combine data in a range.

Why do We Need to Learn Excel TEXTJOIN Function?

In the data processing process on a spreadsheet, sometimes we need to combine some data which are located in separated cells into one so we can show them in a much simpler way. In the combination process, often we also need to avoid empty cells or need to use a sign as separators in the text which becomes the result of the combination process.

As an example of this need, imagine we want to create product codes to make the information lookout of the products simpler. In the formulation process of the product codes, things like the product category, production date, production location, or other information want to be combined using separators such as dash (“-“) in between the information variant. All of the information is separated in some columns on our spreadsheet. Probably it will be easy to combine them manually if there are only a few products that we want to make the code from. But, if the products are many and we also need to pay attention to the empty cells in each of the columns so they are not combined in the product codes, then it will be very troublesome and take a lot of time if we do it manually.

To help us do it fast, we can use the TEXTJOIN excel formula. Excel TEXTJOIN function helps you in combining various data into a text by optionally using a separator sign and ignore empty cells. TEXTJOIN excel formula is very useful to use if you often deal with a combination process for various objectives in the spreadsheet.

What is TEXTJOIN Excel Formula?

TEXTJOIN excel formula is a formula with the usage objective to get a text which is the combined results of some data using a separator sign and the option to ignore empty cells in the combination process if you use cell range for the formula process. Excel TEXTJOIN function can be used since the 2019 version of excel.

Generally, the inputs of excel TEXTJOIN function can be explained as follows:



Notes:

• delimiter = the separator sign for the result
• ignore_empty = notification whether we want to ignore empty cells or not
• text1 = the component to be combined
• … = other components that we want to combine

How to Use Excel TEXTJOIN Function?

The following will explain how to write the TEXTJOIN excel formula. Excel TEXTJOIN function needs three required inputs which are the separator sign for the result, the notification to ignore empty cells or not, and the components to be combined. You can input as many cell ranges or components that you need to combine as you want in this excel TEXTJOIN function.

Using TEXTJOIN Excel Formula

1. Type equal sign ( = ) in the cell where you want to put the process result in
   
   
   
   
2. Type TEXTJOIN (can be with large and small letters) and open bracket sign after =
   
   
   
   
3. Input the separator sign / cell coordinate where the separator is in after the open bracket sign. If you type it directly (not input it in the coordinate form), then you must type it between two quotes signs (“”). Then type a comma sign ( , )
   
   
   
   
4. Input your notification to the formula whether you want to ignore the empty cells or not if later you input cell ranges in the next input part for the data that you want to combine. Type TRUE if you want to ignore and not use empty cells in the combined result and type FALSE if you do not want to ignore them. Then type a comma sign
   
   
   
   
5. Input data / cell coordinate where the data that you want to combine is. You can also input a cell range directly if the data that you want to combine is close to each other (usually we use cell range directly on this input part). If you type it directly (not coordinate) and the form of it is not number, then you must type it between two quotes signs
   
   
   
   
6. Optional: Type a comma sign then input other data / cell coordinates / ranges if there are still other things that you want to combine for the final result. Separate with comma signs for each of the things that you input. If you type it directly (not coordinate) and the form is not number, then you must type it between two quotes signs
   
   
   
   
7. Type close bracket sign after all of the combination components have been inputted
   
   
   
   
8. Press Enter
9. The process is done!
   
   
   
   

Exercise

After you have understood how to use excel TEXTJOIN function, now is the time you deepen your understanding by doing the exercise below.

Download the exercise file and answer all of the instructions. Download the answer key file to check all of your answers if you have done the exercise or when you are confused about how to finish the exercise!

Link for the exercise file of the excel TEXTJOIN function:
Download here

Instructions

Answer each instruction on the gray-colored place in each corresponding row!

1. Combine all of the contents of columns 1 to 5 in the No. 1 row with comma and space as the separator signs. Ignore the empty cells!
2. Combine all of the contents of columns 1 to 5 in the No. 1 row with comma and space as the separator signs. Include the empty cells!
3. Combine all of the contents of columns 1 to 5 in the No. 1 row and an X letter with dash as the separator sign. Ignore the empty cells!

Link for the answer key file of the excel TEXTJOIN function:
Download here

Additional Notes

• If you want to combine manually, then you can do it using and sign (&) between the data that you want to combine
• There are also other formulas for a simpler combination process in the spreadsheet which are CONCAT and CONCATENATE
• Whatever the data type that you combine, the result of TEXTJOIN excel formula is a text type
• If you want space as the separator sign, then do not forget to include the space in the separator input part of the TEXTJOIN excel formula!