Split Text Into Columns With Excel Formulas
Split Text With Formulas is a Excel function that textsplit (excel 365) splits text at the specified delimiter and spills the results across columns. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Extract first names, last names, domains, or any delimited data using formulas instead of the Text to Columns wizard.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.