Combine/Merge Data in Excel
There are several ways in Excel to combine data for two or more cells in Excel. The technique and functions for merging data are:
- Using the CONCATENATE function.
- By using ‘&’ (ampersand sign).
- TEXTJOIN function for merging ranges (Excel 2016, Excel online).
- Using CONCAT function (from Excel 2016).
- The Flash Fill technique – Excel 2013 (Windows) and later.
Each of these methods for combining columns, rows, and ranges data is explained below with example sheets.
Combining cells text by CONCATENATE function
See the following example with the formula and Excel sheet where I used the A and B columns and displayed the combined result in the C column.
The A column contains First Names and the B column contains Last Names.
The CONCATENATE formula:
=CONCATENATE(A2, " ", B2)
- After writing this formula in the C2 cell, you may drag the fill handle to copy the formula to the other cells.
- For that, after writing the formula for the C2 cell, go to the bottom of the C2 cell till the + sign appears with the solid line.
- Now drag to the desired cell. As you leave the handle, you will see C3, C4, etc cells are filled with respective texts from A and B cells.
- Excel manages the cell numbers automatically.
Using text strings, cells, and numbers with space and double quote examples
The following CONCATENATE formula shows using the cell’s text, constant text strings, numbers, space, and double quotes.
As such, text strings are enclosed in double quotes; the purpose is to show what if you want to display a double quote in the resultant combined string.
The formula for C2 cell:
=CONCATENATE("Full Name is ",A2, " ", B2, CHAR(10), "Shoe size = 9", """")
The CHAR(10) function added a new line that you can see for the “Shoe Size = “ in the resultant text string.
You can see, double quotes are used four times to display once in the resultant string.
The Wrap Text option is visible under the Home tab.
The TEXTJOIN function for merging ranges
The TEXTJOIN function enables joining text strings and multiple ranges where each cell value is combined by a given delimiter.
The syntax of using the TEXTJOIN function:
Where:
| Argument | Description |
| delimiter | The delimiter can be a space, comma, or some other character(s). This is required. |
| ignore_empty | Use the TRUE value for ignore_empty argument if you want to omit the blank cells, FALSE otherwise. This argument is also required. |
| text1 | The text1 argument can be a string, cell, or range of cells that you want to combine. This is also a required argument. |
| text2 and so on | The text2 and so on represent the other ranges or text strings that you want to merge (optional). |
An example of TEXTJOIN with comma and range
In this example, I will merge the text of range from A2 to A5 cells that contains First Names.
The value for the delimiter is a comma, so each cell value should be separated by a comma:
The TEXTJOIN formula:
=TEXTJOIN(", ", TRUE, A2:A5)
The result:
You can see that all First Names are joined in the C2 cell that is separated by commas.
The Example of using multiple ranges in TEXTJOIN
For this example, I will use multiple ranges in the TEXTJOIN formula. It will join the First and Last names for each row while the delimiter is a space.
Besides, the CHAR(10) function is also used for displaying names with line breaks:
The formula of TEXTJOIN:
=TEXTJOIN(" ", TRUE, A2:B2, ,CHAR(10), A3:B3, CHAR(10), A4:B4)
The resultant sheet:
Using ‘&’ for combining strings
The ‘&’ operator works like the CONCATENATE function where you may combine text strings, numbers, individual cells, etc.
See the following example with the formula:
=A2 & " " & B2
This is the simplest of the ways if you require combining individual cells.
Merging cells by Flash Fill technique
In this way of combining 2 cells, for example, first and last names, type the complete name in the C2 cell (exactly as in A2 and B2 cells containing First Name and Last Names, respectively).
Now as you start typing the full name in the C3 cell, Excel will understand and suggest the full name based on the A3 and B3 cell.
Press Enter to fill the name in C3 in a Flash.
If this feature is not working, you have to enable this as follows:
Run it manually
Run Flash Fill Automatically
There you may see the Automatically Flash Fill option, check it.
Combining text by CONCAT function
Again, if you are using the latest version of Excel (2016 or above) then you may use the CONCAT function for merging text.
The CONCAT is the replacement for the CONCATENATE function, though the latest release also supports CONCATENATE only for backward compatibility.
The general syntax of the CONCAT function is:
Where text1 can be a simple string, individual cell, or cell range.
CONCAT function example
The cells A5 and B5 are joined by using the CONCAT function. Just like the CONCATENATE example, I also used CHAR(10) function for adding the new line:
The CONCAT formula:
=CONCAT("Full Name = ",A5, " ", B5, CHAR(10), "Shoe size = 8", """")
The result of the above formula:
Using ranges in the CONCAT function
The benefit of using CONCAT over CONCATENATE is that the CONCAT also allows the use of cell ranges.
See the following example where I will display multiple ranges using the CONCAT function.
The formula:
Result:
The drawback is that CONCAT syntax has no delimiter option so cell text is joined without space or any other character.
You need to work out how to sort this issue by using some function or adjusting cell text.