How to Split Full Names into First and Last in Excel

How to Split Names Online (Every Sheet at Once)

1. Open the free Split Text to Columns tool and drop in your .xlsx, .xlsm, or .xls workbook. Processing is fully local — the file is never uploaded.
2. Confirm the column letter that holds the names (A, B, C…). The same column is processed in every sheet of the workbook.
3. Pick the delimiter: space, comma, semicolon, pipe, tab, or a custom literal. A multi-character sequence such as “ -> ” is matched as a whole.
4. Choose the mode: Insert adds new columns and shifts everything on the right; Overwrite replaces the neighbouring cells without widening the sheet.
5. Keep “First row is a header” ticked (the default) so titles like “Full Name” are never split, then click Split text to columns.
6. Review the per-sheet report and download the result as your-file-name-split-columns.xlsx.

• Split Text into Columns in ExcelThe free online tool used in the steps above.

Insert vs Overwrite: Picking the Safe Mode

Both modes read the same source column but write the result differently. If column B holds “red,green,blue” in a three-column sheet and is split on commas:

Overwrite mode exists for sheets where nothing lives to the right of the source column — splitting “First Last” in column A of a name-only list, for example. The moment any column to the right holds data you need, use Insert mode.

For a two-part name in column A of an empty-ish sheet, Overwrite is clean: the first name replaces A, the last name lands in B. For a CRM export with an email column in B, Insert is the only safe choice.

Manual Method: Text to Columns Inside Excel

1. Select the column of full names.
2. Go to Data → Text to Columns, choose Delimited, and click Next.
3. Tick Space (for “Ada Lovelace” style names) or Comma (for “Lovelace, Ada” imports), then Finish.

Text to Columns always overwrites — cells to the right of the selection are replaced without asking. The wizard also works on exactly one column of one sheet per run: a five-sheet workbook with a names column on each sheet means five wizard passes, five decisions about delimiters, and five chances to eat the wrong column.

The wizard’s “Other” box accepts one single character — it cannot split on a multi-character delimiter such as “ - ” or “, ” with a trailing space. Those imports need the online tool or formulas.

The Formula Method, and When It Is Worth It

Formulas produce a live split that updates when the source cell changes — the only reason to prefer them over a one-time split:

• Excel 365/2021: =TEXTBEFORE(A2," ") for the first name, =TEXTAFTER(A2," ") for the last.
• Older Excel: =LEFT(A2,FIND(" ",A2)-1) for the first name; =MID(A2,FIND(" ",A2)+1,100) for the last.
• Every variant needs a spare column pair and a copy-paste-as-values pass to freeze the results.

One honest limitation no method escapes: a split is mechanical. “Mary Jane Watson” becomes “Mary” + “Jane Watson” on a space delimiter — middle names need two passes (split once, then split the remainder again), and multi-part surnames like “Van Der Berg” need a judgement call no tool can make for you. After splitting imported data, a cleanup pass on stray spaces is usually next.

• Find and Replace in ExcelFix abbreviations, dots, or casing across the split result.

Which Method Fits Which Situation