How to Split First and Last Names in Excel

If A2 contains Maya Lopez, enter =TEXTSPLIT(A2," ") in B2 to put Maya in B2 and Lopez in C2. This is a quick way to split first and last names in Excel when every name has exactly two parts separated by one space.

Download the split-names practice workbook to follow the examples and compare the formula outputs.

For a list that changes, use formulas so the results follow the source cells. For a one-time cleanup, use Text to Columns. Both methods split at a character you choose; neither can decide which words belong to someone's given or family name. Check that your list follows the rule before labeling the output columns.

The formula examples below use Microsoft 365 or Excel 2024 for Windows or Mac. If your version doesn't recognize TEXTSPLIT, TEXTBEFORE, or TEXTAFTER, use the Text to Columns section.

Split each word into its own column

Start on a blank worksheet. Enter Full name in A1, Part 1 in B1, Part 2 in C1, and Part 3 in D1. Put Maya Lopez in A2 and Dakota Lennon Sanchez in A3.

In B2, enter:

=TEXTSPLIT(A2," ")

Copy B2 to B3. Leave the cells to the right empty so Excel can place the results there automatically. This is called spilling.

The space inside " " tells TEXTSPLIT where to separate the text. Notice that row 3 needs three output columns. Calling column C “Last name” would make that row misleading: it contains Lennon, while Sanchez is in D3.

Microsoft's TEXTSPLIT documentation describes the delimiter and spill behavior. If Excel shows #SPILL!, check whether something occupies the cells the result needs. Move that content or choose a clear output area. Enter these spilling formulas in ordinary worksheet cells outside an Excel Table.

Allow for extra spaces and empty rows

A pasted name may contain leading spaces or several spaces between words. Replace the formula in B2 with:

=IF(TRIM(A2)="","",TEXTSPLIT(TRIM(A2)," "))

Then copy it down the source list. TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to one. The IF function leaves the result empty when the cleaned source has no text.

For example, a value with two leading spaces, three spaces between Maya and Lopez, and two trailing spaces still produces Maya and Lopez. A single name such as Socrates produces just one result. A blank cell or a cell containing only ordinary spaces produces an empty result.

TRIM doesn't remove every kind of invisible character. If text copied from a web page still won't split, it may contain nonbreaking spaces rather than ordinary spaces. Microsoft's TRIM reference explains this distinction. Clean those characters in the source before relying on a space delimiter.

Keep the first word and the rest in two columns

Sometimes you need exactly two output fields, even when a name contains several words. Use this rule: first word in one column, everything after the first space in another.

On another worksheet, enter these headers:

Put your original names in A2 downward. In B2, clean the spacing:

=TRIM(A2)

In C2, return the first word:

=IF(B2="","",TEXTBEFORE(B2," ",1,0,0,B2))

In D2, return everything after it:

=IF(B2="","",TEXTAFTER(B2," ",1,0,0,""))

Copy B2:D2 down. The helper column B lets you see the cleaned value that both formulas use.

TEXTBEFORE stops at the first space; TEXTAFTER starts after it. The 1 selects the first occurrence, and the two zeros retain the normal matching settings. The last argument specifies what to return when there is no space: C2 keeps the whole cleaned name, while D2 returns empty text. This avoids the default missing-delimiter #N/A error documented by Microsoft for TEXTBEFORE.

Here are the expected results. “Empty” means the formula displays nothing; don't type that word into the source cell.

For a list you have verified contains a single-word given name followed by the complete family name, you can label C and D First name and Last name. Until then, the literal labels are more useful. Lennon Sanchez might include a middle name, and Mary Ann might be a two-word given name. The formula has no evidence to settle either question.

What if you only want the last word?

On the same worksheet, put Last word in E1 and enter this in E2:

=IF(B2="","",TEXTAFTER(B2," ",-1,0,0,""))

Copy down. The -1 searches from the end, so E3 returns Sanchez. However, E5 returns only Cruz, losing de la. E4 stays empty because Socrates has no space. Microsoft's TEXTAFTER reference documents the backward search and fallback argument.

Use this version only when you really need the last space-separated word, or have confirmed that every family name in your source is one word. It won't reliably remove middle names while preserving multipart family names.

Split names stored as “Last, First”

A comma gives you a clearer boundary when the source consistently uses family name, given name. On a separate worksheet, enter Smith, Jordan in A2. Set B1 to Given name(s) and C1 to Family name.

In B2, extract the text after the comma:

=IF(TRIM(A2)="","",TRIM(TEXTAFTER(A2,",",1,0,0,"Review")))

In C2, extract the text before the comma:

=IF(TRIM(A2)="","",TRIM(TEXTBEFORE(A2,",",1,0,0,"Review")))

The results are Jordan and Smith. With de la Cruz, Ana in A3, copying the formulas down preserves de la Cruz together in C3. TRIM removes the space after the comma.

These formulas expect exactly one separating comma. A nonempty entry without a comma returns Review in both columns. Smith, leaves the given-name result empty, while , Jordan leaves the family-name result empty. Review missing parts and entries containing extra commas; the formula cannot supply missing information or distinguish a name separator from punctuation elsewhere.

Use Text to Columns for a one-time split

The desktop Text to Columns wizard is useful when you want separate values without keeping formulas. The results won't update when the original name changes.

On a fresh worksheet, put Maya Lopez in A2 and Dakota Lennon Sanchez in A3. Keep A as your original column, and leave B:D empty.

1. Select A2:A3 and choose Data → Text to Columns.
2. Choose Delimited, then Next.
3. Select Space and clear the other delimiters. Check the preview: Maya's name needs two columns, Dakota's needs three.
4. Select Next and set Destination to $B$2. Check that the entire output area is empty, because existing values there can be replaced.
5. Select Finish. B2:C2 should contain Maya and Lopez; B3:D3 should contain Dakota, Lennon, and Sanchez.

These steps follow Microsoft's Convert Text to Columns Wizard guide. For more on previewing fields and choosing column formats, see our Text to Columns tutorial.

For a consistently comma-separated Last, First list, select Comma instead of Space. For repeated spaces, clean the source first or use the wizard's Treat consecutive delimiters as one option if available, then inspect the preview. Neither choice decides which words make up a family name.

Choose the method that fits your list

Before importing the result into a contact list, check more than the first row. Include single names, names with several words, and empty fields in your review. A neatly filled column tells you the split worked; it doesn't establish that each person's name was divided correctly.