How to Combine Cells in Excel: 5 Quick Methods Explained

Combining cells in Excel is a helpful skill to organize and present data better in your spreadsheets. It lets you put together the contents of multiple cells into one, making complex data easier to understand and look nicer.

There are different ways to do this in Microsoft Excel, and each method has its own advantages and uses.

To combine cells in Excel, you can use the Ampersand symbol (&) or the CONCAT/ TEXTJOIN functions. They all merge text from multiple cells into a single cell, making data presentation smoother and more manageable. Another easy method is using the Merge and Center icon in the Home tab.

In this article, we’ll explain various techniques to combine multiple cells in Excel. We’ll give you step-by-step instructions and practical tips to improve your spreadsheets.

5 Ways of Combining Cells in Excel

Combining cells in Excel is a useful technique for organizing and displaying data. It allows you to merge the contents of two or more adjacent cells into one larger cell.

This can help make your spreadsheet more readable and tidy, especially when dealing with text data or labels.

There are 5 methods for combining cells in Excel. They are:

1. Using CONCAT function
2. Using CONCATENATE function
3. Using TEXTJOIN function
4. Using the ampersand (&)
5. Using merge cells

1) Using CONCAT Function to Combine Cells

This function is helpful when combining text from separate cells into a single cell.

The formula for this is =CONCAT(A2, ” “, B2), where A2 and B2 are the cell references you want to combine.

You can also use commas to separate the cells and quotation marks to add spaces, commas, or other text within the formula.

2) Using CONCATENATE Function to Combine Cells

This is a similar method to CONCAT but with a different syntax.

The formula for CONCATENATE is =CONCATENATE(A2, ” “, B2).

Like CONCAT, you can also include spaces and other characters within the formula.

3) Using TEXTJOIN Function to Combine Cells

You can combine multiple cells using Excel TEXTJOIN function.

The formula for TEXTJOIN is =TEXTJOIN(” “,TRUE,A2:B2)

4) Using The ampersand (&) to Combine Cells

You can use the ampersand symbol to join cells together.

For example, the formula to combine cells A2 and B2 with a space in between would be =A2 & ” ” & B2.

5) Using Merge Cells to Combine Cells

If you want to merge cells so that the content is centered across the merged cells, you can use the Merge & Center feature.

To do this follow the below steps.

1. Select the cells you want to merge.
2. Go to the Home tab and click on the Merge & Center button.

However, be aware that merging cells can overwrite existing data, so ensure you have a backup or copy of the original data when merging.

Methods to Combine Cells

In this section, we’ll explore different methods to combine cells in Excel.

There are primarily three methods: using formulas, using functions, and using the Merge & Center feature.

1.) Using Formulas

To combine cells using a formula, you can use the ampersand (&) symbol. Follow these steps:

1. Select a blank cell where you want to display the combined data.
2. Type = and click the first cell you want to combine.
3. Type & and use quotation marks with a space enclosed to include spaces in the combined cell. For example, type ” “.
4. Repeat steps 2 and 3 for all cells you want to combine.

For instance, if you want to combine cells A2 and B2, enter the formula =A2&” “&B2 into the desired cell.

2) Using Functions

Excel also provides functions such as CONCAT, TEXTJOIN, and CONCATENATE to combine cells.

In this section, we will explore how you can combine cells using the above functions.

1) Using CONCAT to Combine Cells

The CONCAT function allows you to merge the contents of two or more cells into a single cell. The syntax for this function is =CONCAT(cell1, cell2, …).

You can use commas to separate the cells you want to combine and quotation marks to add spaces, commas, or other text.

1. Select a blank cell for the combined data.
2. Type =CONCAT( and select the cells you want to combine, separated by commas.
3. Close the parentheses and press Enter.

For example, to combine cells A2 and B2, use the formula =CONCAT(A2, B2).

2) Using CONCATENATE to Combine Cells

The CONCATENATE function is another method for combining cells in Excel. It works similarly to the CONCAT function, but its syntax is slightly different: =CONCATENATE(cell1, cell2, …).

1. Select a blank cell for the combined data.
2. Type =CONCAT( and select the cells you want to combine, separated by commas.
3. Close the parentheses and press Enter.

For example:

=CONCATENATE(A2, " Family")

The following screenshot demonstrates this method:

In the above example, the formula would combine the contents of cell A2 and the word “Family” with a space in between.

3) Using TEXTJOIN to Combine Cells

The TEXTJOIN function provides additional flexibility when combining cells in Excel.

While the CONCAT and CONCATENATE functions require manual entry of separators between cells, the TEXTJOIN function allows for more dynamic concatenation using separators.

The syntax for TEXTJOIN is =TEXTJOIN(delimiter, ignore_empty, cell1, cell2, …). For example

1. Select a blank cell for the merged data.
2. Type =TEXTJOIN( and enter the delimiter (e.g., a comma or space) enclosed in quotation marks.
3. Type a comma, followed by TRUE or FALSE to indicate whether to ignore empty cells.
4. Select the cells you want to combine (you can select ranges too), separate by commas, and close the parentheses.

For example, if you wish to combine cells A1 to A4 with a comma, use the below formula.

=TEXTJOIN(", ",TRUE,A1:A4)

This formula would combine the contents of cells A1 through A4, using a comma and a space as a delimiter and ignoring any empty cells within the range.

3) Using Merge & Center

