The output from TEXTSPLIT is anarraythat willspillinto multiple cells in the workbook.
Split text into columns or rows
TEXTSPLIT can split a text string into columns or rows.
To use TEXTSPLIT, you will need to provide the text to split and a delimiter.
is not found, yet TEXTSPLIT behaves as if the delimiterwas foundat the end of the text.
Both the TEXTBEFORE and TEXTAFTER functions have a similar feature, but it must be enabled with thematch_endargument.
With TEXTSPLIT, this behavior is automatic anduseful.
Only “Red” and “Green” are returned.
Match mode
The fifth argument,match_mode, determines case sensitivity when looking for a delimiter.
By default, TEXTSPLITiscase-sensitive andmatch_modeis zero (0).
Supply 1 todisable case sensitivity.
In the example below the delimiter is " x " and " X “.
The formula in D5 sets match mode to 1 to make TEXTSPLIT ignore case.
Padding
The last argument in TEXTSPLIT ispad_with.This argument is optional and will default to #N/A.
The formula in cell F3 is:
Notice also that there is an extra space separating green and purple.
TheTRIM functioncan be used to clean up extra space characters that appear in the output from TEXTSPLIT.
However, when TEXTSPLIT is used on arangeof cells, TEXTSPLIT returns an “array of arrays”.
The result may be a truncated version of the data or in some cases an error.Example here.
TEXTAFTER Function
The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter.
