How to Separate First and Last Name in Google Sheets
For the first name use =INDEX(SPLIT(A2," "),1); for the last name use =REGEXEXTRACT(A2,"\S+$"), which grabs the final word even when there is a middle name.
=INDEX(SPLIT(A2," "),1)
SPLIT breaks the name on spaces; INDEX(...,1) returns the first piece (the first name).
How it works
SPLIT turns "Ada Lovelace" into separate cells at each space. INDEX(...,1) picks the first word for the first name. For the last name, REGEXEXTRACT(A2,"\S+$") is more reliable than counting words because \S+$ matches the final run of non-space characters no matter how many middle names there are. To split everything into columns at once, just use =SPLIT(A2," ").
Variations
Last name (handles middle names)
=REGEXEXTRACT(A2,"\S+$")
Matches the final word only, ignoring any middle names.
First name via regex
=REGEXEXTRACT(A2,"^\S+")
Matches the first run of non-space characters.
Split every part into its own column
=SPLIT(A2," ")
Spills first, middle, last across adjacent columns.
Examples
| Scenario | Formula |
|---|---|
| First name from full name | =INDEX(SPLIT(A2," "),1) |
| Last name from full name | =REGEXEXTRACT(A2,"\S+$") |
FAQ
How do I get just the last name?
Use =REGEXEXTRACT(A2,"\S+$"). It returns the final word, so middle names don't break it.
What if names have a middle name?
Use regex, not word position: ^\S+ for first and \S+$ for last will always grab the outer words.
How do I split a name into all its parts?
=SPLIT(A2," ") spills each word into its own column across the row.