How do I split one column of text into multiple columns, like splitting a full name into first and last?
In Google Sheets, =SPLIT(A2," ") splits text by a delimiter (a space here) directly into adjacent cells. In Excel 365 or 2021 and newer, the equivalent is =TEXTSPLIT(A2," "). Older Excel versions do not have a formula for this; use the built-in Text to Columns tool instead.
Formula (Google Sheets: =SPLIT(A2, " "). Excel 365/2021+: =TEXTSPLIT(A2, " "). Older Excel: use Data > Text to Columns instead of a formula.)
=SPLIT(A2," ")Both SPLIT and TEXTSPLIT take a piece of text and a delimiter (the character to split on, like a space, comma, or hyphen) and spill the pieces across the cells to the right, automatically, without you needing to select a range in advance. =SPLIT(A2," ") in Google Sheets, or =TEXTSPLIT(A2," ") in modern Excel, both turn "Jane Smith" into "Jane" in one cell and "Smith" in the next.
If your Excel version does not include TEXTSPLIT (it was added in Excel 365 and Excel 2021), the built-in Text to Columns tool (Data > Text to Columns, choose Delimited, then pick the delimiter) does the same split without a formula, in every version of Excel and Google Sheets.
Step by step
- 1
Identify your delimiter
This is the character that separates the parts, such as a space in a full name or a comma in "city, state".
- 2
In Google Sheets, use SPLIT
=SPLIT(A2," ") spills the parts into the cells to the right.
- 3
In modern Excel, use TEXTSPLIT
=TEXTSPLIT(A2," ") does the same thing. If it is unavailable, use Data > Text to Columns instead.
- 4
Check for extra spaces before splitting
Run TRIM first if the source text has irregular spacing, so the split does not add empty pieces.
Common questions
Why does TEXTSPLIT show a #NAME? error?
TEXTSPLIT is only available in Excel 365 and Excel 2021 or newer. Use Text to Columns or upgrade to see the function.
Can I split on more than one delimiter, like a comma or a space?
Yes. Both SPLIT and TEXTSPLIT accept more than one delimiter character or, in TEXTSPLIT's case, an array of delimiters, splitting on whichever one appears.
Related formulas
How to Remove Extra Spaces and Hidden Characters with TRIM and CLEAN
RelatedHow to Combine Text from Multiple Cells with CONCATENATE and TEXTJOIN
RelatedHow to Calculate the Difference Between Two Dates with DATEDIF
From the blogHow to Make Sense of Your Spreadsheet Data Without Being an Analyst
Want a formula written for your exact spreadsheet, not a generic example?
Try the free formula generator