Joining text with & and TEXTJOIN practice exercises
Put text from several cells together into one, with spaces or other separators between the parts, and skip the cells that are empty.
How to join text in Excel
Excel has three ways to put text together. The & operator glues values together, as in A2&" "&B2. CONCAT does the same for a whole range. TEXTJOIN takes a separator, then TRUE or FALSE for whether to skip empty cells, and then the cells you want to join. CONCAT and TEXTJOIN need Excel 2019 or Microsoft 365, while & works in every version.
=A2&" "&B2 joins "Anna" and "Berg" into "Anna Berg". The space is a text in quotes. =TEXTJOIN(", ",TRUE,A2:D2) joins an address spread over four cells with a comma and a space, and it skips an empty Suite cell so you don't get two commas in a row.
Numbers and dates lose their formatting when you join them. If B2 is a date, the joined text shows a serial number like 45366 instead of Mar 15, 2024. Wrap it in TEXT to control the look: =A2&" "&TEXT(B2,"mmm d, yyyy"). The exercises below cover joining first and last name with &, building a lowercase email address, joining an address with TEXTJOIN and building an ID from LEFT, UPPER and &.
- 1Join first and last name with &In C2, join the first name in A2 and the last name in B2 with a space between them. Use the & operator.
- 2Build an email addressPremiumIn C2, build the email address [email protected] in small letters from A2 and B2. Join the parts with & and wrap the whole thing in LOWER.
- 3Join an address with TEXTJOINPremiumIn E2, join A2:D2 into one address with a comma and a space between the parts. Use TEXTJOIN and skip the empty Suite cell.
- 4Build an ID from a department and a numberPremiumBadge IDs are the first 3 letters of the department in capitals, a dash and the employee number, like MAR-1042. In C2, build the ID with LEFT, UPPER and &.
Functions in this topic
Read the guide
Questions
What is the difference between CONCATENATE, CONCAT and TEXTJOIN?
CONCATENATE is the old function and accepts only single cells, not ranges. CONCAT replaces it and accepts ranges, but still has no separator. TEXTJOIN adds a separator and can skip empty cells. Microsoft keeps CONCATENATE only for compatibility, so use CONCAT or TEXTJOIN in new files, or just the & operator.
How do I join a column of text into one cell with commas?
Use TEXTJOIN with the whole range: =TEXTJOIN(", ",TRUE,A2:A20). The TRUE skips empty cells, so you don't get stray commas. In an Excel version without TEXTJOIN, you have to chain the cells one by one with &, which gets long fast. Flash Fill with Ctrl+E can also build a few examples by pattern.
How do I add a line break when joining text in Excel?
Put CHAR(10) between the parts: =A2&CHAR(10)&B2. Then turn on Wrap Text for the cell on the Home tab, or the text still shows on one line. With TEXTJOIN you can use CHAR(10) as the separator: =TEXTJOIN(CHAR(10),TRUE,A2:D2).