Combine Cells in Excel

How to Combine Cells in Excel?

Combining cells or also known as merging cells in excel is a method where we combine one or more than one cells to make it as a single cells, the button used to combine cells is called merge and center and it is available in the Home tab in the alignment section, to combine cells we need to select more than two cells and press merge and center button.

Top Methods to Combine Cells in Excel

Let’s understand the working of Combine cells in excel with the below methods.

You can download this Combine Cells Excel Template here – Combine Cells Excel Template

Example #1: Add Spaces to Combined Text

In this example, we have used “&” to combine the product category with its code.

Here we have used the =A4&” “&B4 formula to combine both data

Combine Cells in Excel Example 1

and you will get the output as “A8″.

Combine Cells in Excel Example 1-1

You can also merge cells with space with “&” operator and with concatenating function, as shown in the below table.

For example, =A16&” “&B16 will merge “Tanuj” & “Rajput” to “Tanuj Rajput” as shown in the below table.

Combine Cells in Excel Example 1-2

Example #2: Add Line Break to Combined Text

If you want to merge two columns with a line break, you can achieve it by adding Char(10) between the “&” operator. CHAR (10) is a line feed in Windows, which means that it forces anything after it to go to a new line.

Just apply the =F4&CHAR(10)&G4 formula.

Combine Cells in Excel Example 2

And click on wrap text to show the line break in your excel file; you can use the line break to merge address data.

Combine Cells in Excel Example 2-1

Example #3: Combine Cells with Text and a Number

Suppose you need to combine the text with number data, then you can achieve it by taking the text as a string and merging them with & operator as shown in the below example, the text will be added to the payment terms.

i.e. =”Due in “&A34&” days”

Text and a Number Example 3

Example #4: Combine Cells with Text and a Formatted Date

In this example, we are merging date with text by using & operator. But in the first example, the output date is in number, so to overcome this problem, we have to use text formula in excel with date, for formatting as shown in the second example. 

=”Today is “&TODAY ()

=”Today is “&TEXT (TODAY (),”mm-dd-yyyy”)

Formatted Date Example 4

The Output is

Formatted Date Example 4-1

Example #5: Combine Cells using VBA.

Suppose we have the data located in the excel sheet range from A4 to B4, and you want to combine both in cell C4 then use the below code.

The following code sample combines both the cells to a single cell without any data loss.

Code:

Sub Mergecells()

  Range(“C4”).Value = Range(“A4”).Value & Range(“B4”).Value

End Sub

Things to Remember

  • Merge cells in excel can merge two cells into one cell, but it keeps data from the first cell only.
  • Must use text formula while combining the date with & operator otherwise date will appear as a number.

Recommended Articles

This has been a guide to Combine Cells in Excel. Here we discuss how to quickly combine cells in excel using & operator along with other examples and a downloadable template. You may also look at these useful excel tools –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>