AaText19 min read ·

How to Split First and Last Names in Excel

Separate first and last names in Excel with TEXTBEFORE, TEXTAFTER, LEFT and FIND, Text to Columns or Flash Fill, even with middle names and extra spaces.

Quick answer

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Use =TEXTBEFORE(A2, " ") for the first name and =TEXTAFTER(A2, " ", -1) for the last name, where -1 means the last space, so middle names are skipped. In Excel 2021 or older, use =LEFT(A2, FIND(" ", A2)-1) for the first name, and wrap A2 in TRIM if the names have extra spaces.

Arguments in [brackets] are optional.

On this page ⌄
  1. The TEXTBEFORE and TEXTAFTER syntax
  2. Get the first name with TEXTBEFORE
  3. Get the last name with TEXTAFTER
  4. Clean messy names first: TRIM and PROPER
  5. Split a name into columns with TEXTSPLIT
  6. Extract the middle name or initial
  7. Middle and last names in Excel 2021 and older
  8. Split "Last, First" names
  9. Names with only one word: IFERROR and if_not_found
  10. Separate names with Text to Columns
  11. Separate names with Flash Fill (Ctrl+E)
  12. Join first and last names back together
  13. Common mistakes when splitting names
  14. Frequently asked questions about splitting names
  15. Which versions of Excel have TEXTBEFORE, TEXTAFTER and TEXTSPLIT?
  16. Key takeaways

To split a full name into a first and last name in Excel, take the text before the first space for the first name, and the text after the last space for the last name. In Microsoft 365, Excel 2024 and Excel for the web, TEXTBEFORE and TEXTAFTER do each job in one short formula:

=TEXTBEFORE(A6, " ")→ Sofia
=TEXTAFTER(A6, " ", -1)→ Martinez

TEXTSPLIT puts every part of a name in a cell of its own. In Excel 2021 and older, LEFT, RIGHT and FIND do the same job, and Text to Columns and Flash Fill split names without a formula. This guide covers all five ways, plus the names that break the simple formulas: extra spaces, messy capitals, middle names, "Last, First" and names with only one word.

The examples use this customer export in A1:D6, the same one as the TRIM guide and the LEFT, RIGHT and MID guide:

A: Full nameB: EmailC: PhoneD: Order code
2emma JOHNSON[email protected](555) 201-4433NW-2026-0187
3liam o'brien[email protected]555.388.1902CT-2025-0942
4OLIVIA chen[email protected]555-647-2210FB-2026-0033
5noah patel[email protected](555) 903 7751NW-2026-0451
6Sofia Martinez[email protected]555 412 0098AW-2024-1120

The names are messy on purpose. A2 has two spaces before emma, three between the words and one after JOHNSON. A4 ends with two spaces, and A5 has three spaces between noah and patel. A3 and A6 are tidy. You can't see the spaces in the table, but every formula below sees them.

A few examples also use these names in H1:H5, each with a problem of its own:

H: Name
2Grace Elizabeth Kim
3James R. Walker
4Rivera, Daniel
5Priya

H2 has a middle name and H3 a middle initial. H4 puts the last name first, and H5 has only one name.

The TEXTBEFORE and TEXTAFTER syntax

TEXTBEFORE returns the text before a delimiter, the character you split at, and TEXTAFTER returns the text after it. For names, the delimiter is a space, typed as a space between two quotes: " ". Both functions take the same arguments:

ArgumentWhat it meansExample
textThe text to split, usually a cellA6
delimiterThe character or text to split at, in quotes" "
instance_numOptional. Which delimiter to use. 1, the default, is the first and 2 the second. A negative number counts from the end, so -1 is the last-1
match_modeOptional. 0, the default, matches upper and lower case exactly, and 1 ignores case. It only matters when the delimiter contains letters1
match_endOptional. 1 treats the end of the text as a delimiter too1
if_not_foundOptional. What to return when the delimiter isn't in the text. Without it, you get #N/A""

Most name formulas only need the first three. The last two come up for names with only one word.

Get the first name with TEXTBEFORE

What's the first name in A6? It's the text before the first space:

=TEXTBEFORE(A6, " ")→ Sofia
=TEXTBEFORE(A3, " ")→ liam

TEXTBEFORE stops at the first space, so a middle name doesn't get in the way. The first name in H2 comes out the same way:

=TEXTBEFORE(H2, " ")→ Grace

The result keeps the capitals of the original, so liam stays lowercase. PROPER fixes that, below. To split a whole list, type the formula next to the first name and copy it down.

