Course / Working with Text

Joining text

Combine names and labels with &, CONCAT and TEXTJOIN. Intermediate · 4 min · &, CONCAT, TEXTJOIN

Spreadsheets often keep pieces of text in separate columns: first name, last name, city. Joining them back together is one of the most common text jobs, and Excel gives you three tools for it:

• The & operator glues values together: =A2&" "&C2
• CONCAT does the same job as a function: =CONCAT(C2, ", ", A2)
• TEXTJOIN puts a delimiter between every item, and can skip empty cells

Any text you type into a formula, even a single space, goes inside "double quotes".

Steps

1. In D2, build a display name from the first and last names with a space in between: =A2&" "&C2. Then copy it down to D7.

2. Staff directories often list people surname first. In E2:E7, use CONCAT to build names like Moreau, Ines.

3. Some people have a middle name and some don't. In F2:F7, build each full name with TEXTJOIN, using a space as the delimiter and TRUE to skip empty cells: =TEXTJOIN(" ", TRUE, A2:C2)

4. TEXTJOIN can also squeeze a whole column into one cell. In B9, list everyone's first name, separated by a comma and a space: Ines, Tomasz, …

Remember

• & joins values: =A2&" "&C2.
• CONCAT joins a list of values or ranges.
• TEXTJOIN(delimiter, TRUE, range) adds a delimiter between items and skips blanks.
• Text inside a formula goes in double quotes, spaces included.

Previous: Two-way lookups · Next: LEFT, RIGHT, MID and LEN