How to use TEXTSPLIT function in Excel?

The TEXTSPLIT function in Excel is used to split text strings into separate cells based on a specified delimiter. This function can be particularly useful for breaking up text data into individual components. Here’s how to use the TEXTSPLIT function:

Syntax

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

text: (Required) The text string you want to split.
column_delimiter: (Required) The delimiter that separates the text into columns. This can be a character (like a comma or space) or text string.
row_delimiter: (Optional) The delimiter that separates the text into rows. If omitted, the function will not split into rows.
ignore_empty: (Optional) If TRUE, ignores empty values in the output. Defaults to FALSE.
match_mode: (Optional) Used to specify case sensitivity. A value of 0 indicates a case-sensitive match, while 1 indicates a case-insensitive match. Defaults to case-sensitive.
pad_with: (Optional) If specified, uses this value for padding missing elements in arrays that result from splitting.

Example

Suppose you have a text string in cell A1 that says “apple,banana,orange”, and you want to split this into separate cells.

=TEXTSPLIT(A1, ",")

This formula will return three separate values in adjacent cells horizontally: “apple”, “banana”, and “orange”.

Additional Considerations

Splitting into Rows: If you want to split text into rows instead of columns, you can specify the `row_delimiter`. For example, if you have a text string with line breaks and you use CHAR(10) (which represents a line break), you can split the text into rows.
Complex Delimiters: You can use more complex string patterns as your delimiter if your data requires it.
Ignoring Empty Results: Use the `ignore_empty` argument if you need to exclude any empty spaces that occur between delimiters.

Remember that the availability of the TEXTSPLIT function depends on your version of Excel, as it’s a newer function introduced in Excel 365 and Excel 2021. If you’re using an older version, you might need to use other functions like `TEXT`, `SEARCH`, `LEFT`, `RIGHT`, or `MID` in combination with each other to achieve similar results.

facebooklinkedin