Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Merge Two Columns in Excel With a Space

Users often need to merge data from two columns in Excel. Often they merge data without space. That creates obscurity in the presentation of the data. So, in this article, we will discuss 5 methods of how to merge two columns in Excel with a space.


Download Practice Workbook

You can download the practice workbook here.


5 Effective Ways to Merge Two Columns in Excel With a Space

In this article, we will talk about 5 handy ways to merge two columns in excel with a space. Firstly, we will use the Ampersand operator to merge data in two columns. Secondly, we will use the CONCAT function. Thirdly, we will opt for the CHAR function to combine the cells. Then, we will go for the TEXTJOIN function to do the task. Finally, we will resort to a VBA code to merge two columns in Excel.

how to merge two columns in excel with a space


1. Using Ampersand Operator

The Ampersand operator is widely used to combine texts in Excel. In this method, we will merge two values in two columns in excel with a space between them using this operator.

Steps:

  • Firstly, select the D5 cell and write the following formula,
=B5&" " & C5
  • Then, hit Enter.

  • Consequently, you will find that your data is merged with a space.
  • Then, move the cursor down to fill the rest of the data cells.

using ampersand operator to merge two columns in excel with a space

Read More: How to Merge Columns in Excel (4 Ways)


2. Applying CONCAT Function

The CONCAT function is Excel’s default function to concatenate texts. In this instance, we will use the function to merge two values in two columns.

Steps:

  • To begin with, choose the D5 cell and write the following formula,
=CONCAT(B5," ",C5)
  • Then, press Enter.

  • Consequently, you will find that your data is merged.
  • Then, lower the cursor to the last data cell.
  • Excel will automatically fill the cells according to the formula.

using concat function to merge two columns in excel with a space

Read More: How to Merge Two Columns in Excel Without Losing Data


3. Applying CHAR Function

The CHAR function takes the ASCII code of a character and displays it. 32 is the ASCII code for space. In this method, we will insert 32 as the argument of the CHAR function and create a space between two values that we will be merging.

Steps:

  • To start with, select the D5 cell and write the following formula:
=B5&CHAR(32)&C5
  • Then, press the Enter button.

  • As a result, you will observe that your data is merged.
  • Then, move the cursor down to the last data cell.
  • Excel will automatically fill the rest of the cells according to the formula.

applying char function to merge columns in excel with a space

Read More: How to Merge Two Columns in Excel and Remove Duplicates


4. Using TEXTJOIN Function

The TEXTJOIN function combines text from different cells and introduces a user-specified delimiter between them. In this method, we will use the TEXTJOIN function to merge texts and give space as a delimiter.

Steps:

  • To begin with, select the D5 cell and write the formula below in the cell:
=TEXTJOIN(" ", TRUE, B5:C5)
  • Then, press the Enter key.

  • As a result, you will observe that the data from the two cells are merged.
  • Then, drag the cursor down to the last cell.
  • The rest of the data cells will be filled automatically.

using textjoin function to merge two columns in excel with a space

Read More: How to Join Two Columns in Excel (5 Easy Methods)


5. Applying VBA Code

In the final method, we will resort to a VBA code to merge two columns in Excel with a space.

Steps:

  • Firstly, select the dataset.
  • Then, go to the Developer tab in the ribbon.
  • From there, select the Visual Basic
  • Consequently, the Visual Basic window will be opened.

  • After that, in the Visual Basic tab, click on Insert.
  • Then, select the Module
  • Consequently, a coding module will appear.

  • In the coding module, write down the following code.
  • Then, save the code.

typing vba code to merge two columns in excel with a space

Sub MergeColumns()
' declaring variable type
    Dim x As Long, xrow As Long
    With Sheets("VBA Code")
        xrow = .Range("B" & Rows.Count).End(xlUp).Row
'running for loop
        For x = 5 To xrow
            Sheets("VBA Code").Cells(x, 4) = .Cells(x, 2) & " " & .Cells(x, 3)
        Next x
    End With
End Sub
  • Then, run the code from the Run

running the vba code to merge two columns in excel with a space

  • As a result, you will find that all of your data from the two columns are merged with a space between them.

applying vba code to merge two columns in excel with a space

Read More: Merge Two Columns in Excel with First Name and Last Name


Conclusion

In this article, we have talked about some handy solutions to merge two columns in excel with a space. So, these methods will help the users to combine texts properly and also present them appropriately.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo