Split Text Into Columns With Excel Formulas
Extract first names, last names, domains, or any delimited data using formulas instead of the Text to Columns wizard.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Split a full name into first name and last name"
=TEXTSPLIT(A2," ")TEXTSPLIT (Excel 365) splits text at the specified delimiter and spills the results across columns. For a full name with a space delimiter, it returns first name in one cell and last name in the next.
Step-by-step breakdown
- A2 contains the full name (e.g., "John Smith")
- " " is the delimiter — split at the space character
- Results spill horizontally: first name in the current cell, last name in the next
- For older Excel: =LEFT(A2,FIND(" ",A2)-1) gets the first name
- For older Excel: =MID(A2,FIND(" ",A2)+1,100) gets the last name
Edge cases & warnings
- Names with multiple spaces (e.g., "Mary Jane Watson") split into 3+ columns
- No space in the text returns the full text in one cell
- TEXTSPLIT requires Excel 365 — use LEFT/MID/FIND for older versions
- Leading or trailing spaces can cause empty result cells — use TRIM first
Examples
""John Smith""
"John" | "Smith"""Mary Jane Watson""
"Mary" | "Jane" | "Watson"Frequently asked questions
Does TEXTSPLIT work in Google Sheets?
Google Sheets uses SPLIT() instead: =SPLIT(A2," "). Same concept, different function name.
How do I split by comma instead of space?
Change the delimiter: =TEXTSPLIT(A2,","). For CSV data, this extracts each field into its own column.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.