7 Ways to Concatenate Multiple Cells with Space in Excel

Excel data can take various forms, including text, numbers, synonyms, etc. The CONCATENATE function in Excel combines the values of multiple cells into a single text string. However, we can also accomplish this using other formulas and functions. So, we will demonstrate 7 simple methods in this article to concatenate multiple number of cells containing space in Excel.

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Learn to Concatenate Multiple Cells with Space in Excel with These 7 Methods

First, let us become acquainted with our dataset, which will be used as an example throughout the article. Some students have given us their first, middle, and last names. Now we want to connect multiple cells containing space to form their full names. Hence, we will use this dataset to demonstrate 7 simple approaches to concatenate multiple number of cells containing space in Excel.

The method uses CONCATENATE function in order to concatenate multiple number of cells containing space in Excel. CONCATENATE helps to join two or more text strings into one string.

Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell where you want the full name. Here, we are selecting cell E6.
• Next, insert the formula.

Here, cells B6, C6, and D6 contain the text value, and “ ”  are the spaces we want between the texts. This CONCATENATE function joins all of these and returns the full name.

• Lastly, drag Fill Handle down, and thus you will have the desired result.

📕 Read More: 5 Easy Methods to Combine Rows into One Cell in Excel

This method applies Ampersand or “&” in order to concatenate multiple number of cells containing space in Excel.  Ampersand helps to join two or more text strings into one string.

Let us go through the steps now.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell E6.
• Next, insert the formula.

Here, cells B6, C6, and D6 contain the text value, and “ ”  are the spaces we want between the texts and ampersand joins all of these and returns the full name.

• Lastly, drag Fill Handle down, and thus you will have the desired result.

📕 Read More: 6 Ways to Concatenate Numbers with Leading Zeros in Excel

This method combines Ampersand or “&” and CHAR function to concatenate multiple number of cells containing space in Excel. CHAR function returns the number-specified character and converts code page numbers into characters using CHAR received from files on computers of a different type.

Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell E6.
• Next, insert the formula.

Here, cells B6, C6, and D6 contain the text value, CHAR(32)  returns the spaces we want between the texts, and ampersand joins all of these and returns the full name.

• Lastly, drag Fill Handle down, and thus you will have the desired result.

📕 Read More: 2 Ways to Bold Text in Concatenate Formula in Excel

This method employs TRANSPOSE and CONCATENATE functions in order to concatenate multiple number of cells containing space in Excel. TRANSPOSE function switches or rotates cells without creating duplicated data. On the other hand, CONCATENATE helps to join two or more text strings into one string.

We have a student’s first, middle, and last names in three rows. Now we want to concatenate all of them in a cell.

Let us go through the steps to apply these functions.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell C9.
• Next, insert the formula and press F9.

• Then, in formula bar, it will show the below formula.

• After that, manually delete curly brackets{}, and instead insert parenthesis() and the CONCATENATE.

• Otherwise, you can simply copy this formula and insert it into the cell where you want the outcome.

📕 Read More: 11 Easy Ways to Concatenate Rows in Excel

This method uses TODAY and TEXT functions in order to concatenate multiple number of cells containing space in Excel. Excel calculates the date and time using the serial number as the date-time code and TODAY function returns the current date serial number. On the other hand, you can format a number using the TEXT function to alter its appearance.

Let us go through the steps to apply these functions.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell B6.
• Next, insert the formula and press Enter.

This will return the current date in cell B6.

• Then, in cell C6 insert the formula.

Now, this will return a text string Today is 44937 where 44937 is the date-time code for 11th January. But we want the dating outcome in date format. So, to do that we will use TEXT function.

• Now, insert the formula inside cell D6.

• Lastly, we will have our desired outcome.

📕 Read More: 5 Examples to CONCATENATE If Date Becomes Number in Excel

This method uses TEXTJOIN function to concatenate multiple number of cells containing space in Excel. TEXTJOIN function joins text values from different ranges and strings using a delimiter specified between each value. This function will effectively combine the ranges, the delimiter being an empty text string. However, this function is only available in Microsoft Excel 2019 and above versions. So, for previous versions, please check out the other methods for the solution.

Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell E6.
• Next, insert the formula in the cell.

Here, cells B6:D6 contain the text value, “ ”  is the delimiter, and TRUE is for ignoring empty cells.

• Lastly, drag Fill Handle down, and thus you will have the desired result.

📕 Read More: 6 Ways to Concatenate with Carriage Return in Excel Formula

This method will demonstrate concatenating multiple cells containing space in Excel using Microsoft Visual Basic Application (VBA).

So, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• At first, go to the Developer tab and select Visual Basic from the code group.

• Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module option.

• Enter the code and save it.

Function ConcatenateA(WorkA As Range, Optional sign As String = " ") As String

Dim A As Range

Dim AStr As String

For Each A In WorkA

            If A.Text <> " " Then

            AStr = AStr & A.Text & sign

            End If

Next

ConcatenateA = Left(AStr, Len(AStr) - 1)

End Function

• Now, insert the formula inside cell E6.

• Lastly, drag Fill Handle down, and thus you will have the desired result.

How to Concatenate with Delimiter in Excel

Delimiters are characters dividing texts into different segments. Comma (,), semicolon (;), quote (“, “), brace (), pipe (|), and slash (/) are  all common delimiters. A program uses a predefined character to delimit each data when storing sequential or tabular data.

This method uses CONCATENATE function in order to concatenate with delimiter in Excel. CONCATENATE helps to join two or more text strings and delimiters into one string.

Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select C6.
• Next, insert the formula.

Here, cell B6 contains the text value, and “(” and “)”  are the delimiters that we want on both sides of the text, and CONCATENATE function joins all of these and returns the full name inside the braces.

• Lastly, drag Fill Handle down, and thus you will have the desired result.

📕 Read More: 5 Easy Ways to Add Comma in Excel to Concatenate Cells

📄 Important Notes

🖊️   The updated version of CONCATENATE function is CONCAT function. So, for Excel 2019 or a later version, we suggest you use CONCAT function instead of CONCATENATE.

🖊️  TEXTJOIN function is only available in Microsoft Excel 2019 and above versions. So, for previous versions, please check out the other methods for the solution.

📝 Takeaways from This Article

📌  This article demonstrated 7 approaches to concatenating multiple cells containing space in Excel.

📌   In the first method, we used the CONCATENATE function to join the text strings in Excel.

📌  Then, we applied ampersand to concatenate multiple number of cells containing space in Excel.

📌  Next, we combined ampersand and CHAR functions.

📌  After that, we employed TRANSPOSE and CONCATENATE functions.

📌  Next, we used TODAY and TEXT functions.

📌  Then,  we employed TEXTJOIN function to concatenate multiple amount of cells containing space in Excel.

📌  Finally, we embedded a VBA code to concatenate multiple amount of cells containing space in Excel.

Conclusion

This article has demonstrated 7 approaches to concatenating multiple cells containing space in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

Related Article

• 2 Steps to Concatenate Multiple Results with INDEX-MATCH in Excel