TEXTSPLIT function

The TEXTSPLIT function in Excel is used to split a text string into separate values or text fragments based on a specified delimiter. This function is especially useful when you have a single string with multiple pieces of data separated by a delimiter, and you want to break it down into multiple parts.

Syntax:

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

Parameters:

• text (required): The text string that you want to split.
• delimiter (optional): The character or substring that separates the parts of the text you want to split. For example, a comma (,), space (" "), or any custom delimiter.
• col_delimiter (optional): The character or substring that separates data across columns (horizontal split). Default is the same as delimiter.
• row_delimiter (optional): The character or substring that separates data across rows (vertical split). Default is the same as delimiter.
• ignore_empty (optional): A logical value (TRUE or FALSE) that determines whether empty values in the split should be ignored. The default is FALSE.
• match_mode (optional): A number that determines whether the match should be case-sensitive (1 for case-sensitive, 0 for case-insensitive). Default is 0 (case-insensitive).
• pad_with (optional): The value used to fill empty cells when splitting. This is useful if you want to ensure all output cells have the same number of rows and columns.

Key Points:

• The TEXTSPLIT function returns the parts of a text string as a spill array, meaning it will “spill” the results into multiple rows or columns, depending on the delimiters used.
• It can handle both column and row delimiters, enabling you to split text both horizontally and vertically.
• The function is especially useful for extracting values from strings such as CSV data, email addresses, or any structured text.

Examples of Usage:

1. Split Text into Columns by a Single Delimiter

If cell A1 contains "apple,banana,cherry", and you want to split this string by commas into separate columns:

=TEXTSPLIT(A1, ",")

This will return:

apple  banana  cherry

Explanation:

• The function splits the string "apple,banana,cherry" at each comma (,) and places the resulting values into separate columns.

2. Split Text into Rows by a Single Delimiter

If cell B1 contains "apple,banana,cherry" and you want to split it into separate rows:

=TEXTSPLIT(B1, ",", ,TRUE)

This will return:

apple
banana
cherry

Explanation:

• The function splits the string at each comma (,) and places the results into separate rows.

3. Split Text with Different Row and Column Delimiters

If cell C1 contains the string "apple|banana|cherry;date|elderberry|fig", and you want to split the text into rows and columns:

=TEXTSPLIT(C1, "|", ";")

This will return:

apple   banana   cherry
date    elderberry   fig

Explanation:

• The function uses "|" to separate columns and ";" to separate rows.

4. Ignore Empty Values

If cell D1 contains "apple,,banana,,cherry" (with empty values between commas), and you want to split the text but ignore the empty cells:

=TEXTSPLIT(D1, ",", , TRUE)

This will return:

apple
banana
cherry

Explanation:

• The function skips the empty values and only returns the non-empty fragments ("apple", "banana", "cherry").

5. Case-Sensitive Matching

If you have text in cell E1 as "apple,Apple,Banana", and you want to split by commas with case sensitivity (so “Apple” and “apple” are treated differently):

=TEXTSPLIT(E1, ",", , , FALSE, 1)

This will return:

apple
Apple
Banana

Explanation:

• The function splits the string at commas and matches "apple" and "Apple" as distinct because of the match_mode set to 1 for case-sensitive matching.

6. Using Padding

If you want to split a string and ensure that all the rows or columns have the same number of elements, you can use the pad_with argument. For example, if F1 contains "apple,banana,cherry", and you want to pad any missing values with "N/A":

=TEXTSPLIT(F1, ",", , , FALSE, 0, "N/A")

Explanation:

• If the split text results in fewer items than expected, it will pad the missing values with "N/A".

Related Functions:

• TEXTJOIN: Combines multiple text values into one string, whereas TEXTSPLIT does the opposite by splitting a string into multiple values.
• MID: Extracts a substring from a specific position and for a specific length, but without using delimiters.
• SPLIT (Google Sheets): Similar function available in Google Sheets to split text into multiple cells.

The TEXTSPLIT function is particularly useful when working with structured text, like CSV data, or when you need to break down strings into meaningful components based on delimiters. It enhances data manipulation and makes it easier to analyze text in Excel.