In Excel 2021 and older, find the space with FIND and take everything to its left with LEFT:

=LEFT(A3, FIND(" ", A3)-1)→ liam

FIND returns 5, the position of the space in liam o'brien, and LEFT takes the 4 characters before it. Without the -1, the result would end with the space. FIND is case-sensitive and SEARCH isn't, but for a space or a comma, either one works. The LEFT, RIGHT and MID guide explains the pattern step by step.

Get the last name with TEXTAFTER

The last name is the text after the last space. Give TEXTAFTER -1 as its third argument, so it counts from the end:

=TEXTAFTER(A6, " ", -1)→ Martinez
=TEXTAFTER(A3, " ", -1)→ o'brien

In a two-word name, the first space is also the last, so you could leave out the -1. It matters as soon as a name has a middle name:

=TEXTAFTER(H2, " ")→ Elizabeth Kim
=TEXTAFTER(H2, " ", -1)→ Kim

Without -1, TEXTAFTER splits at the first space and returns the middle and last names together. With -1, it returns only Kim. Use -1 for last names even if your list has no middle names today, so the formula keeps working when one turns up.

The classic formula for older versions takes everything after the first space, so it has the same problem:

=RIGHT(A3, LEN(A3)-FIND(" ", A3))→ o'brien
=RIGHT(H2, LEN(H2)-FIND(" ", H2))→ Elizabeth Kim

A3 has 12 characters and its space is the 5th, so RIGHT takes the last 7. To get the text after the last space in Excel 2021 and older, see middle and last names in older versions below.

Clean messy names first: TRIM and PROPER

Names exported from other systems rarely look like A6. Run the same formulas on the messy rows, and the extra spaces get in the way:

FormulaResultWhy
=TEXTBEFORE(A2, " ")Empty textA2 starts with a space, so there's nothing before the first one
=TEXTAFTER(A4, " ", -1)Empty textA4 ends with a space, so there's nothing after the last one
=TEXTAFTER(A5, " ")patel, with two spaces in frontThe first of the three spaces is the delimiter, and the other two are kept
=TEXTBEFORE(A4, " ")OLIVIARight, but in capitals

The empty results look like blank cells, but they're formulas returning empty text. The fix is to clean the name before you split it. TRIM removes the spaces at the start and end and turns every run of spaces between words into one. PROPER capitalizes the first letter of each word and lowercases the rest. Put TRIM around the cell, inside the split, and PROPER around everything:

=PROPER(TEXTBEFORE(TRIM(A4), " "))→ Olivia
=PROPER(TEXTAFTER(TRIM(A4), " ", -1))→ Chen
=PROPER(TEXTAFTER(TRIM(A5), " ", -1))→ Patel

Read them from the inside out. TRIM removes the two spaces at the end of A4, TEXTBEFORE takes OLIVIA, and PROPER turns it into Olivia. The TRIM guide covers the spaces TRIM can't remove, such as the non-breaking spaces in text copied from a web page.

TEXTAFTER and TEXTBEFORE also accept a whole column. This formula spills five last names into the cells below it:

=PROPER(TEXTAFTER(TRIM(A2:A6), " ", -1))→ Johnson, O'Brien, Chen, Patel, Martinez

PROPER capitalizes any letter that follows something other than a letter. That's why o'brien becomes O'Brien. The same rule gets some names right and others wrong:

=PROPER("o'brien")→ O'Brien
=PROPER("mary-kate")→ Mary-Kate
=PROPER("angus mcdonald")→ Angus Mcdonald

Your turn. The sheet below is live and uses the same customer export. The exercise asks you to clean the full name in A2, the messiest one, before any splitting: fix the spaces and the capitals in one formula, then press Enter.

Try it yourself

A live spreadsheet. Type a formula and press Enter to check it.

Exercise 1 of 11TRIMPROPER

Clean the name in A2: remove the extra spaces and capitalize it properly.

Write your formula in the green cell, G2.

customer-export.xlsx
fx
1Full nameEmailPhoneOrder code
2 emma JOHNSON [email protected](555) 201-4433NW-2026-0187Clean name
3liam o'brien[email protected]555.388.1902CT-2025-0942
4OLIVIA chen [email protected]555-647-2210FB-2026-0033
5noah patel[email protected](555) 903 7751NW-2026-0451
6Sofia Martinez[email protected]555 412 0098AW-2024-1120
7
8
9
10

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

Split a name into columns with TEXTSPLIT

TEXTSPLIT splits a text at every delimiter and spills the pieces into the cells to the right, so one formula fills in both the first and the last name:

