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.

**Approach 1**

**1. Using CONCATENATE Function**

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.

**=CONCATENATE(B6,” “,C6,” “,D6)**

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**

**Approach 2**

**2. Applying Ampersand in Formula**

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.

**=B6&” “&C6&” “&D6**

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**

**Approach 3**

**3. Combining Ampersand with CHAR Function**

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.

**=B6&CHAR(32)&C6&CHAR(32)&D6**

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**

**Approach 4**

**4. Employing TRANSPOSE and CONCATENATE Functions**

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

**=TRANSPOSE(C5:C7)&” “**

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

**={“John “,”Klerk “,”Marshman “}**

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

**=CONCATENATE(“John “,”Klerk “,”Marshman “)**

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

**Approach 5**

**5. Using TODAY and TEXT Functions**

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

**=TODAY()**

This will return the current date in cell **B6**.

- Then, in cell
**C6**insert the formula.

**=”Today is “&TODAY()**

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

**=”Today is “&TEXT(TODAY(),”mm-dd-yy”)**

- Lastly, we will have our desired outcome.

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

**Approach 6**

**6. Applying TEXTJOIN Function**

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.

**=TEXTJOIN(” “,TRUE,B6:D6)**

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**

**Approach 7**

**7. Embedding VBA**

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

**=ConcatenateA(B6:D6)**

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

**=CONCATENATE(“(“,B6,”)”)**

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