How to Split Names in Excel Into Two Cells

How-to basics · Updated November 20, 2026

To split names in Excel into two cells, select the name column and choose Data > Text to Columns, then select Delimited and set the delimiter to a space. If you need to split each name only once—for example, keep a middle name with the rest—use TEXTBEFORE and TEXTAFTER formulas instead.

Use Text to Columns for a quick split

Text to Columns is the quickest choice when your list follows a consistent pattern and you want to separate existing values into adjacent columns. In Microsoft Excel for Microsoft 365 on Windows, first make sure the column to the right of your names is empty: the split results can fill it, and any content there could be overwritten.

  1. Select the cells containing the names, such as A2:A100. You can select the whole column if it contains only the data you want to split.
  2. Open Data > Text to Columns in the ribbon.
  3. In the Convert Text to Columns Wizard, choose Delimited, then select Next.
  4. Check Space in the Delimiters list. If names contain repeated spaces, select Treat consecutive delimiters as one.
  5. Check the preview. Set a destination, such as $B$2, if you want to preserve the original names in column A. Select Finish.

A value such as “Jordan Lee” becomes “Jordan” and “Lee” in separate columns. The delimiter is the character Excel uses to decide where to divide each value. A space is suitable for names separated by spaces; if the source uses commas, tabs, or another separator, select that delimiter instead.

Excel separates at every matching delimiter, not just the first one. “Jordan Alex Lee” may therefore occupy three output columns. This is useful when you want every word in its own column, but it is not the right choice if the goal is exactly two cells per name. For that, use a formula that splits at the first or last space.

Split at the first space with formulas

Formulas are useful when the original names should remain intact, when you want the results to update automatically, or when you need exactly two output columns. Put the original names in A2:A20, with the first output in B2 and the second in C2. These Microsoft 365 formulas split at the first space and keep all remaining words together in the second cell.

With names in A2:A20 and the first output in B2, enter this formula to return the first word, or the whole value if there is no space:

=IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2))

With names in A2:A20 and the second output in C2, enter this formula to return everything after the first space, or a blank if there is no space:

=IFERROR(TEXTAFTER(TRIM(A2)," "),"")

Copy both formulas down the rows. For “Jordan Alex Lee,” the results are “Jordan” and “Alex Lee.” For “Jordan Lee,” they are “Jordan” and “Lee.” For “Jordan,” the first formula returns “Jordan” and the second returns a blank. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one, which helps avoid empty-looking results caused by messy spacing.

TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. In Excel for the web, the same functions can be used if they are available in the account’s Excel version. If a formula using them is not recognized, try Text to Columns or the compatible formulas below.

Choose whether to split at the first or last space

A two-cell split does not have one universally correct meaning. Splitting at the first space puts the first word in one cell and everything after it in the other. That can suit a dataset where the first word is a given name and the remaining words are meant to stay together. It does not necessarily identify a person’s full given name and family name.

For example, splitting “Mary Ann van Buren” at the first space produces “Mary” and “Ann van Buren.” Splitting at the last space instead produces “Mary Ann van” and “Buren.” Neither result correctly identifies every name component by itself; the appropriate split depends on how the data is defined and what you need to do with it.

If your data treats the final word as the surname and everything before it as the given-name portion, use a last-space split. With names in A2:A20 and the first output in B2, this formula returns everything before the final space, or the whole name if there is no space:

=IFERROR(TEXTBEFORE(TRIM(A2)," ",-1),TRIM(A2))

With names in A2:A20 and the second output in C2, this formula returns the final word, or a blank if there is no space:

=IFERROR(TEXTAFTER(TRIM(A2)," ",-1),"")

For “Mary Ann van Buren,” this gives “Mary Ann van” and “Buren.” That is a mechanical split, not a guarantee that “Buren” is the correct family-name field for every naming convention. Check the intended data rules before using split columns for sorting, matching, mail merges, or reports.

Use formulas in older Excel versions

If your version does not recognize TEXTBEFORE or TEXTAFTER, older functions can split at the first ordinary space. With names in A2:A20 and the first output in B2, use:

=IFERROR(LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1),TRIM(A2))

With names in A2:A20 and the remaining text in C2, use:

