In order to get the Full Name at a time from separate columns, we need to combine those cells. Here, we are going to learn some easy and smooth approaches to combine names in Excel with Space.
For clarification, we are going to use a dataset containing the First Name and Last Name of Hollywood actors. We will combine the First Name and Last Name cells to have the Full Name of those actors.
1. Applying Ampersand (&) Symbol to Combine Names in Excel with Space
The most common and easy way to combine names in Excel with space is to apply the Ampersand (&) symbol.
Steps :
- Firstly, We have to select the cell where we want to get the desired result. Here, I selected D5 where I want to get the Full Name.
- Select the Names that we want to combine with space. Here, I selected B5 and C5.
- Then, insert the following Formula:
=B5&" "&C5Here, the Ampersand (&) symbol is used to combine the cells along with a space.
- Press ENTER.
We will be able to see the Full Name in the selected cell along with space as a separator.
- Finally, Use Fill Handle to AutoFill till the required cells.
Read More: How to Concatenate Names in Excel
2. Using Excel CONCATENATE Function to Combine Names with Space
The CONCATENATE function is another effective way to combine names in Excel with space.
Steps :
- Select the cell where we want to get the desired result. Here, I selected D5 where I want to get the Full Name.
- Select the Names that we want to combine with space. Here, I selected B5 and C5.
- The Formula that needs to use here is:
=CONCATENATE(B5," ",C5)Here, CONCATENATE is used to combine the cells along with the space.
- Press ENTER and the names will be combined.
- Lastly, use Fill Handle to AutoFill the rests.
Note: CONCATENATE Function is applicable only for cells, not for range.
3. Inserting CONCAT Function to Joining Names with Space in Excel
We can have combined names with space for a range using CONCAT Function which is missing in CONCATENATE Function.
Steps :
- Select the cell where the combined name is expected. Here, I selected D5 where I want to get the Full Name.
- Select the Names that we want to combine with space. Here, I selected B5 and C5.
- The Formula that we used here is:
=CONCAT(B5," ",C5)Here, CONCAT is used to combine the cells along with the space.
- Press ENTER and the names will be combined.
- Use Fill Handle to AutoFill till the last.
Read More: How to Combine Name and Date in Excel
4. Executing Flash Fill Command to Concatenate Names with Space
The Flash Fill Command execution is another simple way to combine names with space.
Steps :
- First of all, I have to input the Format in which pattern I want to get my results. Here, I declared in D5 cell how I wanted the names to be combined as Full Name which is Brad Pitt.
Select the cell from where you want the Flash Fill
- Then, go according to the sequence:
- Home—> Editing —> Fill —> Flash Fill
Alternatively, from the Data tab —-> select Flash Fill.
- Press ENTER and the rest will be Filled
5. Combining Names in Excel with Space Using TEXTJOIN Function
We can also adopt the TEXTJOIN Function to combine names with space.
Steps :
- Select the cell where I want to execute the TEXTJOIN Function. Here, I selected the D5
- Now, I use the following Formula to combine cell B5 and C5:
=TEXTJOIN(" ",TRUE,B5,C5)Here, we are using space as our delimiter then we used TRUE to ignore_empty. Next, s4elected the cells B5 and C5 as text1 & text2 to combine the names with space.
- Press ENTER and the names will be combined.
- Use Fill Handle to AutoFill the next.
Read More: How to Combine Two Formulas in Excel
6. Using Excel Power Query to Combine Names with Space
Power Query is the smartest way to combine names in Excel with space.
Steps :
- Select any cell from the Table. Here, I selected the C5 cell from the Table.
- Followingly, select From Table/Range from the Data
- Then, a dialog box will appear then select the cell range where you want to apply Power Query.
- I selected the range B4:C14.
- Next, mark the box titled My table has headers and press OK.
A new Power Query window will appear containing the selected columns.
- Select both columns using the CTRL key.
- Then, Right Click on the mouse. A Context Menu will appear. From there, select Merge Columns.
Here, a dialog box will appear.
- Select Space from the Separator and give a name to the New column which will contain the result. Here, I have given the New column name “Full Name”.
- Press OK.
Then, we will be able to see the column containing the combined names.
- Next, from the File, select Close and Load.
Then, we will be able to see the results in a new Sheet of our existing workbook.
Download Practice Workbook
Conclusion
In this article, I have tried to show 6 smart and efficient ways to combine names in Excel with space. I hope it will be helpful for Excel users. Comment below for any further pieces of information.
