It can be tedious to combine data manually in Excel. Luckily, you can save time and energy by taking advantage of formulas and VBA to combine cells in Excel. In this tutorial, I will guide you step by step on how to do it.
How to combine two cells without space
Combining two cells without space is easy. Follow these simple steps.
1. Enter the = sign in a new cell where you wish to place the combined results.
2. Select the first cell you wish to combine
3. Type the & Symbol
4. Select the second cell you wish to combine and press enter
The final formula should be something like this. =C5&D5
Alternatively, you can use the =CONCATENATE(C5,D5) function to combine two cells without space.
How to combine cells with space
1. Enter the = sign in a new cell where you wish to place the combined results.
2. Select the first cell you wish to combine
3. Type the & Symbol
4. Type ” “ . This will add a space immediately after the content of the first cell
5. Now select the second cell that you wish to join and press enter.
The formula should appear like this. =C6& ” “&D6
The equivalent of the & function that combines cells with space is =CONCATENATE(C5,” “,D5). Just like the & function, a space when joining cells using CONCATENATE() is represented by a blank space I between double space
How to combine cells with comma
1. Enter the = sign in a new cell where you wish to place the combined results.
2. Select the first cell you wish to combine
3. Type the & Symbol
4. Type ”,“ . This will add a comma immediately after the content of the first cell
5. Type ” “ . This will add a space immediately after the comma.
6. Now select the second cell that you wish to combine and press enter.
The final formula to combine cells with a comma is =C7&”,”&D7
You can replace C7 and D7 with your cells of choice
How to combine cells with line breaks
Combining cells is an easy and straightforward task. But combining cells with line breaks in Excel can be a daunting task. However, with the right formulas, you can achieve the goal easily. Follow these steps.
Assuming you have different columns containing names and addresses of people. For example, in the image below.
You can combine all these cells in one column with line breaks to have a neat-looking address.
As you can see from the image below. Combining them with a simple & or CONCATENATE() makes the address appear as a sentence. This is not a good way to represent an address. Therefore, we need to add line breaks.
To do this, you need to include the CHAR(10) function. This is a function that forces anything that appears after it to start in a new line.
Apply the formula below in the first cell where you wish to combine addresses with line breaks
=B5&CHAR(10)&C5&CHAR(10)&D5&CHAR(10)&E5&CHAR(10)&F5
After applying the formula, press enters and then apply wrap text.
Double-click on the fill handle or drag it down to combine the remaining cells with a line break.
Learn how to combine Date and Time from two separate cells into one.
How to combine two columns
You can combine two columns using the & or the CONCATENATE functions and the fill feature on Excel. Follow the steps below.
Using the & function
1. Enter the = sign in a new cell where you wish to place the combined results.
2. Select the first cell you wish to combine
3. Type the & Symbol
4. Type ” “ . This will add a space immediately after the first cell
5. Type a second & symbol and select the second cell
6. Press on Enter, and Excel will combine the two cells automatically.
7. Double-click on the fill handle to combine the remaining cells of the columns. Alternatively, you can hold it a the bottom right corner and drag it to combine the remaining columns
As you can see from the image above, Excel has combined both columns with a space. The final formula that we have applied to the rest of the cells looks like this. =B5&” “&C5
Using Concatenate Function
You can also use the concatenate function to combine cells or columns.
1. Type the following formula in the first row of the cells that you wish to combine
=CONCATENATE(A3,” “,B3) : Use this formula if you wish to combine columns with space
=CONCATENATE(A3,”,”,B3) :Use this formula if you wish to combine columns with a comma
=CONCATENATE(A3,B3) : Use this formula if you wish to combine cells without space.
2. Press enter to apply the formula
3. Double click on the fill handle or drag it down to combine the remaining columns
Download Free Practice Sheet
VBA Code to combine cells in Excel
You can easily combine cells in Excel using VBA code.
1. Open the spreadsheet containing cells that you wish to combine
2. Launch the VBA editor by pressing fn + F11 on Mac or Alt+f11 shortcut on windows
Sub CombineCells()
Dim RangeToCombine As Range
Dim CombinedString As String
Dim Separator As String
Dim DestinationCell As Range
' Select the range of cells to combine
Set RangeToCombine = Application.InputBox(prompt:="Select the range of cells to combine", Type:=8)
' Ask user to choose separator
Separator = InputBox("Enter the separator you want to use between values. Type 'space' for space or 'linebreak' for line break", "Choose Separator")
If Separator = "space" Then
Separator = " "
ElseIf Separator = "linebreak" Then
Separator = Chr(10)
Else
MsgBox "Invalid separator entered. Please try again."
Exit Sub
End If
' Select the destination cell for the combined string
Set DestinationCell = Application.InputBox(prompt:="Select the cell where you want to output the combined string", Type:=8)
' Combine the cells
For Each cell In RangeToCombine
CombinedString = CombinedString & cell.Value & Separator
Next cell
' Remove the last separator
If Len(CombinedString) > 0 Then
CombinedString = Left(CombinedString, Len(CombinedString) - Len(Separator))
End If
' Output the combined string in the destination cell
DestinationCell.Value = CombinedString
End Sub
3. Click on Insert, then select module
4. Copy and paste this VBA code into the editor
5. Head over to the worksheet containing the data you wish to combine. Open the developers tab and click on Macros
6. Click run on the Macro “CombineCells”
7. Select the range of cells you wish to combine the pop-up screen and press ok
8. Type the separator that will separate the data in the destination cell. Type space for space and linebreak for line break
9. Select the destination where you wish to combine the cells and press ok
Hurray: The cells will be combined in the destination cell successfully
You can apply the VBA code to the remaining cells.
Frequently Asked Questions
1. How to combine three Cells in Excel ?
You can combine three cells in Excel by using & or CONCATENATE() function.
For instance, if you want to combine cells A1, B1, and C1, you need to apply the following formula.
=A1&B1&C1 :This will combine A1, A2 and A3 without space
=A1&” “&B1&“ ”&C1 : This will combine the three cells A1,B1 and C1 with space.
=A1&” “&B1&“ ”&C1 : This will combine the three cells A1,B1 and C1 with a comma
2. How to combine three columns in Excel?
First, combine the cells in the first row using any of the formulas we have discussed above. Then double-click on the fill handle or drag it down to combine all cells in the three columns.
3. How to combine cells with formatting?
For example, if you want to combine a cell with text and another one with percentage, you will have to add the TEXT function. The formula for combining cells or columns with formatting is =B5&” “& TEXT(C5,”0.00%”)
See the image below on how to preserve formatting while
4. How to combine cells in Excel with names?
You can combine cells in Excel with names using the & or CONCATENATE(). However, you need to add a double quote with space in between the names. This will help to separate the names with a space giving them proper formatting.
1. Type the following formula in the first cell where you want to combine names.
=B5&” “&C5&” “&D5
2. Press enter to apply the formula
3. Double click on the green fill handle or drag it down to combine the remaining names.
Final Thoughts
Combining cells and columns in Excel is easy. All you need is to apply the right formula depending on the contents you wish to combine. You can also combine text will line breaks or preserve formatting. These two formulas. & and CONCATENATE() are the go-to for everything you wish to combine.