Excel Practice
Lessons Lesson 55

LEFT and RIGHT: Taking from the Ends

About this lesson

LEFT returns a given number of characters from the start of a piece of text. RIGHT returns them from the end.

By the end you can

  • take the first or last few characters of a cell with LEFT and RIGHT.
  • read a formula that takes from the ends and say what it returns.

Practises LEFT RIGHT MID LEN

The idea

Both take two things: the text, and how many characters. Ask LEFT for three characters of a code beginning LDN and the depot comes back. Ask RIGHT for one and you get the last character. The second argument is a count, not a position. That is the only thing people get wrong at first.

Neither goes past the end. Ask LEFT for fifty characters of a ten-character code and you get all ten, not an error. That is useful when you know it. Sometimes you want everything from some point to the end, and you have no idea how long the text is. A large number is the clean way to say so. It is the trick that makes RIGHT usable on text of different lengths.

The mistake to watch for

Giving LEFT or RIGHT a position instead of a count. The second argument is how many characters to take. So asking for the third character with LEFT gives the first three. And the two never go past the end, which cuts both ways. Asking for more than there is returns everything. So a wrong count can look like a right answer on the short rows.

Where this comes up again

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

Every product code starts with a three-letter depot. Pull the depot out of A2 into B2.

To begin, type it exactly:

=LEFT(A2,3)

B2
Row A B C D E F G
1 Product code Depot Line
2 LDN-4471-A
3 MCR-2093-B
4 GLW-8810-A
5 BRS-5527-C
6
7 Code length 10
8

Every step

  1. 1

    Every product code starts with a three-letter depot. Pull the depot out of A2 into B2. To begin, type it exactly: =LEFT(A2,3).

    Hint. Two arguments: the text, and a number.

  2. 2

    The last character is the line. Pull it out of A2 into C2.

    Hint. Same shape as LEFT, other end.

  3. 3

    Somebody asked for more characters than there are. Read =LEFT(A3,50) and say what it returns.

    Hint. Ten characters, fifty asked for.

  4. 4

    In C5, join the depot and the line of the Bristol code with a hyphen between them. The result should look like BRS-C.

    Hint. Three pieces joined by two ampersands.