Lessons Lesson 59

LEFT with FIND: Splitting a Name

About this lesson

Putting FIND inside LEFT splits text at a character whose position you cannot predict. So one formula takes the first name out of every row, whatever the names are.

By the end you can

  • split text at a character by putting FIND inside LEFT.
  • take the part after a character with MID and FIND together.

Practises LEFT FIND MID LEN

By Ali, Founder, Excel Lessons

The idea

This is the first formula in the course where one function's answer becomes another's argument. It deserves a pause. LEFT needs to be told how many characters to take. You do not know, because the names are different lengths. FIND works it out. Put it where the number goes, and the formula adjusts itself.

The minus one is where people get hurt. Learn it as a sentence, not a habit. FIND returns where the space is. LEFT wants how many characters come before it. Those differ by one. Leave the minus one out and you get the name with a space stuck on the end. It looks completely correct on screen, because the space is at the end and nothing moves. Then it fails to match anything. A VLOOKUP against it returns #N/A, and a comparison returns FALSE. You will check the lookup, the range and the data before you check the length. =LEN() on the result is the two-second answer. The vlookup-errors lesson has the other half of this story.

The mistake to watch for

Leaving out the minus one. FIND returns where the space is. LEFT wants how many characters come before it. Those differ by one. Without the subtraction, the first name comes back with a space on the end. It looks the same on screen, and it fails to match anything it is compared to. LEN on the result is how you catch it.

Read more about this

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

You cannot split a name without knowing where the space is.

In B3, type:

=FIND(" ",A2)

B3
Row A B C D E F G
1 Contact First name Surname
2 Amara Osei
3 Tomasz Wójcik
4 Ingrid Halvorsen
5 Dele Adeyemi
6
7
8

Every step

  1. 1

    You cannot split a name without knowing where the space is. In B3, type: =FIND(" ",A2).

    Hint. Look for a single space.

  2. 2

    LEFT takes text from the start, for a given length. In B2, take the first name from A2 using LEFT and FIND. Shape: =LEFT(text,FIND(" ",text)-1).

    Hint. The position of the space is one more than the length of the first name.

  3. 3

    Without the minus one, LEFT takes the space too. Read =LEN(LEFT(A2,FIND(" ",A2))) and say what it returns.

    Hint. Count the characters LEFT actually took.

  4. 4

    MID can take everything from a position onward. In C2, take the surname from A2, after the space. Shape: =MID(text,FIND(" ",text)+1,99).

    Hint. One past the space, and then everything.

  5. 5

    B5 should return the first name from A5, but a hidden character breaks every comparison. Fix B5. Remember: LEFT without -1 keeps the space in the result.

    Hint. One character too many.

  6. 6

    In B4, get the first name from A4 any way, using FIND. Do not count the characters yourself.

    Hint. Find the space, then take everything before it.