How to Concatenate Multiple Cells With Space in Excel (7 Methods)

When dealing with Microsoft Excel, we occasionally need to concatenate multiple cells with their values using Excel formulas. We can easily concatenate multiple cells with space in Excel by using formulas. To concatenate multiple cells with space in Excel, we can use the CONCATENATE function, Ampersand(&) Symbol, TEXTJOIN, TRANSPOSE Functions, CHAR formula, and VBA Macros also.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

7 Suitable Ways to Concatenate Multiple Cells With Space in Excel

Let’s assume a scenario where we have information about the First Name, Middle Name, and Last Name of the employees of the Armani group in Columns B, C, and D respectively. In our dataset, we’ll concatenate these cells in Column E by using Excel formulas. Here’s an overview of the dataset of our today’s task.

1. Use the CONCATENATE Function to Concatenate Multiple Cells With Space in Excel

Here we’ll learn how to concatenate multiple cells with space by using the CONCATENATE function. This is the easiest and most time-saving function to concatenate multiple cells with space. Let’s follow the instructions below to learn!

Steps:

• First, select cell E5.

• After selecting cell E5, type the CONCATENATE function in the Formula Bar. The function is,

• Hence, press Enter on your keyboard, and you will get Shaun Aijack Lee as the output of the function.

• Then drag the Fill Handle to do the same for the rest of the employees.

Read More: How to Concatenate in Excel (3 Suitable Ways)

2. Perform the CONCATENATE and TRANSPOSE Functions to Concatenate Multiple Cells With Space in Excel

In this method, we will apply the TRANSPOSE, and CONCATENATE Functions to concatenate multiple cells with space in Excel. Let’s follow the steps below to learn!

Steps:

• Firstly, we will apply the TRANSPOSE function to transpose rows 4 to 6 into columns. For that select cell E5.

• After that, type the TRANSPOSE function in the Formula Bar. The TRANSPOSE function is,

• After typing the TRANSPOSE function in the Formula Bar, simply press F9 on your keyboard. Now, F9 converts the function into a value with a curly bracket.

• Hence, delete the curly brackets from the right and left most sides, and write down the CONCATENATE before “Samuel “,”Johnson “,”Taylor ” with parenthesis according to our screenshot that has been given below.

• At last, press Enter on your keyboard, and you will get Samuel Johnson Taylor as the output of the function.

Read More: Opposite of Concatenate in Excel (4 Options)

3. Apply the Ampersand(&) Symbol to Concatenate Multiple Cells With Space in Excel

Ampersand Symbol is the most useful symbol to combine cells in Excel. This symbol is widely used in Excel to concatenate cells. In this method, we will learn how to apply the ampersand symbol to concatenate multiple cells with space in Excel. Please follow the steps below.

Step 1:

• First of all, select cell E5 to concatenate cells B5, C5, and D5 with space.

• After selecting cell E5, type the formula in the Formula Bar. The formula which is typing in the Formula Bar is,

• Further then, simply press Enter on your keyboard and you will get Shaun Aijack Lee in cell E5 as the return of the formula.

Step 2:

• Further, drag the Fill Handle to apply the same formula for the rest of the employees.

Read More: How to Concatenate Multiple Cells in Excel (8 Quick Approaches)

Similar Readings:

• How to Concatenate Date That Doesn’t Become Number in Excel (5 Ways)
• Carriage Return in Excel Formula to Concatenate (6 Examples)
• Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)
• How to Bold Text in Concatenate Formula in Excel (2 Methods)
• Concatenate Rows in Excel (11 Methods)

4. Insert the CHAR Function to Concatenate Multiple Cells With Space in Excel

In this method, we will learn the most interesting formula named the CHAR function to concatenate cells with space in Excel. To concatenate cells with space in Excel by using the CHAR function, let’s follow the steps below.

Step 1:

• First of all, select cell E5.

• Hence, type the CHAR function in the Formula Bar. The CHAR function in the Formula Bar is,

• Where CHAR(32) returns space.

