Excel 2016 – Combine Multiple Columns into One Using TEXTJOIN
Very often users need to combine text from two or more columns into one column. Until now, if you wanted to join text strings from a range of cells, you had to specify each cell individually and the delimiter between those cells. The new TEXTJOIN and CONCAT functions let you combine text strings from ranges of cells with or without using a delimiter. Simply refer to the range, specify the delimiter once and Excel does all the hard work. In this example, we will demonstrate TEXTJOIN to combine parts of an address.
Note: TEXTJOIN and CONCAT are replacements (easier) for the old CONCATENATE function, which gave the user the power to join strings of separate text into one cell.
- Open a file with columns that need to be combined.
- Click into the cell where you would like to display the combined text. In this example cell F2.
- Click Insert Function on the Formula Bar. The Insert Function dialog box displays
- Type TEXTJOIN in the Search for a function text area. Click Go. The Search results display. Select TEXTJOIN from the Select a Function list and then click OK.
- The Function Arguments window displays. Here is where you will enter what character to use to separate the combined text (Delimiter), what to do if there is no value in a cell (Ignore_empty), and the range of cells to combine (Text1). Click OK.
Note: As you enter the information, the formula is added to the Formula Bar and a preview of the results can be seen in the lower left corner of the Function Arguments dialog box.
- The combined text appears in the selected cell, separated by the specified delimiter. Use the AutoFill handle to drag the formula down to the rest of the cells.
- The TEXTJOIN function has combined text from five separate columns into one column.
Note: Any blank cells are ignored.