
Split and Extract Text with TEXTSPLIT, TEXTBEFORE, and TEXTAFTER (No Regex Needed)
CSV files, file paths, product codes, email addresses—these three functions let you split and extract text cleanly without regex. The most common patterns are collected here so you can copy and paste them right away.
Core Concepts & Syntax
| Function | Syntax | Description |
|---|---|---|
| TEXTSPLIT | =TEXTSPLIT(text, col_delim, [row_delim], [ignore_empty], [match_mode], [pad_with]) | Splits a text string into an array |
| TEXTBEFORE | =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | Returns text before a delimiter |
| TEXTAFTER | =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | Returns text after a delimiter |
TEXTSPLIT Basics: Split by Delimiter
Example 1) Split into columns with a hyphen (-)
=TEXTSPLIT(A2,"-")
Example 2) CSV with commas followed by spaces
=TEXTSPLIT(A2,", ") // Treats ", " as one delimiter
Example 3) Split vertically by line breaks
=TEXTSPLIT(A2,,CHAR(10)) // Omit col_delim; row_delim=line break
Consecutive Delimiters, Case Sensitivity, and Multiple Delimiters
Example 4) Ignore blank values from consecutive delimiters
=TEXTSPLIT(A2,",",,TRUE)
Example 5) Recognize multiple delimiters at once
=TEXTSPLIT(A2,{",",";","|"})
Example 6) Ignore case (match_mode=1)
=TEXTSPLIT(A2,"abc",,FALSE,1)
TEXTBEFORE/AFTER: nth Occurrence and Last Occurrence
Example 7) Email domain (after the first ‘@’)
=TEXTAFTER(A2,"@")
Example 8) File extension (after the last ‘.’)
=TEXTAFTER(A2,".",-1)
Example 9) Parent folder in a path (before the last ”)
=TEXTBEFORE(A2,"\",-1)
Example 10) Before/after the nth hyphen
=TEXTBEFORE(A2,"-",N)
=TEXTAFTER(A2,"-",N)
- You can provide a default value with if_not_found. For a simple option, use
IFERROR(TEXTAFTER(...),"Not found"). - When match_end=1, the end of the text is treated as a delimiter.
Convert Rows/Columns & Extract the nth Token
Example 11) Turn a horizontal split result into a vertical one
=TRANSPOSE(TEXTSPLIT(A2,"-"))
Example 12) Extract only the nth token
=INDEX(TEXTSPLIT(A2,"-"), N)
Example 13) Wrap into rows of up to three items
=WRAPROWS(TEXTSPLIT(A2,", "), 3, "")
Formulas for Older Excel Versions
In environments without the TEXT functions, such as older Excel versions, use the following patterns.
Example 14) Text after the last delimiter
=RIGHT(A2, LEN(A2)-FIND("|", SUBSTITUTE(A2,"-","|", LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))))
Example 15) Between two delimiters (middle code)
=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
Example 16) Text to Columns (Ribbon)
Use Data → Text to Columns for a one-time split, or use the Split Column feature in Power Query.
Mistakes, Exceptions & Performance Checklist
- Blank cells from consecutive delimiters →
ignore_empty=TRUE. - Case sensitivity →
match_mode=1, or standardize the case with UPPER/LOWER. - Variable-length rows → Fill blanks with
pad_with, or arrange into a grid withWRAPROWSorWRAPCOLS. - Process a range at once → Apply TEXTSPLIT by row with BYROW/MAP.
- Performance → Cache repeated calculations with
LET, and avoid entire-column references such as X:X.
Summary
| Goal | Main Formula |
|---|---|
| Split all text | TEXTSPLIT(A2,"-") |
| Before/after the nth occurrence | TEXTBEFORE/AFTER(A2,"-",N) |
| Based on the last occurrence | TEXTBEFORE/AFTER(A2,"-", -1) |
| Ignore consecutive delimiters | TEXTSPLIT(A2,",",,TRUE) |
FAQ
How do I split multi-line text into rows?
Use =TEXTSPLIT(A2,,CHAR(10)), or use TRANSPOSE as in =TRANSPOSE(TEXTSPLIT(A2,"-")).
How do I handle blanks or missing values?
Use IFERROR(TEXTAFTER(...),"Not found"), or specify a default fill value with TEXTSPLIT’s pad_with.