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.
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
and you will get the output as “A8″.
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.
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.
And click on wrap text to show the line break in your excel file; you can use the line break to merge address data.
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”
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”)
The Output is
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.
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.
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 –