The Merge & Center feature in Excel helps you to merge cells while also centering their content.

You can follow the steps below to combine cells using this method:

1. Select the cells you want to merge together.
2. Click on the Home tab in the menu.
3. Locate the Alignment group and click Merge & Center.

In addition to the main Merge & Center option, you can choose other merge options by clicking the drop-down arrow:

• Merge Across: Merges selected cells in the same row into a single cell.
• Merge Cells: Merges selected cells into a single cell without centering the content.

By using these methods, you can effectively combine Excel cells according to your requirements.

Working with Text and Data

When working with text and data in Excel, you may need to combine cells containing different types of information, like names, dates, and numbers.

Excel offers various methods to combine cells while retaining the correct formatting.

This section will discuss two sub-topics: Adding Commas, Spaces, and Dashes and Handling Dates and Numbers.

1) Adding Commas, Spaces, and Dashes

When combining text cells in Excel, it’s common to add commas, spaces, or dashes as separators.

The CONCAT function can be used to achieve this.

Here’s an example that demonstrates how to combine name and country cells with a dash between two spaces:

=CONCAT(A2," - ",B2)

The following screenshot demonstrates this method:

In this formula, A2 (first cell) contains the name, and B2 (second cell) contains the country.

Another option is to use the ampersand (&) to join cells together:

=A2&" - "&B2

The following screenshot demonstrates this method:

This formula works in the same way as the CONCAT function and produces the same result.

2) Handling Dates and Numbers

When combining multiple cells that include dates and numbers, it’s essential to format them correctly. Excel allows you to customize the appearance of merged cells using the TEXT function.

In the below example, you have a name in cell A2 and a grade point average in cell B2, you can combine these cells with a dash separator as follows:

=A2&"-"&TEXT(B2,"0.0")

The following screenshot demonstrates this method:

In this example, the TEXT function is used to format the number in cell B2 with one decimal place.

When working with dates, apply a specific date format using the TEXT function.

For example, to combine a name with a joining date, use:

=A2&"-"&TEXT(B2,"dd/mm/yyyy")

In this formula, A2 contains the name, and B2 contains the joining date. The TEXT function formats the date as dd/mm/yyyy.

By using these techniques, you can effectively combine various data types in Excel, maintaining proper formatting and ensuring readability.

How to Combine Cells Using VBA

Visual Basic for Applications (VBA) is a powerful tool that can enhance your Excel experience. With VBA, you can perform advanced operations, such as combining cells, more efficiently and effectively.

One way to combine cells using VBA is through the MergeCells property, as shown in the following example:

Range("A1:B2").MergeCells = True

In this example, cells A1 to B2 are combined into a single cell.

VBA can be useful for users who want to automate routine tasks or run complex operations that may be difficult or time-consuming using regular Excel functions.

Learn more about Excel by watching the following video:

Final Thoughts

As a data specialist, you’ll often face situations where data manipulation is important.

Combining cells in Excel is like merging information to create a better picture. It’s easy – use the “&”, Excel function such as CONCAT, CONCATENATE, TEXTJOIN or Merge and Center icon.

Your data will look cleaner and more organized. Whether you’re new to Excel or experienced, this skill will make your spreadsheets more visually appealing and easier to understand.

Frequently Asked Questions

In this section, you’ll find some frequently asked that you may have when combining cells in Excel.

How do I merge cells in Excel without losing data?

To merge cells in Excel without losing data, you can use the Merge & Center option on the Home tab > Alignment group.

Select the adjacent cells you want to combine and then click the Merge & Center button. It is important to note that only the content of the upper-left cell will be kept.

The contents of other cells will be deleted in the process.

What is the formula for combining cells with text in Excel?

You can combine cells with text in Excel using the CONCAT or CONCATENATE function.

To do so, type =CONCAT or =CONCATENATE followed by the cell references you want to combine separated by commas.

For example: =CONCAT(A2, ” “, B2) or =CONCATENATE(A2, ” “, B2) to combine the content of cells A2 and B2 with a space between them.

How can I merge multiple columns into one in Excel?

To merge multiple columns into one in Excel, you can use the TEXTJOIN function.

This function allows you to specify a delimiter and to ignore empty cells. The syntax for the TEXTJOIN function is: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …).

For example, if you want to merge columns A, B, and C with a comma separator, you can use the formula =TEXTJOIN(“,”, TRUE, A2:C2).

What is the process to concatenate columns in Excel?

Concatenating columns in Excel can be done using the CONCAT, CONCATENATE, or TEXTJOIN functions.

These functions allow you to combine the contents of multiple cells into one cell.

To concatenate columns, simply type the corresponding function, followed by the cell references you want to combine, separated by commas or an ampersand (&) operator. For example: =A2 & B2 & C2.

How do I combine cells with multiple lines in Excel?

To combine cells with multiple lines in Excel, you can use the CHAR function along with the CONCAT, CONCATENATE, or TEXTJOIN functions.

The CHAR function allows you to insert a line break character (CHAR(10)) between the cell contents.

For example: =CONCAT(A2, CHAR(10), B2) will combine cells A2 and B2, with a line break between them.

How can I merge data from different cells in Excel?

Merging data from different cells in Excel can be achieved by using a combination of the CONCAT, CONCATENATE, or TEXTJOIN functions, along with other functions like CHAR for line breaks.

These functions help you combine the contents of various cells into a single cell, allowing you to display and manipulate the merged data more easily.