The TEXTSPLIT function in Excel is used to split a text string into multiple cells based on specified delimiters. This function is particularly useful for breaking down text into separate columns or rows, similar to the Text-to-Columns wizard but in formula form.
Syntax
=textsplit(text, col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_width])
The TEXTSPLIT function syntax has the following arguments:
- text: The text string you want to split.
- col_delimiter: The character(s) that mark where to split the text across columns.
- row_delimiter: (Optional) The character(s) that mark where to split the text down rows.
- ignore_empty: (Optional) A boolean value (TRUE or FALSE). If TRUE, consecutive delimiters are ignored. Defaults to FALSE.
- match_mode: (Optional) Specify 1 for a case-insensitive match. Defaults to 0 (case-sensitive).
- pad_with: (Optional) The value to pad the result with if there are missing values. The default is #N/A.
Example
Let’s say you have the following text in cell A1:
- A1: “Apple, Banana, Cherry”
You want to split this text into separate cells in a row, using a comma and a space as the delimiter. You can use the TEXTSPLIT function as follows:
=TEXTSPLIT(A1,", ")
This formula will return:
Apple Banana Cherry
Another Example
If you have a text string in cell A1 that you want to split into separate rows, you can use:
=TEXTSPLIT(A1,,", ")
This will split the text into rows:
Apple
Banana
Cherry
Complex Example
If you have a more complex text string in cell A1, such as “Apple=Banana, Cherry”, and you want to split it into both rows and columns, you can use:
=TEXTSPLIT(A1, "=", ", ")
This will split the text into:
Apple Banana
Cherry