To demonstrate how to use the concatenate function, we will use a dataset with the First Name, Middle Name, and Last Name columns. We will apply the CONCATENATE, TEXTJOIN functions, and Ampersand (&) symbol.
Read More: How to Concatenate with Delimiter in Excel
Method 1 – Use Ampersand(&) Symbol to Concatenate with Space
The simplest way to concatenate all the names into a single cell and add space between each name is to use the Ampersand (&) symbol. Here is how to do it.
Steps:
- Select a cell (i.e., E5) first.
- Apply the following formula to that cell:
=B5&" "&C5&" "&D5
- Press ENTER to see the output.
- Use Fill Handle to AutoFill the remaining cells in column E.
Read More: How to Concatenate Apostrophe in Excel
Method 2 – Use the CONCATENATE Function to Concatenate with Space
The second way to concatenate with space in Excel is by using the CONCATENATE function. Here are the steps.
Steps:
- Enter the following formula in a selected cell.
=CONCATENATE(B5," ",C5," ",D5)
- Hit the ENTER button.
- Drag the Fill Handle to copy the formula to the remaining cells in column E.
Read More: How to Bold Text in Concatenate Formula in Excel
Method 3 – Employ the TEXTJOIN Function to Concatenate with Space
When you need to combine a large number of cells, using the Ampersand (&) symbol or the CONCATENATE function may be a bit troublesome. Instead, you can use the TEXTJOIN function. Follow the steps below to learn how.
Steps:
- Select your destination cell (i.e., E5).
- Enter the following formula in that cell:
=TEXTJOIN(" ",TRUE,B5:D5)
- Press ENTER to see the output.
- AutoFill the TEXTJOIN function to the remaining cells in column E.
Note: The TEXTJOIN function is only available in Office 365.
Read More: How to Concatenate Cells but Keep Text Formatting in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Hi Rifat
Is it possible to TextJoin non-consecutive and out-of-order cells, EG A4:A6 plus A1?
Thanks // Dominic
Hi DOMINIC,
Yes, it’s possible to join non-consecutive cells using the TEXTJOIN Function in Excel.
Let me show you how you can do it.
Let’s assume this is your dataset.
Then, in A8, apply the following formula
=TEXTJOIN(",",TRUE,A4:A6,A1)The delimiter is a comma (,)
TRUE indicates that you are going to ignore empty values.
Then, press ENTER to get the output.
I hope it helps.
Have a good day.
Hi Rifat,
Thank you for your easy-to-follow instructions that make life easy to use some of Microsoft Excel functions.
Dear Isaac,
You are most welcome.
Regards
ExcelDemy