=TEXTSPLIT(A6, " ")→ Sofia, Martinez
=TEXTSPLIT(H2, " ")→ Grace, Elizabeth, Kim

Type the first one in F6, and Sofia lands in F6 and Martinez in G6. The full syntax is:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

For names, you only need the first two arguments. A name with a middle name spills into three cells, so in a list that mixes both kinds, the last names don't line up in one column. When you need a column of last names, use TEXTAFTER with -1 instead.

Every space is a split point, extra ones included. =TEXTSPLIT(A4, " ") returns four cells: OLIVIA, chen and two empty ones, one for each space at the end of A4. TRIM fixes that, and PROPER fixes the capitals of every piece at once:

=PROPER(TEXTSPLIT(TRIM(A4), " "))→ Olivia, Chen

TEXTSPLIT's ignore_empty argument does part of TRIM's job. Set to TRUE, as in =TEXTSPLIT(A5, " ", , TRUE), it ignores consecutive delimiters, so a run of spaces like the three in A5 doesn't leave empty cells. TRIM is still the better habit, because the trimmed name works in every other formula in this guide too.

TEXTSPLIT needs empty cells to spill into. If a cell to the right already holds something, you get #SPILL! instead. To split a whole list, type TEXTSPLIT next to the first name and copy it down, and each copy spills across its own row.

Extract the middle name or initial

H2 and H3 have three parts. The first and last name formulas work on them unchanged:

=TEXTBEFORE(H3, " ")→ James
=TEXTAFTER(H3, " ", -1)→ Walker

The middle name is what's left between the first space and the last one. TEXTAFTER removes the first name, then TEXTBEFORE with -1 removes the last name:

=TEXTAFTER(H2, " ")→ Elizabeth Kim
=TEXTBEFORE(TEXTAFTER(H2, " "), " ", -1)→ Elizabeth
=TEXTBEFORE(TEXTAFTER(H3, " "), " ", -1)→ R.

Because of the -1, a name with two middle names returns both of them. For only the initial, take the first character with LEFT:

=LEFT(TEXTBEFORE(TEXTAFTER(H2, " "), " ", -1))→ E

A two-word name has no middle name. There's no second space to find, so the formula returns #N/A:

=TEXTBEFORE(TEXTAFTER(A6, " "), " ", -1)→ #N/A

Wrap it in IFERROR to show empty text instead. =IFERROR(TEXTBEFORE(TEXTAFTER(A6, " "), " ", -1), "") returns empty text for Sofia Martinez, and for a single name like Priya, and still returns Elizabeth for H2.

To find the rows that have a middle name, count the words. That's the number of spaces plus one, and the spaces are the characters SUBSTITUTE removes:

=LEN(TRIM(H2))-LEN(SUBSTITUTE(TRIM(H2), " ", ""))+1→ 3

Middle and last names in Excel 2021 and older

Without TEXTAFTER's -1, getting the text after the last space takes a trick. The best-known one swaps each space for 100 spaces:

=TRIM(RIGHT(SUBSTITUTE(H2, " ", REPT(" ", 100)), 100))→ Kim

REPT(" ", 100) is 100 spaces, and SUBSTITUTE puts them in place of each space in the name. Now the last name sits at the end, behind a long run of spaces, so the last 100 characters are spaces and Kim, with nothing from Elizabeth. TRIM removes the spaces. It works for any number of words, as long as the last name is no longer than 100 characters. Copied to other rows, it returns Walker for H3 and Martinez for A6.

The second way finds the position of the last space. Count the spaces, swap the last one for a character that isn't in any name, such as #, then find the #:

=LEN(H2)-LEN(SUBSTITUTE(H2, " ", ""))→ 2
=SUBSTITUTE(H2, " ", "#", 2)→ Grace Elizabeth#Kim
=FIND("#", SUBSTITUTE(H2, " ", "#", 2))→ 16

Removing every space shortens H2 from 19 characters to 17, so it has 2 spaces. SUBSTITUTE's fourth argument replaces only the 2nd one, and FIND returns its position, 16. RIGHT then takes the 19 − 16 = 3 characters after it. All in one formula:

=RIGHT(H2, LEN(H2)-FIND("#", SUBSTITUTE(H2, " ", "#", LEN(H2)-LEN(SUBSTITUTE(H2, " ", "")))))→ Kim

The REPT version is shorter. The LEN version is useful when you need the position of the last space itself. Both work in every version of Excel.

