Text Functions: TEXTJOIN

Introduction

The TEXTJOIN function combines text from multiple cell ranges and strings while including a delimiter. In other words, it joins several cell values together to form a separate combined cell value. A delimiter specifies, or separates, the different text values combined. If the delimiter is within an empty text string, the TEXTJOIN function will effectively concatenate the ranges. This function is used when combining text within multiple cells into one line of text. This can include referencing a string of text to list dates, arrange a series of names and titles, or list a series of items.

Functions

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

The TEXTJOIN function joins multiple values together with or without a delimiter. TEXTJOIN can join values with the concatenation operator (an ampersand (&)) or by using one of several functions for concatenation.

The TEXTJOIN function has the following arguments:

  • delimiter: Required. A text string, either empty, with one or more characters enclosed by double quotes (for example: “, ”), or a reference to a valid text string; if a number is referenced, it will be treated as text
  • ignore_empty: Required. Input TRUE to ignore empty cells when combining values and type FALSE to indicate a missing value in the combined result
  • text1: Required. A text item (text string or an array of strings, such as a range of cells) to be joined
  • [text2, …]: Optional. Additional text items text string or an array of strings, such as a range of cells) to be joined; a maximum of 252 text arguments for the combination of text items, including text1, can be used

Uses

Joining a name and title together which are listed in separate cells.

Example: =TEXTJOIN(“ “,1,E3, B3:D3) (see image for reference to given example)

Concatenating values in the order they appear.

For example, cell A1 contains “Tea” and cell A2 contains “Coffee”. Using the following equation: =TEXTJOIN(“ “,TRUE,A1,A2), the text that follows will be “Tea Coffee”. You can also join cell values in a range with a comma and space.

Example: =TEXTJOIN(“,“,TRUE,A1:A3)

When using the TEXTJOIN function to concatenate numbers, any number formatting is lost.

For example, you will lose formatting on a set of dates (ex. 1-Dec-2021 in cell A1 and 2-Dec-2021 in cell A2 will revert to serial numbers). Instead, you can use the TEXT function to apply formatting during concatenation

Using the following equation: =TEXTJOIN(“-“,1,TEXT(A1,”mmm d”), TEXT(A2,“mmm d”)), the text that follows will be “Dec 1 – Dec 2”.

Practice

Practice using the TEXTJOIN function by answering the questions below in the highlighted cell.

  1. Combine all the CIPA ratings into a single column
    Answer:

    Afghanistan: 2-1.5-2.5-2.5-3.5
    Albania: NA-NA-NA-NA-NA
    …

Conclusion

Use text join to help you quickly combine two data points in two separate cells in to a single cell. This allows you to avoid other cumbersome methods of combining data such as copy and paste.

css.php