Using the CONCAT function
Follow these steps:
- Continue with the workbook from the previous section; that is, SSGJoin.xlsx.
- We will use the CONCAT function to combine the surname and name of the customer into a single cell. Click inside cell R2 to begin constructing your formula.
- Type =CONCAT( to start the formula.
- Locate and click on cell D2, then add a comma, after which you should click the next cell reference to include or add a delimiter. In this case, we will add an inverted comma, comma, and a space, then another inverted comma.
- Click on cell C2 to collect the name of the customer:
Figure 9.59 – The CONCAT formula’s construction
Now that you have mastered the & and CONCAT methods, let’s look at the
TEXTJOIN function.
Constructing the TEXTJOIN function
The TEXTJOIN function is more efficient due to the following reasons:
- It allows a delimiter to be entered once, which will automatically be included for each value of the text string.
- It allows you to include spaces or remove blank spaces in the text string. Let’s construct the TEXTJOIN formula:
- Working in the same workbook that you worked in for the previous example, notice that row 16 is missing bits of data. Not all the cells contain data. We will use the TEXTJOIN function to include all the data from the range, excluding the empty cells, but we will include a comma and a space after each value of the string.
- The answer needs to be in cell R16. Select cell R16.
- Type =TEXTJOIN( to start the formula. The formula syntax we require is as follows:
Figure 9.60 – The TEXTJOIN syntax’s construction
- Enter “, ” for the delimiter.
- Add a comma, then select TRUE for the ignore_empty cells, then add a comma to separate the arguments again.
- Select the A16:O16 range, and then add a parenthesis to end the formula.
- Press Enter to confirm and display the result.
With that, we have mastered joining data in Excel. Let’s move on to the final section of this chapter and learn how to separate data.
