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.
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.
1. Using Ampersand Operator to Merge Two Columns in Excel With a Space
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.
Read More: How to Merge Columns in Excel
2. Applying CONCAT Function to Merge Two Columns With a Space
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.
Read More: How to Merge Two Columns in Excel Without Losing Data
3. Merging Two Columns With a Space Using Excel 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.
Read More: How to Merge Two Columns in Excel and Remove Duplicates
4. Using TEXTJOIN Function to Merge Two Columns in Excel With a Space
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.
5. Applying VBA Code to Merge Two Columns With a Space
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.
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 tab.
- As a result, you will find that all of your data from the two columns are merged with a space between them.
Read More: Merge Two Columns in Excel with First Name and Last Name
Download Practice Workbook
You can download the practice workbook here.
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.