=IFERROR(MID(TRIM(A2),FIND(" ",TRIM(A2))+1,LEN(TRIM(A2))),"")

Fill the formulas down as you would with the newer functions. They use FIND to locate the first space, then return either the text before it or the text after it. The IFERROR wrapper handles a one-word name without a space. These formulas also rely on ordinary spaces; they do not automatically clean every unusual character that may arrive from a website or external file.

Try Flash Fill for a one-time result

Flash Fill can recognize a pattern from examples and fill a neighboring column without leaving formulas in the result. It is handy for a small, one-off cleanup when the names have a predictable format. For example, type “Jordan” beside “Jordan Lee,” then type the next expected first name on the following row. Select the cells where you want the results and choose Data > Flash Fill. You can also try the Windows shortcut Ctrl+E.

To create the second column, provide examples of the remaining text, such as “Lee” beside “Jordan Lee,” and run Flash Fill again. Review the output rather than assuming the pattern is right: names with middle names, initials, suffixes, or inconsistent punctuation can cause Excel to infer an unwanted rule. Flash Fill produces values rather than a live formula, so later edits to the source names will not automatically update its results.

Check spacing, punctuation, and unusual names

Most split problems come from variation in the source data rather than from the command itself. Before splitting a large list, inspect a representative sample, including the shortest and most complicated entries. Look for blank cells, leading or trailing spaces, repeated spaces, initials, hyphens, apostrophes, commas, and suffixes such as “Jr.”

  • Repeated spaces: Text to Columns can treat consecutive delimiters as one. Formula examples above use TRIM to normalize ordinary repeated spaces.
  • Middle names or multiple-word surnames: Decide whether the second cell should contain the remainder of the name or only the final word. A basic delimiter split cannot infer a person’s naming structure.
  • Comma-separated names: If the source is written as “Lee, Jordan,” use a comma as the Text to Columns delimiter, or adapt a formula to the comma. The order is still determined by the source format.
  • Nonbreaking spaces: Some copied data uses a space-like character that ordinary TRIM may not remove. If a split fails despite visible spaces, clean or replace the source separator first.
  • Blank rows: Test how blanks appear in the results and avoid filling formulas far beyond the data unless you want empty output rows.

Do not assume every person has exactly two name components. A name split is a text operation, not a name-recognition feature. When accuracy matters, preserve the original column and review exceptions against a reliable source rather than silently discarding part of a name.

Preserve the source and turn formulas into values

Before using Text to Columns, consider copying the original names to a backup column or worksheet. Text to Columns can write into cells beside the source, and choosing a destination over the source area can replace the original values. A copy also makes it easier to compare the split with the input and reverse a mistake.

Formula results recalculate when their source cells change. If you need fixed results—for example, to send a clean file to someone who should not see formulas—copy the output columns and use Home > Paste > Paste Values. Confirm that the pasted values look right before deleting or hiding the source column. Keep the original names if they may be needed for future corrections, matching, or audit checks.

For a recurring import, formulas can be easier to refresh than a manual split, provided the input layout remains consistent. For a one-time list with simple, uniform names, Text to Columns may take less setup. Either way, check the destination cells first and verify a few results—including edge cases—before using the output downstream.

Frequently Asked Questions

How do I split a full name into first and last name in Excel?
For a simple two-word name, use Data > Text to Columns with Space as the delimiter. If names can include middle names, use formulas that split at the first or last space according to your data rules, then review the results. Excel cannot reliably determine name parts from text alone.

How can I split a name into only two columns when it has a middle name?
Use the first-space formulas above to put the first word in one column and all remaining words in the other. Alternatively, use the last-space formulas to put the final word in one column and everything before it in the other. Choose based on the meaning you need for the two fields.

Why does Text to Columns create more than two columns?
When Space is selected as the delimiter, Excel splits at each space. A three-word name can therefore produce three fields. Use a formula that splits at one occurrence of the space when you need exactly two output cells.

Can I split names without changing the original column?
Yes. With formulas, put the results in empty columns beside the source. In Text to Columns, set a destination in an empty area before selecting Finish. Keeping the source is useful for checking and correcting the results.

Will the split update if I edit the original name?
Formula results update when their source value changes. Text to Columns and Flash Fill create values, so you need to run the operation again to reflect later edits.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.