In this post, you will learn how to join two strings or multiple strings together in Excel. We will look at the use of ampersand (&) operator and TEXTJOIN function to accomplish these tasks.
If you want, you can download the workbook used in this post from here – click here to download
Joining text strings –
One of the more basic text manipulation actions that you can do is joining text strings
together. In the example shown in Figure below, we are creating a full name column by
joining together 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 below 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.
