Excel TEXTSPLIT function

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.

Related Functions

Excel CONCAT function

The Excel CONCAT function merges multiple text strings into one, perfect for data concatenation without using the ampersand (&).

Excel TEXTAFTER function

The Excel TEXTAFTER function extracts text after a specified delimiter, enhancing data parsing and extraction tasks.

Excel TEXTJOIN function

The Excel TEXTJOIN function combines text from multiple ranges, including a delimiter, perfect for creating comprehensive strings.

Excel TEXTBEFORE function

The Excel TEXTBEFORE function extracts text before a specified delimiter, useful for segmenting and analyzing string data.

Content Navigation