How to Combine Data From Two or More Cells/Range in Excel

There are a numbers 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)

A visual illustration of how to combine/merge Excel Cells data by using different ways. Learn how t use CONCATENATE function, '&', TEXTJOIN function, CONCAT function, and Flash Fill technique.

Combine/Merge Data in Excel

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:

  1. Using CONCATENATE function
  2. By using ‘&’ (ampersand sign)
  3. TEXTJOIN function for merging ranges (Excel 2016, Excel online)
  4. Using CONCAT function (from Excel 2016)
  5. 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

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 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:


Excel combine data

  • 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", """")

Excel combine text 2

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 the CHAR(10) function, you have to use the “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:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)


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:

Excel TEXTJOIN multiple

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:

=A2 & " " & B2

ampersand merge

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

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.

The Flash Fill technique is available only in Excel 2013 for Windows and later versions.

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:

CONCAT(text1, [text2],…)

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:

CONCAT combine

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:

=CONCAT(A2:B3,CHAR(10), A4:B5)


CONCAT combine ranges

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.