For the middle name, find both spaces and take what's between them with MID:

=MID(H2, FIND(" ", H2)+1, FIND(" ", H2, FIND(" ", H2)+1)-FIND(" ", H2)-1)→ Elizabeth

The first space is the 6th character. The second FIND starts looking one character later, at 7, so it finds the second space, at 16. MID starts at 7 and takes 16 − 6 − 1 = 9 characters. A two-word name has no second space, so FIND returns #VALUE!, and IFERROR around the formula turns that into empty text.

Split "Last, First" names

Some systems export names last name first, like Rivera, Daniel in H4. Split at the comma instead of the space:

=TEXTBEFORE(H4, ",")→ Rivera
=TEXTAFTER(H4, ", ")→ Daniel

The delimiter for the first name is a comma and a space, ", ", so the space doesn't end up at the start of Daniel. If some rows have no space after the comma, split at the comma alone and let TRIM remove the space: =TRIM(TEXTAFTER(H4, ",")) also returns Daniel. TEXTSPLIT takes the same delimiter, and =TEXTSPLIT(H4, ", ") spills Rivera and Daniel into two cells, last name first.

To turn the name around into First Last, join the two parts with a space between them:

=TEXTAFTER(H4, ", ")&" "&TEXTBEFORE(H4, ",")→ Daniel Rivera

In Excel 2021 and older, find the comma with FIND:

=LEFT(H4, FIND(",", H4)-1)→ Rivera
=TRIM(MID(H4, FIND(",", H4)+1, LEN(H4)))→ Daniel

The comma is the 7th character, so LEFT takes the 6 before it. MID starts just after the comma and runs to the end, which gives a space and Daniel, and TRIM removes the space.

If a column mixes both formats, test for the comma first. ISNUMBER(FIND(",", H4)) is TRUE when there's a comma, and IF picks the formula that fits:

=IF(ISNUMBER(FIND(",", H4)), TEXTAFTER(H4, ", "), TEXTBEFORE(H4, " "))→ Daniel

The same formula returns Grace for H2, which has no comma.

Names with only one word: IFERROR and if_not_found

Some lists have rows with a single name, like Priya in H5. There's no space to split at, so the formulas return errors:

=TEXTBEFORE(H5, " ")→ #N/A
=TEXTAFTER(H5, " ", -1)→ #N/A
=LEFT(H5, FIND(" ", H5)-1)→ #VALUE!

TEXTBEFORE and TEXTAFTER return #N/A when they can't find the delimiter, and FIND returns #VALUE!. Decide what a single name should give you, usually the name as the first name and nothing as the last name. Then put that in if_not_found, the sixth argument:

=TEXTBEFORE(H5, " ", , , , H5)→ Priya

For the last name, =TEXTAFTER(H5, " ", -1, , , "") returns empty text. The empty commas skip the arguments in between. Names with a space aren't affected, so the same two formulas still return Sofia and Martinez for A6.

For the first name, there's a shorter way. match_end set to 1 makes the end of the text count as a delimiter, so a single name comes back whole:

=TEXTBEFORE(H5, " ", , , 1)→ Priya

TEXTSPLIT doesn't need either fix. =TEXTSPLIT(H5, " ") returns Priya in one cell.

In Excel 2021 and older, use IFERROR, which replaces any error with the value you give it. It works around TEXTBEFORE too:

=IFERROR(LEFT(H5, FIND(" ", H5)-1), H5)→ Priya
=IFERROR(TEXTBEFORE(H5, " "), H5)→ Priya

Where you have the choice, if_not_found is more precise. It only covers the missing space, while IFERROR hides every error, including the #NAME? from a misspelled function. The IFERROR guide has more on when to use it.

The REPT formula for the last name doesn't return an error for a single name. It returns the whole name, so Priya would end up as both the first and the last name. Check one-word rows by eye.

Separate names with Text to Columns

For a one-off job, Text to Columns splits the names without any formulas:

  1. Select the names, A2:A6.
  2. On the Data tab, click Text to Columns.
  3. Choose Delimited and click Next.
  4. Under Delimiters, tick Space. Tick Treat consecutive delimiters as one too, so runs of spaces like the three in A5 don't create empty columns. Click Next.
  5. In Destination, enter an empty cell, such as $F$2. Otherwise the first names go over the full names in column A and the last names over the emails in column B, after Excel asks whether to replace the data there.
  6. Click Finish.

For "Last, First" names, tick both Comma and Space, along with Treat consecutive delimiters as one, so the comma and the space after it count as one split.

