How to concatenate cells and add space between words in Excel?

In Excel, combining the contents of multiple cells into a single cell is a common task, often used for creating readable text or formatted data. However, when you concatenate cells using functions like CONCATENATE or the & operator, the resulting text is often merged without any spacing. To ensure clarity and readability, you may want to insert spaces between the values as they are joined. This can be achieved using a few simple techniques, such as modifying the formula or leveraging advanced functions like TEXTJOIN. Here's how you can effectively concatenate cells while adding spaces between words.

Concatenate multiple cells and add space or other delimiters between words with formulas

• With CONCATENATE function
• With TEXTJOIN function

Concatenate multiple cells and add space or other delimiters between words with Kutools for Excel

 Concatenate multiple cells and add space or other delimiters between words with formulas

Suppose you have the following three columns of data, and you need to concatenate their values with spaces between them to achieve the following result:

● Use the CONCATENATE function

The CONCATENATE function in Excel can be used to combine multiple cell values into a single cell. While the CONCATENATE function does not automatically include spaces or other delimiters between words, you can manually add them within the formula to achieve the desired formatting.

Here is the CONCATENATE formula to help you to solve this job. Please do as follows:

1. Enter the following formula into a blank cell besides your data, "E2", for instance, see screenshot:

=CONCATENATE(A2," ",B2," ",C2)

2. Then drag the fill handle down to the cells that you want to contain this formula, and the cells in the same row have been concatenated by space at once, see screenshot:

● Use the TEXTJOIN function

The TEXTJOIN function in Excel provides a simple and efficient way to concatenate multiple cells while adding spaces or other delimiters between the words. Unlike traditional methods like CONCATENATE or the & operator, TEXTJOIN allows you to specify a delimiter that will automatically be inserted between the values, making it especially useful for large datasets. Additionally, it can ignore empty cells, ensuring that the final result is clean and free of unnecessary separators.

Enter the TEXTJOIN formula as this, and then drag the fill handle down to fill this formula to other cells, see screenshot:

=TEXTJOIN(" ",TRUE, A2:C2 )

 Concatenate multiple cells and add space or other delimiters between words with Kutools for Excel

If there are multiple cells needed to be concatenated, the above formulas will be somewhat complex, here, I can introduce you a powerful tool- "Kutools for Excel", with its "Combine Rows, Columns or Cells without Losing Data" utility, you can quickly concatenate multiple cells from a row or a column into one cell with any specific delimiters.

1. Select the range of cells that you want to combine based on a row.

2. Click "Kutools" > "Merge & Split" > "Combine Rows, Columns or Cells without Losing Data", see screenshot:

3. In the popped out dialog box, please do the following operations:

(1.) Select one option that you need to combine the cells based on under "To combine selected cells according to following options", this example, I choose "Combine columns";

(2.) Then choose a separator that you want to separate the combined contents from the "Specify a separator";

(3.) At last, you can specify an option for dealing with the combined cells, you can keep or delete contents from combined cells, or you can also merge those combined cells.

4. After finishing the settings, please click "Ok" or "Apply" button, and all the selected cells have been concatenated into one cell based on rows, see screenshot:

With this "Combine" feature, you can also concatenate cells based on columns or merge all the cells into a single cell.

This guide offers straightforward methods to concatenate cell values in Excel while adding spaces or other delimiters, allowing you to choose the one that best suits your needs. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

 Demo: Concatenate multiple cell values and add space or other delimiters between words with Kutools for Excel

Related articles:

How to quickly combine text and date into same cell in Excel?

How to combine cells with line break in Excel?