Excel Practice
Lessons Lesson 61

Joining Text: & and CONCAT

About this lesson

The ampersand joins pieces of text end to end. So a first name, a space in quotation marks and a last name become one full name.

By the end you can

  • join cells and words together with the ampersand.
  • use CONCAT for a list of pieces and say when & is simpler.

Practises CONCAT CONCATENATE TEXTJOIN

The idea

There are three ways to join text in Excel, and they do the same thing. The ampersand is the shortest. It reads in the order the result comes out. CONCAT is the function form. CONCATENATE is the old name for CONCAT. It is kept for old files and not worth learning now.

Whichever you use, joining adds nothing you did not ask for. No space appears between two names unless you put one there in quotation marks. That is the mistake everybody makes once. The less obvious point is what the result is. Joining always produces text. So two numbers joined together are no longer numbers. They will not match either number in a lookup, will not sort in numeric order, and will not add up. Join for display. Keep the original columns for anything that has to compute.

The mistake to watch for

Two mistakes, one obvious and one not. Forgetting the space, so two names run together, is caught the first time you look. The quieter one is joining numbers. The result is text. It will not add up, will not sort in numeric order, and will not match either number in a lookup. Join for display, and keep the original columns for anything that has to compute.

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Join the first and last name in D2 with a space between them, so you get a full name.

To begin, type it exactly:

=A2&" "&B2

D2
Row A B C D E F G
1 First Last Town Label
2 Rosa Marchetti Deal
3 Elliot Nwankwo Frome
4 Sana Idris Rhyl
5
6
7
8

Every step

  1. 1

    Join the first and last name in D2 with a space between them, so you get a full name. To begin, type it exactly: =A2&" "&B2.

    Hint. You need something between the two names.

  2. 2

    Somebody left the space out. Read =A3&B3 and say what it returns.

    Hint. Nothing goes between them.

  3. 3

    Now the same job with a function. In D3, use CONCAT to join the first name, a space and the last name.

    Hint. Three arguments.

  4. 4

    Build a full postal label in D4. First name, a space, the last name, then a comma and a space, then the town.

    Hint. Count the separators you need before you start.

  5. 5

    Joining changes what the value is. Read =LEN(A2&B2) and say what it returns.

    Hint. Four letters and nine letters.