How to combine cells in Excel: Easy Guide

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

How to combine cells with the & function

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 a comma

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.

how to combine cells with line break in Excel

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

combine cells in Excel

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”

combine cells in Excel using VBA

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

How to combine cells in Excel with formatting

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.

How to combine cells with names in Excel

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.

Leave a Reply