Learning Excel: Joining text
Sticking text together
For the place cards on the tables, Natalie wants the first name and last name together. You can already cut text up; now for the reverse.
Joining with &
With & you stick texts together. A space doesn't come along by itself: you add it as text between quotation marks.
=A2&" "&B2 gives Sadie De Vere.
G2
=A2&" "&B2
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | |
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | Sadie De Vere |
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | |
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | |
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | |
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | |
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | |
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | |
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | |
| 10 |
TEXTJOIN
To join a whole range, TEXTJOIN is shorter. You give the delimiter, TRUE to skip empty cells, and the range.
=TEXTJOIN(", ",TRUE,A2:A9) puts all the first names in a row, with a comma and a space between them.
G2
=TEXTJOIN(", ",TRUE,A2:A9)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | |
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | Sadie, James, Amira, Liam, Faye, Bill, Libby, Owen |
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | |
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | |
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | |
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | |
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | |
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | |
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | |
| 10 |
Try it yourself
In G2, make a short place card, like S. De Vere, and fill down to G9.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | First name | Last name | Table | Mobile | As typed | Result | ||
| 2 | Sadie | De Vere | sadie.devere@mail.co.uk | T01-COD | 07700 900 467 | sadie de vere | ||
| 3 | James | Pearson | james.pearson@yahoo.co.uk | T01-STEAK | 07700 900 233 | JAMES PEARSON | ||
| 4 | Amira | El Idrissi | amira@elidrissi.co.uk | T02-VEG | 07700 900 654 | amira el idrissi | ||
| 5 | Liam | Walters | liam.w@gmail.com | T02-COD | 07700 900 566 | Liam Walters | ||
| 6 | Faye | Clark | faye.clark@hotmail.co.uk | T03-STEAK | 07700 900 456 | faye clark | ||
| 7 | Bill | Moss | bill@moss-bakery.co.uk | T03-VEG | 07700 900 321 | BILL moss | ||
| 8 | Libby | Jennings | libby.jennings@yahoo.co.uk | T04-COD | 07700 900 899 | libby jennings | ||
| 9 | Owen | Van Dyke | owen.vandyke@mail.co.uk | T04-STEAK | 07700 900 030 | owen VAN DYKE | ||
| 10 |
G2
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson