Summary
The Excel TEXTSPLIT function allows you to split text strings into an array of elements, using specified delimiters for columns and rows. This function is akin to performing the Text-to-Columns feature programmatically within a formula.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
- text: The text you want to split. Required.
- col_delimiter: The text that marks where to split the text across columns.
- row_delimiter: [Optional] The text that marks where to split the text down rows.
- ignore_empty: [Optional] Specify TRUE to ignore consecutive delimiters and avoid creating empty cells.
- match_mode: [Optional] 0 for case-sensitive (default), 1 for case-insensitive.
- pad_with: [Optional] The value used to fill any unfilled spaces.
Return value
Array of text values split based on the specified delimiters.
How to use
TEXTSPLIT is versatile for splitting text based on delimiters and can be used for data parsing, formatting, and preparation tasks. It can split text both horizontally and vertically, offering a high degree of flexibility.
Examples
Simple TEXTSPLIT
Splitting Names Separated by Commas: To split a list of comma-separated names into separate cells:
=TEXTSPLIT("John, Jane, Doe", ",")
This formula splits “John, Jane, Doe” into three separate cells horizontally.
TEXTSPLIT with Multiple Delimiters
Using Different Delimiters for Rows and Columns: Splitting text using different delimiters for rows and columns:
=TEXTSPLIT("Jan-2020,Feb-2020;Mar-2020,Apr-2020", ",", ";")
This splits the text first by semicolons into rows and then by commas into columns.
TEXTSPLIT Ignoring Empty Cells
Omitting Empty Cells Between Delimiters: Ignoring consecutive delimiters to avoid empty cells:
=TEXTSPLIT("apple,,banana", ",", TRUE)
This only returns “apple” and “banana”, skipping the empty cell.
TEXTSPLIT with Case Sensitivity
Case-Insensitive Splitting: Splitting text without case sensitivity:
=TEXTSPLIT("One,two,Three", ",",, 1)
This splits “One,two,Three” into separate cells regardless of case differences.
TEXTSPLIT with Custom Padding
Custom Padding for Uneven Splits: Using custom padding for splits resulting in uneven arrays:
=TEXTSPLIT("Red=10,Blue=15,Green", "=", ",",, "N/A")
Splits the text into a 2×3 array, using “N/A” for missing values in the Green row.
Additional Notes
- TEXTSPLIT can handle multiple delimiters and can work effectively with complex text strings.
- The function is particularly useful in scenarios where manual text splitting is cumbersome or error-prone.
- It can be used to automate data extraction processes where specific text patterns are involved.