• After that, press Enter on your keyboard and you will get Shaun Aijack Lee in cell E5 as the output of the function.

Step 2:

• Then, place your cursor Bottom-Right on cell E5, an Autofill Sign pops up, and drag it downward.

• After completing the above process, we will get the desired output in column E.

Related Content: VBA to Concatenate Range with Separator in Excel (3 Ways)

5. Apply the TEXT and TODAY Functions to Concatenate Multiple Cells With Space in Excel

Here, we will apply the TEXT and the TODAY functions to combine cells in Excel. Let’s follow the instructions below to learn!

Steps:

• First, select cell B5.

• After that, type the TODAY function in the Formula Bar. The formula is in the Formula Bar,

• Now, press Enter on your keyboard and you will get 2/28/2022 as the return of that function.
• After completing the above process, again select cell C5.

• In cell C5, type a new formula. The formula is,

• Again, press Enter on your keyboard and you will get Today is 44620 which is a long number without a date formatting as the return of that function.

• To give the number into a format select a new cell D5.

• After selecting cell D5, again, type a new formula with double quotes in the Formula Bar. The formula is,

• Where TODAY() returns the current date.

• After completing the above process, again press Enter on your keyboard and you will get your desired output which has been given in the below screenshot.

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)

6. Run a VBA Code to Concatenate Multiple Cells With Space in Excel

In this method, we will run a VBA Code to concatenate multiple cells with space. Let’s follow the instructions below to learn!

Step 1:

• Firstly, from your Developer ribbon, go to,

Developer → Visual Basic

• After that, a window named Microsoft Visual Basic Applications – Concatenate Cells with Space will appear in front of you.

• Hence, from the Insert option, go to,

Insert → Module

Step 2:

• Further, write down the below VBA code in the Concatenate Cells with Space module.

Function ConcatenateR(WorkR As Range, Optional sign As String = " ") As String
Dim R As Range
Dim OutStr As String
For Each R In WorkR
            If R.Text <> " " Then
            OutStr = OutStr & R.Text & sign
            End If
Next
ConcatenateR = Left(OutStr, Len(OutStr) - 1)
End Function

• While completing to type the VBA code in that module, then, run the code. To do that, go to,

Run → Run Sub/User Form

Step 3:

• Now, go back to your worksheet and type the ConcatenateR formula in cell E5. The ConcatenateR formula is,

• After that, press Enter on your keyboard and you will get Shaun Aijack Lee in cell E5 as the output of the user-defined ConcatenateR function.

• Similarly, autoFill the ConcatenateR formula to the entire column E to get your desired output that has been given in the below screenshot.

Read More: How to Concatenate String and Variable in Excel VBA (A Detailed Analysis)

7. Apply the TEXTJOIN Function to Concatenate Multiple Cells With Space in Excel

After learning the CONCATENATE function, Ampersand symbol method, CHAR function, TEXT, and TODAY formula, we’ll learn to concatenate multiple cells with space in Excel by using the TEXTJOIN function. To concatenate multiple cells in Excel by using the TEXTJOIN function, follow the instructions below to learn!

Step 1:

• First, select cell E5, where we will type the TEXTJOIN function.

• After selecting cell E5, type the TEXTJOIN function in the Formula Bar. The formula in the Formula Bar is,

• While completing to type the function in the Formula Bar, simply press Enter on your keyboard and you’ll get Shaun Aijack Lee as the return of the TEXTJOIN function.

Step 2:

• Now, place your cursor on the bottom-right of cell E5, and instantly an autoFill sign will appear in front of you. Drag the autoFill sign downward.

• After completing the above process, you will be able to get your desired output.

Read More: Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)

Things to Remember

👉 you can only use the TEXTJOIN function in Excel 2019 or later including Microsoft 365.

Conclusion

I hope all of the suitable methods mentioned above to concatenate multiple cells with space will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)
• Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)
• Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
• How to Concatenate and Keep Currency Format in Excel (3 Methods)
• Add Leading Zeros in Excel by CONCATENATE Operation