Hands-On Excel

Hands-On Excel Challenges LEFT, MID + FIND

Split first and last names in Excel

First name: =LEFT(A2, FIND(" ", A2) - 1). Last name: =MID(A2, FIND(" ", A2) + 1, LEN(A2)). FIND gives the position of the space, and the -1 and +1 keep the space itself out.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

15 · LEFT, MID + FIND · Core

Split first and last names

Split each full name into a first name in column B and a last name in column C. Every name has one space, between the first and last name.

FIND(" ", A2) gives the position of the space: 4 in "Ada Lovelace". The first name is everything before it, the last name everything after it.

contacts.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · LEFT, MID + FIND

Find the space

=FIND(" ", A2) returns the position of the first space. In "Ada Lovelace" that's 4: A, d, a, then the space. Everything before it is the first name, everything after it the last name.

First name: LEFT

LEFT(text, n) takes the first n characters. The first name is one character shorter than the position of the space, so: =LEFT(A2, FIND(" ", A2) - 1). Without the -1 you get "Ada " with a space at the end. It looks the same, but lookups and comparisons treat it as different text.

Last name: MID or RIGHT

MID(text, start, n) takes n characters from a starting position. Start one after the space and take plenty: =MID(A2, FIND(" ", A2) + 1, LEN(A2)). Asking for more characters than are left is fine. The RIGHT version counts from the end, so it needs the exact length: =RIGHT(A2, LEN(A2) - FIND(" ", A2)).

In Excel 365: TEXTBEFORE and TEXTAFTER

=TEXTBEFORE(A2, " ") and =TEXTAFTER(A2, " ") do the same in one step. They're easier to read, but files opened in Excel 2021 or older show #NAME?.

No formula: Text to Columns and Flash Fill

Data → Text to Columns splits on the space once, and Flash Fill (Ctrl+E) copies a pattern you type. Both are quick for a one-off clean-up, but the results are fixed values: add a new name and you have to do it again. A formula updates by itself.

Common mistakes

FAQ

How do I separate first and last names in Excel?
Use =LEFT(A2,FIND(" ",A2)-1) for the first name and =MID(A2,FIND(" ",A2)+1,LEN(A2)) for the last name, then fill both down.
Why is there a space at the end of my first name?
FIND returns the position of the space itself, so LEFT(A2,FIND(" ",A2)) includes it. Subtract 1: LEFT(A2,FIND(" ",A2)-1).
How do I get the last name when there is a middle name?
In Excel 365, =TEXTAFTER(A2," ",-1) returns the text after the last space. In older versions this needs a longer formula with SUBSTITUTE.
Should I use Text to Columns or a formula to split names?
Text to Columns is quick for a one-time clean-up. Use a formula when the list will change, because it updates automatically.

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Percent of a totalPercentages Starter · 3 min
  6. 06 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  7. 07 Pass or fail?IF Core · 3 min
  8. 08 Count the ones that matchCOUNTIF Core · 3 min
  9. 09 Add up with two conditionsSUMIFS Core · 3 min
  10. 10 Look it upVLOOKUP Core · 3 min
  11. 11 Find the right bracketVLOOKUP approximate match Core · 3 min
  12. 12 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  13. 13 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  14. 14 Catch the error, not the bugIFERROR Core · 3 min
  15. 15 Split first and last namesLEFT, MID + FIND Core · 3 min
  16. 16 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy