One of the most important features of Excel is to concatenate, that is to collect and maintain data from various places in a worksheet. Today I will be showing how you can concatenate with space in Excel.
Download Practice Workbook
How to Concatenate with Space in Excel
Here we’ve got a data set with the First Name, Middle Name, and Last Name of some employees with a company named Johnson Group.
Our objective today is to concatenate the different sections of the names into a single cell separated by spaces.
1. Using Ampersand(&) Symbol
We can use the Ampersand (&) symbol to concatenate all the names into a single cell with spaces.
The formula to concatenate the names of the first employee with spaces will be:
=B4&" "&C4&" "&D4
Then you can drag the Fill Handle to copy the formula for the rest of the employees.
2. Using the CONCATENATE Function
You can perform a similar task using the CONCATENATE function in Excel.
Insert all the values that you want to concatenate as the arguments of the function.
Therefore, to concatenate the values in cells B4, C4, and D4 with spaces will be:
=CONCATENATE(B4," ",C4," ",D4)
Then drag the Fill Handle to do the same for the rest of the employees.
3. Using the TEXTJOIN Function
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.
Note: The TEXTJOIN function is only available in Office 365.
The Syntax of the TEXTJOIN function is:
The TEXTJOIN formula to join the cells B4, C4, and D4 with spaces will be:
Then drag the Fill Handle to copy the formula for the rest of the employees.
Explanation of the Formula
- The first argument of the TEXTJOIN function is the delimiter (By which the texts are separated). This is a space (” “) in this case.
If you want, you can use anything else as the delimiter. It can be any string.
- The second argument indicates whether we want to ignore the empty cells within the given range or not.
We set it as TRUE. That means, if there are one or more empty cells within our given range, we want to ignore those.
- The third argument is the range that we want to concatenate. It is B4:D4 for the first employee.
Using these methods, we can concatenate two or more cells into one single cell with spaces. Do you have any questions? Feel free to ask us.