How to Use TEXTSPLIT Function in Excel Step-by-Step

Hello everyone! Today i will show you how to use TEXTSPLIT function in Excel step by step. TEXTSPLIT is available in Excel 2021 and Excel 365.

What is TEXTSPLIT Function in Excel?

The TEXTSPLIT function help you split any text string into multiple cells (across columns or down rows) using delimiter separator like comma ,, space " ", dash -, or slash /. In old Excel, you need use Text to Columns tool, but TEXTSPLIT formula do it automatically and update dynamically!

📚 Related Tutorial: Want to combine text from multiple cells together instead of splitting? Check out How to Use TEXTJOIN Function in Excel!

🛠️ Formula Syntax

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

• text: The text string or cell you want to split (example: A1).
• col_delimiter: Symbol to split text across columns (example: ",", " ", "-").
• [row_delimiter] (Optional): Symbol to split text down into rows.
• [ignore_empty] (Optional): Put TRUE to skip consecutive empty delimiters.
• [match_mode] (Optional): 0 for case-sensitive match (default), 1 for case-insensitive match.
• [pad_with] (Optional): Value to pad missing cells in 2D grid.

📊 Simple Examples

Example 1: Split text by comma into columns

=TEXTSPLIT(A1, ",")

If cell A1 contains "Apple,Orange,Banana", Excel splits it into 3 separate column cells:

Example 2: Split full name by space

=TEXTSPLIT(A1, " ")

"John Smith" ➔ Cell 1: John | Cell 2: Smith

Example 3: Split text into rows (Vertical Split)

Leave col_delimiter blank and use 3rd argument row_delimiter:

=TEXTSPLIT(A1, , ",")

If cell A1 contains "Apple,Orange,Banana", result splits vertically down into 3 rows:

Apple
Orange
Banana

Example 4: Split by multiple delimiters at same time

=TEXTSPLIT(A1, {",", ";"})

This formula splits text using both comma , and semicolon ;.

Example 5: Ignore empty blank values

=TEXTSPLIT(A1, ",", , TRUE)

Useful when text has extra consecutive commas like "Apple,,Orange,,,Banana". Setting ignore_empty to TRUE removes blank cells.

Example 6: Split date string by dash -

=TEXTSPLIT(A1, "-")

"2026-09-14" ➔ 2026 | 09 | 14

⚡ Modern Excel Text Functions Comparison

Here is quick guide comparing modern text formulas in Excel:

💡 Quick Tips for TEXTSPLIT:

1. TEXTSPLIT is dynamic array formula available in Microsoft 365 and Excel 2021+. Make sure surrounding cells are empty so it won't show #SPILL! error!
2. To split into rows instead of columns, leave 2nd argument (col_delimiter) empty and put separator in 3rd argument (row_delimiter).

📚 Related Resources & Tutorials

• How to Use TEXTJOIN Function in Excel
• How to Connect Text Values in Excel (CONCAT, &)
• How to Change Text Case (UPPER, LOWER, PROPER) in Excel
• Top 15 Basic Excel Formulas for Beginners ↗