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

excel concatenate multiple cells with space

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.

excel concatenate multiple cells with space


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.

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

=CONCATENATE(B5," ",C5," ",D5)

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

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

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

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

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.

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

  • After that, type the TRANSPOSE function in the Formula Bar. The TRANSPOSE function is,
=TRANSPOSE(C4:C6)&” “

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

  • 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.
=CONCATENATE("Samuel ","Johnson ","Taylor ")

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

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

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

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.

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

  • After selecting cell E5, type the formula in the Formula Bar. The formula which is typing in the Formula Bar is,
=B5&" "&C5&" "&D5

  • 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.

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

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


Similar Readings:


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.

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

  • Hence, type the CHAR function in the Formula Bar. The CHAR function in the Formula Bar is,
=B5&CHAR(32)&C5&CHAR(32)&D5
  • 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.

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

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.

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

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

  • 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.

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

  • In cell C5, type a new formula. The formula is,
= “Today is “&TODAY()

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

  • 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,
="Today is " &TEXT(TODAY(),"mm-dd-yy")
  • Where TODAY() returns the current date.

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

  • 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.

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

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

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

  • After that, a window named Microsoft Visual Basic ApplicationsConcatenate 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

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

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

Run → Run Sub/User Form

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

Step 3:

  • Now, go back to your worksheet and type the ConcatenateR formula in cell E5. The ConcatenateR formula is,
=ConcatenateR(B5:D5)

  • 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.

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

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

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

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.

Using the TEXTJOIN Function to Concatenate Multiple Cells With Space in Excel

  • After selecting cell E5, type the TEXTJOIN function in the Formula Bar. The formula in the Formula Bar is,
=TEXTJOIN(" ", TRUE, B5:D5)

  • 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.

Using the TEXTJOIN Function to Concatenate Multiple Cells With Space in Excel

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.

Using the TEXTJOIN Function to Concatenate Multiple Cells With Space in Excel

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo