To split names in Excel, select the name cells and choose Data > Text to Columns > Delimited, select Space, choose an empty destination, and click Finish. This works well when each name has exactly two parts; for names with middle names or other spaces, use Flash Fill or a formula so you control what goes in each of the two result cells.
Choose what the two cells should contain
“Split a name into two cells” can mean different things. You might want a first name and a last name, or a first name and everything after it. Those are not always the same: if a cell contains “Mary Ann Smith,” a split at the first space gives “Mary” and “Ann Smith,” while a split at the last space gives “Mary Ann” and “Smith.” Decide which result you need before splitting a whole list.
For a list where every entry is a simple two-part name, Text to Columns is usually the quickest option. If the entries vary, formulas are easier to review and update. Flash Fill can recognize a pattern from examples, but its results are fixed values rather than formulas. Keep the original name column until you have checked the results.
Split two-part names with Text to Columns
Text to Columns divides text at a delimiter, such as a space, and places the pieces in neighboring columns. In Excel for Microsoft 365 on Windows, use these steps:
- Make sure the columns to the right of the names are empty, or choose a different destination in step 5.
- Select the name cells in one column. You can include the header if you want, but exclude it if you do not want Excel to split the header.
- Choose Data > Text to Columns.
- Select Delimited, then click Next. Select Space as the delimiter and check the preview.
- Set Destination to an empty starting cell, such as B2, if you want to preserve the source in column A.
- Click Finish and inspect the resulting columns.
For example, “Jordan Lee” becomes “Jordan” and “Lee.” The important limitation is that Text to Columns splits at every selected space, not just the first or last one. “Jordan Avery Lee” therefore produces three pieces. If you have only two available output columns, later pieces can overwrite existing data, so use an empty area and check the preview before finishing. On Mac, the Text to Columns command is also on the Data tab in current desktop Excel; if you are using Excel for the web and do not see the command, use a formula or open the workbook in desktop Excel.
Use formulas to split at the first space
Formulas are useful when you want the result to update whenever the original name changes. They also let you define the second result as the remainder of the name rather than as just the next word. The following formulas use the first space as the dividing point and trim extra spaces at the ends.
With the full name in A2, enter this in B2 to return the first word; for “Mary Ann Smith,” the result is “Mary.”
=IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2))
With the full name in A2, enter this in C2 to return everything after the first space; for “Mary Ann Smith,” the result is “Ann Smith.” A single-word name returns a blank in C2.
=IFERROR(TEXTAFTER(TRIM(A2)," "),"")
Fill both formulas down the rows by dragging the fill handle or double-clicking it. TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. If a workbook must be opened in an older version that does not support them, use the older-function alternatives below. These formulas split at the first space, so they do not automatically identify a person’s legal or preferred given and family names.
Split at the last space for a different result
If you want the final word in one cell and everything before it in the other, split at the last space instead. This can suit entries where the last word is the family name, but it is still a rule about position, not a reliable way to interpret every naming convention. For “Mary Ann Smith,” it returns “Mary Ann” and “Smith.”
With the full name in A2, enter this in B2 to return all text before the last space; a single-word entry is returned intact.
=IFERROR(TEXTBEFORE(TRIM(A2)," ",-1),TRIM(A2))
With the full name in A2, enter this in C2 to return the final word; a single-word entry returns a blank.
=IFERROR(TEXTAFTER(TRIM(A2)," ",-1),"")
The negative instance number tells these functions to find the last matching space. Use these formulas only when that is the desired split rule. For example, a compound family name may contain more than one word, so the last word alone might not be the complete family name.
Use older formulas when TEXTBEFORE is unavailable
In older Excel versions, LEFT, FIND, MID, LEN, and TRIM can split at the first space. The formulas below return the first word and then the remaining text. They handle a name with no space by returning the full name in the first result and a blank in the second.
With the full name in A2, enter this in B2 to return the text before the first space, or the entire trimmed entry if there is no space.
=LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1)
With the full name in A2, enter this in C2 to return the text after the first space, or a blank if there is no space.
=TRIM(MID(TRIM(A2),FIND(" ",TRIM(A2)&" ")+1,LEN(TRIM(A2))))
These formulas calculate from the source cell, so you can copy them down the list. If you need to keep only the results, copy the output columns and use Home > Paste > Paste Values in a different location. That replaces formulas with their current displayed values; keep the original data if you may need to revise the split later.
Use Flash Fill when examples reveal the pattern
Flash Fill can fill a column by recognizing a pattern you demonstrate. It is handy when names do not follow a simple delimiter rule, or when you want to split based on a specific example. It does not create a live link to the source, so changes to the original names will not automatically update the filled results.
- Keep the full names in column A and type the intended first result for the first row in B2.
- Type the intended second result in C2, if you want two output columns.
- Start typing the next example in B3. When Excel suggests the rest of the column, review the preview and accept it. You can also select the cells to fill and choose Data > Flash Fill.
- Repeat for the other output column, checking several varied rows rather than accepting a pattern without review.
On Windows, Ctrl+E runs Flash Fill when the feature is available. On Mac, use Data > Flash Fill rather than assuming the Windows shortcut applies. Flash Fill is best for a consistent list with recognizable examples; it may guess incorrectly when a list mixes initials, titles, middle names, or inconsistent spacing.
Handle spaces, middle names, and unusual entries
Names are not always two words separated by one ordinary space. Some records have leading or trailing spaces, repeated spaces, middle names, initials, titles, suffixes, or family names made of multiple words. A space-based split cannot determine the correct human interpretation of those cases by itself. For example, “Ana María de la Cruz” should not necessarily be reduced to “Ana” and “Cruz.”
- Extra spaces: TRIM in the formulas removes leading and trailing spaces and reduces repeated ordinary spaces between words to one. It does not remove every unusual whitespace character, such as a nonbreaking space copied from some websites.
- Middle names: Decide whether the second cell should contain the middle name too. A first-space split keeps the remaining words together; Text to Columns separates each word into its own column.
- Titles and suffixes: A title such as “Dr.” or a suffix such as “Jr.” may make a two-part split inappropriate. Review such rows individually or define a consistent rule for the dataset.
- Single-word entries: Formula options above keep the word in one result and leave the other blank. Check that this is acceptable for your use.
If results look unchanged after a formula is entered, check that the cell is not formatted as Text; change it to General and re-enter the formula. Also confirm that the formula refers to the correct source row and that there is no apostrophe before the equals sign. When importing a list, clean or review the source before relying on a split for mailing, sorting, or matching records.
Choose a method and verify the results
Use Text to Columns for a quick, one-time split when every entry follows the same two-part format. Choose formulas when results should recalculate or when you need a clear first-space or last-space rule. Choose Flash Fill when you can show Excel the intended pattern and are comfortable reviewing static results. For a list that contains varied name structures, no automatic method can replace checking the records against a trusted source.
Before using the output, inspect examples from the top, middle, and bottom of the list, plus any rows with more than one space, no space, initials, or suffixes. Confirm that the output columns did not overwrite other data, that the headers stayed intact, and that the source column is still available if needed. If you sort or filter the data later, keep each person’s related fields in the same row.
Frequently Asked Questions
Can Excel split a full name into first and last name automatically?
Yes, if the names all follow a predictable pattern. Text to Columns splits at every space; formulas can split at the first or last space. Neither method can reliably decide which words are given names or family names in every naming convention, so review exceptions.
How do I split a name into two columns but keep the middle name?
Use a first-space split. The first output contains the first word, and the second contains the remainder, including any middle name. The TEXTBEFORE and TEXTAFTER formulas above do this in Microsoft 365 and Excel 2024.
Will the split update if I edit the original name?
Formulas update when their source cell changes. Text to Columns and Flash Fill create values, so they do not automatically recalculate from an edited source.
Can I split names without changing the original column?
Yes. With Text to Columns, set Destination to an empty cell before finishing. With formulas or Flash Fill, place the results in separate empty columns and leave the source column in place.
Why did Text to Columns create more than two columns?
Because Space was selected as a delimiter, Excel split at each space. Names with middle names or multiple-word family names therefore produce extra pieces. Use formulas that split at just the first or last space, or review the extra pieces before using the results.
Related
- Excel Remove Duplicates While Keeping the Oldest Date
- Excel Remove Slicer: How to Delete or Clear One
- Excel Remove Everything but Duplicates: Keep Repeats
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Number Data Validation ExplainedYes. Google Sheets data validation can require a cell to contain a number that meets a rule, such…
- Can You Search in an Excel Slicer? How to Find ItemsYes. In Excel for Microsoft 365 on Windows, you can search the items shown in a slicer by…
- Excel Shortcut for Inserting Rows Above or BelowIf you mean a worksheet row, select the row where the new one should go and press Ctrl+Shift+Plus…
- How to Print Timestamps from Google Sheets ClearlyTo print timestamps from Google Sheets, format the cells as date and time, select the sheet or range…
- Excel Freeze Panes: Why the Option Is UnavailableIf Excel will not let you freeze panes, first press Esc to exit cell-editing mode, then click a…
- Flip Paste in Excel: Turn Rows Into Columns EasilyTo flip-paste a range in Excel for Microsoft 365 on Windows, select and copy the cells, click a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.