How to Concatenate with Space in Excel (3 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

Concatenating in Excel is one of the most useful tools available to Excel users. It allows you to join two or more cells together to form a unified value. This can be particularly useful for combining data from multiple sources into a single value. In this article, I am going to explain 3 suitable ways on the topic of how to concatenate with space in Excel. I hope it will be helpful for you if you are looking for a similar sort of thing.


How to Concatenate with Space in Excel: 3 Suitable Ways

To concatenate with space, I am going to discuss 3 suitable ways in the following section. For more clarification, I am going to use a dataset with the First Name, Middle Name, and Last Name columns. We will apply the CONCATENATE, TEXTJOIN functions, and Ampersand (&) symbol as well to concatenate cells with space, Here’s an overview of the dataset for our today’s task.

Concatenate with Space

Read More: How to Concatenate with Delimiter in Excel


1. Use Ampersand(&) Symbol to Concatenate with Space

The simplest way to concatenate with space is to use the Ampersand (&) symbol. In the following section, we will use the Ampersand (&) symbol to concatenate all the names into a single cell with spaces.

Steps:

  • Select a cell (i.e. E5) first.
  • Next, input the following formula in that cell.
=B5&" "&C5&" "&D5

Use Ampersand(&) Symbol to Concatenate with Space

  • Hence, press ENTER to have the output.

  • Afterward, use Fill Handle to AutoFill the rest of the cells in column E.

Concatenate with Space

Read More: How to Concatenate Apostrophe in Excel


2. Apply the CONCATENATE Function to Concatenate with Space

You can perform a similar task using the CONCATENATE function in Excel. Just follow the steps mentioned below in order.

Steps:

  • Input the following formula in a selected cell.
=CONCATENATE(B5," ",C5," ",D5)

Apply CONCATENATE Function to Concatenate with Space

  • Next, hit the ENTER button.

  • Then, you can drag the Fill Handle to copy the formula for the rest cells in column E.

Concatenate with Space

Read More: How to Bold Text in Concatenate Formula in Excel


3. Employ the TEXTJOIN Function to Concatenate with Space

When you have a large range of cells to join, using the Ampersand (&) symbol or the CONCATENATE function may be a bit troublesome. In these cases, you can use the TEXTJOIN function of Excel. Let’s follow the instructions below to learn!

Steps:

  • First of all, pick a cell (i.e. E5) to have the concatenated with space data.
  • Followingly, input the following formula in that cell.
=TEXTJOIN(" ",TRUE,B5:D5)

Employ TEXTJOIN Function to Concatenate with Space

  • After that, press ENTER to have the output.

  • Further, AutoFill the TEXTJOIN function to the rest of the cells in column E.

Concatenate with Space

Notes

The TEXTJOIN function is only available in Office 365.

Read More: How to Concatenate Cells but Keep Text Formatting in Excel


Download Practice Workbook

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


Conclusion

At the end of this article, I like to add that I have tried to explain 3 suitable ways on the topic of how to concatenate with space in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

4 Comments
  1. Hi Rifat

    Is it possible to TextJoin non-consecutive and out-of-order cells, EG A4:A6 plus A1?

    Thanks // Dominic

  2. Hi DOMINIC,
    Yes, it’s possible to join non-consecutive cells using the TEXTJOIN Function in Excel.
    Let me show you how you can do it.
    Let’s assume this is your dataset.

    image-1

    Then, in A8, apply the following formula
    =TEXTJOIN(",",TRUE,A4:A6,A1)

    image-2

    The delimiter is a comma (,)
    TRUE indicates that you are going to ignore empty values.

    Then, press ENTER to get the output.

    image-3

    I hope it helps.
    Have a good day.

  3. Hi Rifat,

    Thank you for your easy-to-follow instructions that make life easy to use some of Microsoft Excel functions.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo