Text formula page
Split Text Formula | TEXTSPLIT and SPLIT
Use TEXTSPLIT in Excel or SPLIT in Google Sheets when a product code, quantity label, name list, or imported value contains a consistent delimiter.
Split text into columns by comma, dash, space, or another delimiter in Excel or Google Sheets.
SKU-1001-East returns SKU, 1001, and East. A space-delimited value such as 2 Adults 12 Children returns four cells and keeps 12 as one value.
Copy formulas
=TEXTSPLIT(A2, "-")=SPLIT(A2, "-")Excel 365 and Excel 2024 use TEXTSPLIT, while Google Sheets uses SPLIT. Both spill results into adjacent cells. Excel uses ignore_empty to skip repeated delimiters; Google Sheets uses split_by_each and remove_empty_text.
Example data
| Source in A2 | Delimiter | Returned cells |
|---|---|---|
| SKU-1001-East | Dash | SKU | 1001 | East |
| 2 Adults 12 Children | Space | 2 | Adults | 12 | Children |
| North Warehouse | Space | North | Warehouse |
| Red<>Blue<>Green | <> | Red | Blue | Green |
SKU-1001-East returns SKU, 1001, and East. A space-delimited value such as 2 Adults 12 Children returns four cells and keeps 12 as one value.
How the formula works
- A2 is the source text to split.
- The dash in quotes is the delimiter that marks each break point.
- Replace the dash with a comma, space, slash, or the exact delimiter used in the source data.
- A multi-digit value such as 12 remains one result because the formula splits at delimiters, not between individual characters.
- TRIM plus the empty-result option prevents repeated spaces from creating unwanted blank output cells.
- The result spills into adjacent cells, so those cells must be empty.
| Syntax piece | Role in the formula |
|---|---|
| A2 | The source text, such as SKU-1001-East or 2 Adults 12 Children. |
| "-" or " " | The exact delimiter marking each place where the source should be separated. |
| ignore_empty | Excel TEXTSPLIT uses TRUE to skip empty results caused by repeated delimiters. |
| split_by_each, remove_empty_text | Google Sheets uses FALSE to treat a multi-character delimiter as one token and TRUE to omit empty results. |
| Spill range | The empty cells to the right that receive each returned segment. |
Verified examples
=TEXTSPLIT(A2, "-")Excel: Enter SKU-1001-East in A2 and leave the cells to the right empty. Returns: SKU, 1001, and East in three adjacent cells
=SPLIT(A2, "-")Google Sheets: Enter SKU-1001-East in A2. Returns: SKU, 1001, and East in three adjacent cells
=TEXTSPLIT(TRIM(A2), " ",, TRUE)Excel: Enter 2 Adults 12 Children in A2. Returns: 2, Adults, 12, and Children in four adjacent cells
=SPLIT(TRIM(A2), " ", FALSE, TRUE)Google Sheets: Enter North followed by two spaces and Warehouse in A2. Returns: North and Warehouse in two adjacent cells
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| Excel returns #SPILL! | One or more cells required by the split output already contain data. | Clear the adjacent output cells or move the formula to an empty area. |
| Repeated spaces create blank or misaligned results | The source contains consecutive spaces and the formula preserves empty segments. | Use =TEXTSPLIT(TRIM(A2)," ",,TRUE) in Excel or =SPLIT(TRIM(A2)," ",FALSE,TRUE) in Google Sheets. |
| A value such as Name00 stays in one cell | There is no delimiter between the letters and numbers, so TEXTSPLIT or SPLIT has no break point. | Use a number-extraction formula or add a reliable delimiter before splitting. Do not assume a fixed number of letters. |
When not to use this formula
- Do not use TEXTSPLIT or SPLIT when the source has no reliable delimiter, such as Name00; use pattern-based extraction instead.
- Do not use a simple split formula as a quoted-CSV parser because commas inside quoted values need CSV-aware parsing.
- Do not assume every person name has exactly two space-delimited parts; compound surnames and middle names need a defined business rule.
Alternatives
| Alternative | When to use it |
|---|---|
| Extract Numbers from Text | Use when letters and numbers touch without a delimiter, such as Name00 or INV2048. |
| Combine Cells Formula | Use when split pieces need to be joined again with commas, spaces, or line breaks. |
Related formulas
Official references
- TEXTSPLIT function from Microsoft
- SPLIT function from Google
FAQ
How do I split words and numbers separated by spaces?
Use =TEXTSPLIT(TRIM(A2)," ",,TRUE) in Excel or =SPLIT(TRIM(A2)," ",FALSE,TRUE) in Google Sheets. A value such as 2 Adults 12 Children returns 2, Adults, 12, and Children.
Why does Name00 not split into Name and 00?
TEXTSPLIT and SPLIT need a delimiter. Name00 has no separator, so use a number-extraction formula or introduce a reliable delimiter first.
How do I split with a multi-character delimiter in Google Sheets?
Use SPLIT(A2,"<>",FALSE,TRUE). FALSE tells Google Sheets to treat <> as one delimiter instead of splitting on each character.
Can I split a full name into first and last name?
You can split a name into space-delimited tokens, but that does not reliably identify first and last names. Middle names, prefixes, and compound surnames need a defined rule before you separate them.