REGEXSPLIT function

REGEXSPLIT(text, regexDelimiter, limit?, caseInsensitive?) REGEXSPLIT(text; regexDelimiter; limit?; caseInsensitive?)

text — Text

The text string which should be split apart.

regexDelimiter — Text

The JavaScript regular expression used as a delimiter.

limit — Number (optional)

The maximum number of returned array elements. If omitted, there is no limit.

caseInsensitive — Logical (optional)

Whether the regular expression should ignore case. If omitted, FALSE is assumed.

Returns — { Text }

The parts of the text string that has been split apart.

Divides a text string into parts using a regular expression as a delimiter, returning the parts as an array. REGEXSPLIT("first ,second ; third", "[\s\t;,]+")REGEXSPLIT("first ,second ; third"; "[\s\t;,]+") returns the array { "first", "second", "third" }{ "first"; "second"; "third" }. The regular expression "[\s\t;,]+" is used to separate the returned parts. It matches one or several characters consisting of whitespace ("\s"), tab characters ("\t"), semicolons and commas.

Regular expressions are a powerful tool to search through and manipulate text, though they take time to master. For an introduction, refer to the blog post we wrote when Calcapp gained support for regular expressions.

Consider TEXTSPLIT for simple needs

TEXTSPLIT also breaks text apart, but does not use regular expressions, and is often easier to understand. It can only handle delimiters consisting of literal text. This formula returns the array { "first", "second", "third" }{ "first"; "second"; "third" }:

TEXTSPLIT("first, second, third", ", ")TEXTSPLIT("first, second, third"; ", ")

TEXTSPLIT works well when the text is known to use fixed text strings as delimeters, but cannot break text strings like "first ,second ; third" apart. That text string requires flexible, variable-length delimeters, which are only provided by regular expressions. Use REGEXSPLIT to break apart more complex text strings.

Examples

REGEXSPLIT("first ,second ; third", "[\s\t;,]+")REGEXSPLIT("first ,second ; third"; "[\s\t;,]+")

Returns the array { "first", "second", "third" }{ "first"; "second"; "third" }. The regular expression "[\s\t;,]+" is used as a delimiter, and therefore separates the returned parts. It matches one or several characters consisting of whitespace ("\s"), tab characters ("\t"), semicolons and commas.

TEXTSPLIT("first, second, third", ", ")TEXTSPLIT("first, second, third"; ", ")

Returns the array { "first", "second", "third" }{ "first"; "second"; "third" }. TEXTSPLIT also breaks text apart, but does not use regular expressions, and is often easier to understand. It can only handle delimiters consisting of literal text.