CONTENTS

Excel TEXTSPLIT, TEXTBEFORE, and TEXTAFTER Complete Guide

To automate repetitive text splitting, Excel TEXTSPLIT is the fastest option. Instead of manually separating compound strings such as CSV data or “Name(Department)-Email,” combine it with TEXTBEFORE/TEXTAFTER to normalize them in seconds.

Quick Fix (3-Minute Process)

Identify the delimiter type (for example, ,, -, |, spaces, or parentheses).
Start with a formula such as =TEXTSPLIT(A2, {",","-"," | "}).
Use ignore_empty=TRUE to remove empty items caused by consecutive delimiters.
Use row_delimiter/col_delimiter to control vertical or horizontal output.
Post-process with TRIM/CLEAN/SUBSTITUTE, and rearrange with HSTACK/VSTACK.
If #SPILL! occurs, clear the surrounding cells and unmerge any merged cells.

Core Concepts

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Specify multiple delimiters using an array constant such as {",","-"," ("}.
Use ignore_empty to control empty items and match_mode for case sensitivity or wildcards.
Use pad_with to fill missing values.

TEXTBEFORE/TEXTAFTER Essentials

TEXTBEFORE: Use instance_num to select the Nth delimiter; match_end=1 treats the end of the text as a delimiter.
TEXTAFTER: Uses the same structure to return the text after the delimiter.

Practical Examples

Example 1: Split name, department, and email

B2: =TEXTBEFORE(A2, "(")
C2: =TEXTBEFORE(TEXTAFTER(A2, "("), ")")
D2: =TEXTAFTER(A2, "-")

Example 2: TEXTSPLIT in one formula

=LET(s,A2, TXT, TEXTSPLIT(s, {"-","(",")"}), HSTACK(INDEX(TXT,1), INDEX(TXT,2), INDEX(TXT,3)))

Example 3: Mixed delimiters and empty-item removal

=TEXTSPLIT(A2, {"/","|",","}, , TRUE)

Example 4: File name based on the last delimiter

=TEXTAFTER(A2, "", -1)
=TEXTBEFORE(TEXTAFTER(A2,"",-1),".")

Example 5: Spill vertically

=TEXTSPLIT(A2, , ",")

Advanced Patterns

=TEXTSPLIT(A2, {",","|"," - "}, , TRUE, 1)    // Ignore case
=TEXTSPLIT(A2, ",", , FALSE, 0, "N/A")            // Keep empty items + padding
=LET(arr, TEXTSPLIT(A2, ","), VSTACK(TAKE(arr,,2), DROP(arr,,2)))

Alternatives and Considerations

Text to Columns (Data > Text to Columns): Simple, but not dynamic.
Power Query: Powerful for large-scale and complex processing, but requires initial setup.
Alternatives for older versions: Combine LEFT/RIGHT/MID/FIND/SUBSTITUTE (difficult to maintain).

Troubleshooting

Symptom	Cause	Solution
#SPILL!	Output range is blocked or contains merged cells	Clear surrounding cells, unmerge cells, or use the formula outside a table
Empty values appear	Consecutive delimiters	`ignore_empty=TRUE`, then post-process with `FILTER`
No match	Exact match is the default	Adjust with `match_mode=1/-1`
Rows and columns are reversed	Confusion between row and column delimiters	Check the argument order
Unexpected spaces	CHAR(160), etc.	`SUBSTITUTE` → `TRIM/CLEAN`

Conclusion and Related Posts

You can now structure text strings instantly with the TEXTSPLIT family of functions. For automating conditional calculations and date handling, see the posts below.

엑셀 초보 에러 해결(8대 오류 한 번에 끝내기)

Excel TEXTSPLIT, TEXTBEFORE, and TEXTAFTER Complete Guide: Automate Text Splitting