Combine data from Multiple columns in Excel

Following are 7 different methods to combine data from Multiple columns or cells in Excel.

Table of Contents

Using & sign

The & Sign, also known as the Concatenation operator can be used to join data in Excel.

="Christopher"&"Nolan" will return ChristopherNolan

To introduce space in between the text strings, we need to wrap it with a pair of double quotes.

="Christopher"&" "&"Nolan"&" "&51 will return Christopher Nolan 51

Now, the formula to combine data from 3 different cells B3, C3 and D3 with a Space in between.

=B3&" "&C3&" "&D3

CONCATENATE Function

The CONCATENATE function in Excel will join the text strings supplied into it and will return the concatenated text as result.

=CONCATENATE("Christopher"," ","Nolan"," ",51) will return Christopher Nolan 51

To combine data in the cells B3, C3 and D3 with a Space in between,

=CONCATENATE(B3," ",C3," ",D3)

CONCAT Function

The CONCAT Function was released with Excel 2019 and is an improved version of the CONCATENATE function. The difference between CONCATENATE and CONCAT function is that the latter can handle cell references as well as data ranges.

To combine data in the cells B3, C3 and D3 using the CONCAT function,

=CONCAT(B3," ",C3," ",D3)

TEXTJOIN Function

The TEXTJOIN function in Excel = CONCAT function + the option to add a ‘delimiter’ between the concatenated text + the option to ignore empty cells.

Needles to say, like the CONCAT function, the TEXTJOIN function can handle cell references as well as data ranges.

Following are two different formulas to combine data in the cells B3, C3 and D3 with a Space in between

=TEXTJOIN(" ",,B3,C3,D3)

=TEXTJOIN(" ",,B3:D3)

Flash Fill

Flash Fill is a kind of magic tool in Excel, which will repeat the pattern shown to it. Flash Fill can be used to Combine, Extract and Convert data.

Flash Fill is placed in the Data tab of the Excel Ribbon and the keyboard shortcut to apply Flash Fill is Ctrl + E.

To combine data from 3 continuous columns, select any cell in adjacent column and type in the expected output in that cell. Press ‘Ctrl + E’ and Excel will fill the remaining cells in the same manner.

Note that for the Flash Fill to work, source data should be in the adjacent column of the output column.

VAB – Macro to combine data from different columns

The following VBA code when executed will combine the data from the ‘current column’ and ‘2 columns on left side’ and will insert the concatenated data into the ‘adjacent column on right side’.

Sub CombineData()
Do While ActiveCell <> ""
ActiveCell.Offset(0, 1).FormulaR1C1 =ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1)& " " & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Select the first cell containing data in the third column and execute the above Macro.

Power Query

This method is recommended when the data is in tabular form. To combine the data in 3 columns,

Select the table > go the Data tab of the Excel ribbon > Click on From selection

If the data range is not an official Excel table, we will be prompted to convert the range into an Excel Table.

Click on OK and the data will be loaded into the Power Query Editor of Excel.

To combine data from all 3 columns, go to the Add Column tab and click on Custom Column

In the Custom Column dialog, specify a name for the new column and type in the following formula.

=[First Name]&" "&[Last Name]&" "&Number.ToText([Age])

Click on OK and we will have a new column with the concatenated data.

To remove the unwanted columns, Select the columns > Right-click on any of the column headers > Remove columns

To load the concatenated data into the worksheet > in the Home tab of the Power Query Editor > click on the split button Close & Load > select Close & Load to…

Use the Import Data dialog to specify the cell where you want to insert the output table.

The table with the concatenated text is loaded into the Excel worksheet.

This method is more dynamic than the other methods explained here.

Add a few names and corresponding age to the source table and click on the Refresh button in the Data tab to see the output table getting updated with concatenated text.