Excel 2019 Bible

Chapter 11: Using Formulas to Manipulate Text. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 

11

FIGURE 11.1

Joining first and last names

This example illustrates the use of the ampersand (&) operator. The ampersand operator

tells Excel to concatenate values with one another. As you can see in Figure 11.1, you can

join cell values with text of your own. In this example, we are joining the values in cells B3

and C3 separated by a space (created by entering a space in quotes).

Excel 2019 introduces the new TEXTJOIN function to provide an easier way to handle more

complex scenarios. This new function requires just a few arguments:

TEXTJOIN(delimiter,ignore_empty_values,text)

The first argument is the character that you want placed between the cells that you are

joining. If you type a comma as the “delimiter,” the function will place a comma between

the joined values.

The second argument determines what to do when Excel encounters an empty cell. You can

either set this argument to TRUE, telling Excel to ignore empty cells, or set it to FALSE.

The best way to think about this argument is how you want Excel to place your chosen

delimiter. Setting this argument to TRUE will ensure that Excel does not add extra commas

between your joined text because of blank cells in the selected range.

The third argument is the text to be joined. This can be a simple text string, or it can be an

array of strings like a range of cells. The TEXTJOIN function requires as least one value or

cell reference in this argument.

Figure 11.2 demonstrates how you can use the TEXTJOIN function to easily pull together

the first name, last name, and middle initial for each person in the table.