Text to Columns is quick, but keep its limits in mind:

  • The results don't update. They're values, not formulas, so a corrected name or a new row needs splitting again.
  • Every space is a split. A middle name gets a column of its own and pushes that row's last name one column to the right.
  • It splits the text as it is. It doesn't fix capitals, so for messy names like these, clean the column with TRIM and PROPER first and paste the results back as values. The TRIM guide shows how.

Separate names with Flash Fill (Ctrl+E)

Flash Fill fills a column by following an example you type. To get the first names:

  1. In the first empty column next to the data, type the first name from row 2 the way you want it to look.
  2. Press Enter, then press Ctrl+E, or click Data > Flash Fill.

Excel fills the cells below with the same pattern. Do the same in the next column, with the last name from row 2, to get the last names.

Flash Fill guesses the pattern from your example, and it doesn't tell you when it guesses wrong. Check every row, especially the messy ones and names with a middle name, and correct any it got wrong. Like Text to Columns, it returns values, so the results don't change when the names do. When the list changes often, use the formulas instead.

Join first and last names back together

The & operator joins text. Put a space, or a comma and a space, in quotes between the parts:

=TEXTAFTER(A6, " ", -1)&", "&TEXTBEFORE(A6, " ")→ Martinez, Sofia
=LEFT(A6)&". "&TEXTAFTER(A6, " ", -1)→ S. Martinez
=LOWER(TEXTBEFORE(A6, " ")&"."&TEXTAFTER(A6, " ", -1))→ sofia.martinez

When the parts are already in their own cells, TEXTJOIN is handier, because it can skip empty cells. Say I2:K4 hold the first, middle and last names from H2:H4, so row 4 has Daniel, an empty middle name and Rivera:

=I2&" "&K2→ Grace Kim
=TEXTJOIN(" ", TRUE, I2:K2)→ Grace Elizabeth Kim
=TEXTJOIN(" ", TRUE, I4:K4)→ Daniel Rivera

The first argument goes between the parts, and TRUE tells TEXTJOIN to skip the empty ones. With &, =I4&" "&J4&" "&K4 puts two spaces between Daniel and Rivera, one on each side of the missing middle name.

Common mistakes when splitting names

ProblemWhat's going onFix
An empty first nameThe cell starts with a space, so there's nothing before the first onePut TRIM around the cell inside the formula
An empty last nameThe cell ends with a space, so there's nothing after the last onePut TRIM around the cell inside the formula
The last name includes the middle nameTEXTAFTER without -1, or RIGHT with FIND, splits at the first space=TEXTAFTER(A6, " ", -1), or the REPT formula in older versions
#N/A from TEXTBEFORE or TEXTAFTERThere's no space or comma to split at, as in a single nameUse the if_not_found argument, or IFERROR
#VALUE! from a formula with FINDFIND found no spaceFIND(" ", A6&" "), or IFERROR
#NAME?TEXTBEFORE, TEXTAFTER and TEXTSPLIT don't exist in Excel 2021 and olderUse LEFT, RIGHT, MID and FIND
Empty cells between the parts from TEXTSPLITEvery extra space is a split pointTRIM first, or set ignore_empty to TRUE
#SPILL! from TEXTSPLITThe cells to the right aren't emptyClear them, or move the formula
OLIVIA or liam instead of Olivia or LiamThe split keeps the original capitalsWrap the formula in PROPER
Mcdonald instead of McDonaldPROPER doesn't know name rules like McFix those names by hand
A double space in a joined name& keeps the spaces around an empty middle nameTEXTJOIN with TRUE
Split names don't updateText to Columns and Flash Fill return values, not formulasUse the formulas

Frequently asked questions about splitting names

How do I get initials from a name?

Take the first letter of the first and last name with LEFT, and join them:

=LEFT(A6)&LEFT(TEXTAFTER(A6, " ", -1))→ SM

For every word, middle names included, LEFT can take the first letter of each piece from TEXTSPLIT, and CONCAT joins them:

=CONCAT(LEFT(TEXTSPLIT(H2, " ")))→ GEK

What about last names with more than one word?

The formulas in this guide treat the last word as the last name, so Juan de la Cruz gives Cruz. To a formula, the de in Juan de la Cruz looks just like the Elizabeth in Grace Elizabeth Kim:

=TEXTAFTER("Juan de la Cruz", " ", -1)→ Cruz
=TEXTAFTER("Juan de la Cruz", " ")→ de la Cruz

