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:

=TEXTJOIN(delimiter, ignore_empty, text1, …)


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

    TEXTJOIN Excel Formula - Screenshot of Step 1

  2. Type TEXTJOIN (can be with large and small letters) and open bracket sign after =

    TEXTJOIN Excel Formula - Screenshot of Step 2

  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 ( , )

    TEXTJOIN Excel Formula - Screenshot of Step 3

  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

    TEXTJOIN Excel Formula - Screenshot of Step 4

  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

    TEXTJOIN Excel Formula - Screenshot of Step 5

  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

    TEXTJOIN Excel Formula - Screenshot of Step 6

  7. Type close bracket sign after all of the combination components have been inputted

    TEXTJOIN Excel Formula - Screenshot of Step 7

  8. Press Enter
  9. The process is done!

    TEXTJOIN Excel Formula - Screenshot of Step 9



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!




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert