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
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:
- In the above formulas, "A2, B2, C2" are the cells that you want to concatenate. If there are more cells that need to be combined, you can simply add their references to the formula, such as:
=CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2) - If you want to use other separator after concertation, for instance, use comma to separate, you can change the formula to this:
=CONCATENATE(A2,",",B2,",",C2)
● 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 )
- " " (Space as a delimiter):
The formula uses a single space (" ") as the delimiter to separate the values being joined. You can change it to other delimiter, such as , - / etc. - TRUE (Ignore empty cells):
The TRUE parameter ensures that any empty cells within the range A2:C2 are ignored, so no extra spaces are added for blank values. - A2:C2 (Range to concatenate):
This is the range of cells whose values will be combined. The values in cells A2, B2, and C2 will be joined together.
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.
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy permanently free AI features! Download now!
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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in