Split and Extract Text with TEXTSPLIT, TEXTBEFORE, and TEXTAFTER (No Regex Needed)

Split and extract text cleanly with Excel TEXTSPLIT, TEXTBEFORE, and TEXTAFTER. A complete collection of practical examples, including multiple delimiters, blank handling, the last delimiter, the nth item, row/column conversion, and formulas for older versions.
Split and Extract Text with TEXTSPLIT, TEXTBEFORE, and TEXTAFTER (No Regex Needed)

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

FunctionSyntaxDescription
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 with WRAPROWS or WRAPCOLS.
  • 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

GoalMain Formula
Split all textTEXTSPLIT(A2,"-")
Before/after the nth occurrenceTEXTBEFORE/AFTER(A2,"-",N)
Based on the last occurrenceTEXTBEFORE/AFTER(A2,"-", -1)
Ignore consecutive delimitersTEXTSPLIT(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.

Enter hyphenated data such as “code-name-version” and try applying TEXTSPLIT → INDEX. Even with complex rules, these three functions make it quick.

Leave a Reply

Your email address will not be published. Required fields are marked *