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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

The ampersand (&) joins pieces of text end to end.

In D2, type:

=A2&" "&B2

Remember: the space is text, so it goes in quotes.

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

    The ampersand (&) joins pieces of text end to end. In D2, type: =A2&" "&B2. Remember: the space is text, so it goes in quotes.

    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

    CONCAT does the same job as a function. In D3, join A3, a space and B3 with CONCAT. Shape: =CONCAT(text, text, text).

    Hint. Three arguments: the name, the space, the name.

  4. 4

    In D4, build a postal label from A4, B4 and C4. Shape: =text&" "&text&", "&text. The comma and its space go inside one pair of quotes.

    Hint. Count the separators you need before you start.

  5. 5

    Joining makes one new value. Read =LEN(A2&B2) and say what it returns.

    Hint. Four letters and nine letters.