If your list has no middle names, take everything after the first space instead, as in the second formula. If it has both, no formula gets every row right. Check those rows by hand, or ask for the names in separate first and last name fields from the system they came from.

How do I remove a title like Dr. or a suffix like Jr.?

Titles and suffixes count as words, so Dr. comes out as the first name and Jr. as the last. Remove them with SUBSTITUTE before you split:

=TEXTBEFORE(SUBSTITUTE("Dr. Grace Kim", "Dr. ", ""), " ")→ Grace
=TEXTAFTER(SUBSTITUTE("James Walker Jr.", " Jr.", ""), " ", -1)→ Walker

SUBSTITUTE is case-sensitive, so the first formula would leave DR. in place. For several titles, nest one SUBSTITUTE inside another, or remove them with Find & Replace, Ctrl+H, before splitting.

How do I sort a list by last name?

Put the last names in a helper column and sort the table by that column with Data > Sort. In Microsoft 365 and Excel 2024, SORTBY does it with one formula, sorting the names by their last names without a helper column:

=SORTBY(TRIM(A2:A6), TEXTAFTER(TRIM(A2:A6), " ", -1))→ OLIVIA chen, emma JOHNSON, Sofia Martinez, liam o'brien, noah patel

The sort ignores upper and lower case, so chen comes before JOHNSON.

How do I look up a customer by last name?

Split the names inside the lookup. TEXTAFTER returns a list of last names for XLOOKUP to search, and the data on the sheet stays as it is:

=XLOOKUP("chen", TEXTAFTER(TRIM(A2:A6), " ", -1), B2:B6)→ [email protected]

XLOOKUP ignores upper and lower case, so chen matches too. If two customers share a last name, XLOOKUP returns the first one.

Which versions of Excel have TEXTBEFORE, TEXTAFTER and TEXTSPLIT?

TEXTBEFORE, TEXTAFTER and TEXTSPLIT need Microsoft 365, Excel 2024 or Excel for the web, on Windows or Mac. Excel 2021 doesn't have them, and typed into Excel 2021 or older, they return #NAME?. The rest of this guide works in older versions. LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE, REPT, TRIM and PROPER work in every version of Excel, IFERROR in Excel 2007 or later, Flash Fill in Excel 2013 or later, and TEXTJOIN and CONCAT in Excel 2019 or later. Text to Columns is in every version of Excel for Windows and Mac.

In Google Sheets, LEFT, RIGHT, FIND, SUBSTITUTE, REPT, TRIM and PROPER work the same way, and SPLIT does the job of TEXTSPLIT. For Microsoft's own examples, see the TEXTBEFORE function, TEXTAFTER function, TEXTSPLIT function and PROPER function pages.

For more practice, the text topic has the free clean-name exercise on the same customer export, plus Premium exercises on a first name from a messy cell, the text before the @ with TEXTBEFORE, a label built from a code and a clean name and joining a list with TEXTJOIN.

Key takeaways

  • =TEXTBEFORE(A2, " ") returns the first name and =TEXTAFTER(A2, " ", -1) the last name. The -1 picks the last space, so middle names are skipped, and TEXTSPLIT puts every part in a cell of its own.
  • Clean messy names inside the formula. TRIM around the cell removes extra spaces, and PROPER around the result fixes the capitals. Check names like McDonald by eye.
  • A single name makes TEXTBEFORE and TEXTAFTER return #N/A, and FIND return #VALUE!. Use the if_not_found argument, or IFERROR.
  • In Excel 2021 and older, use LEFT with FIND for the first name and =TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100)) for the last name. Text to Columns and Flash Fill split names without formulas, but their results don't update.

Practice

Keep going with TEXTBEFORE and TEXTAFTER exercises

Reading gets you started. Solving a few of these is what makes it stick.

  1. 1Clean up a nameFreeClean the name in A2: remove the extra spaces and capitalize it properly.→
  2. 2First name from a messy cell PremiumGet the first name from A5, cleaned up and capitalized: Noah.→
  3. 3Text before a character PremiumGet the part of the email in B6 that comes before the @.→
  4. 4Build a label PremiumFor row 5, build a label like AW-2024-1120 - Sofia Martinez: the order code, a space, a dash, a space, then the clean name.→
  5. 5Join a list PremiumJoin all five order codes in D2:D6 into one cell, separated by a comma and a space.→
All text exercises →
Brian Millot

About the author

Brian Millot

Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.

Related guides

Practice makes formulas

Stop reading. Start typing formulas.

Every exercise is a real spreadsheet with instant feedback. The first two in every topic are free, no sign-up needed.