A customer list has first name, surname and second surname in three columns and you need a single one for the shipping label. The people with no second surname come out with a stray space at the end.
How to use Concatenate Formula step by step
1. Pick the method
The & operator works in every version, CONCAT and TEXTJOIN need Excel 2019 or 365, and CONCATENATE lingers for compatibility.
2. List the cells and the joining text
Separate with commas what you want to join. Anything that is not a reference is treated as fixed text in quotation marks.
3. Copy and drag
Paste the formula in the first row and drag the fill handle to the end of the column.
The Step Count: Traditional Friction vs. KorexTools
Concatenating by hand with quotes and spaces (4 steps)
- Type =A2&" "&B2&" "&C2.
- Spot the extra space when C2 is empty.
- Add an IF for that cell and repeat it in every column.
- Fix the quotes when Excel complains.
With the KorexTools “Concatenate Formula” builder (3 steps)
- Pick the method.
- List the cells and the delimiter.
- Copy and drag the formula.
Concatenating in Excel: why there are four ways and why TEXTJOIN usually wins
CONCATENATE is the function everyone remembers, yet Microsoft kept it only for compatibility once CONCAT arrived. The & operator does the same with less typing, and TEXTJOIN adds what was missing: one delimiter for the whole list and the option to skip empty cells, which prevents double spaces when a middle name is optional.
The builder writes all four variants with your cells. Anything that does not look like a reference is treated as text and wrapped in quotation marks, so you can mix =A2, a hyphen and =C2 without wrestling with quotes. The separator and language apply to the finished formula, including the TRUE that TEXTJOIN carries inside.
How to choose between &, CONCAT and TEXTJOIN
- •Two or three cells with a space between: the & operator is the shortest.
- •A long list or a whole range: TEXTJOIN takes A2:F2 in one go.
- •A workbook that will open in Excel 2016 or earlier: & or CONCATENATE, since CONCAT and TEXTJOIN will answer #NAME?.
Same result, four formulas
| Method | Formula | Minimum version |
|---|---|---|
| & operator | =A2&" "&B2 | Any |
| CONCATENATE | =CONCATENATE(A2," ",B2) | Any |
| TEXTJOIN | =TEXTJOIN(" ",TRUE,A2:B2) | Excel 2019 |
Common Mistakes and How to Avoid Them
- ⚠️Joining a date and seeing 45292 instead of 01/01/2024.
- ⚠️Putting the space inside the formula without quotation marks.
Frequently Asked Questions
What do I type in "Cells or texts to join"?
References separated by commas, such as A2, B2, C2. A loose word, such as Mr., is added in quotation marks as fixed text.
How do I join cells without double spaces when one is empty?
Use TEXTJOIN with a delimiter. With TRUE as the second argument it ignores empty cells and leaves no stray spaces.
Why does CONCAT give me #NAME??
Your Excel is older than 2019. Switch to the & operator or to CONCATENATE, which work in every version.
What I would do if I were in your shoes
«Switch to TEXTJOIN the moment the list passes three items: next month’s you will be glad not to read a string of ampersands.» — DataKorex Engineering Team.