There are a number of ways in Excel to combine data for two or more cells in Excel. The technique and functions for merging data are:
- Using 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 and downloadable files wherever required.
Combining cells text by CONCATENATE function
In the CONCATENATE function, you may provide the text string, cell references, numbers or other characters like line breaks for combining the text. See the following example with formula and Excel sheet where I used A and B columns and displayed the combined result in C column.
The A column contains First Names and B column contains Last Names.
The CONCATENATE formula:
1 =CONCATENATE(A2, " ", B2)
After writing this formula in C2 cell, you may drag the fill handle to copy the formula to the other cells. For that, after writing the formula for C2 cell, go to the bottom of C2 cell till + 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 cells 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:
1 =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.
Note: For displaying the result in multi-lines as using CHAR(10) function, you have to use “Wrap Text” option for the cell. 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 is:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- The delimiter can be a space, comma or some other character(s). This is required.
- Use the TRUE value for ignore_empty argument if you want to omit the blank cells, FALSE otherwise. This argument is also required.
- The text1 argument can be a string, cell or range of cells that you want to combine. This is also required argument.
- 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:
1 =TEXTJOIN(", ", TRUE, A2:A5)
You can see, all First Names are joined in C2 cell that is separated by commas.
The Example of using multiple ranges in TEXTJOIN
For this example, I will use multiple ranges in TEXTJOIN formula. It will join 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:
1 =TEXTJOIN(" ", TRUE, A2:B2, ,CHAR(10), A3:B3, CHAR(10), A4:B4)
The resultant sheet:
As mentioned earlier, the TEXTJOIN function is available for Excel 2016, Excel online, Excel for iPad and iPhone, Excel Mobile, Android Tablets, and Phones.
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:
1 =A2 & " " & B2
This is the simplest of the ways if you require combining individual cells.
Merging cells by Flash Fill technique
The Flash Fill technique is available only in Excel 2013 for Windows and later versions. In this way of combining 2 cells, for example first and last names, type the complete name in C2 cell (exactly as in A2 and B2 cells containing First Name and Last Names, respectively).
Now as you start typing full name in C3 cell, Excel will understand that and suggests full name based on 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
Go to Data tab –> Flash Fill
Run Flash Fill Automatically
Tools –> Options –> Advanced –> Editing Options
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 CONCATENATE function, though latest release also supports CONCATENATE only for backward compatibility.
The general syntax of CONCAT function is:
Where text1 can be a simple string, individual cell or cell range.
CONCAT function example
The cell A5 and B5 are joined by using CONCAT function. Just like CONCATENATE example, I also used CHAR(10) function for adding new line:
The CONCAT formula:
1 =CONCAT("Full Name = ",A5, " ", B5, CHAR(10), "Shoe size = 8", """")
The result of above formula:
Using ranges in CONCAT function
The benefit of using CONCAT over CONCATENATE is that the CONCAT also allows using cell ranges.
See the following example where I will display multiple ranges using the CONCAT function.
The drawback is, as CONCAT syntax has no delimiter option so cells text is joined without space or any other character. You need to work out to sort this issue by using some function or adjusting cells text.