[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

EXTRACT

This dynamic array function helps to extract the matching substring according to a regular expression. Dynamic arrays are the resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell. Before using this function, you need to turn on the allowDynamicArray option.

Syntax 

SJS.REGEXEXTRACT(text, regular_expression, [modifiers])

Arguments 

This function has the following arguments: 

Argument 

Description 

text 

Refers to the text you are trying to search. The function can input a string, a cell reference, or a cell range reference. 

regular_expression 

Refers to the first part of text that matches the regular expression that is returned. 

modifiers 

[Optional] Input to define the pattern. 

  • g (global): retains the index of the last match and allows subsequent searches to start from the end of the previous match. 

  • i (ignore case): makes the whole expression case insensitive. 

  • m (multiline): when enabled, beginning and end anchors are matched with the start and end of a line, instead of the start and end of the whole string. 

  • u (unicode): when enabled, you can use extended unicode escapes in the form. 

  • s (dotall): dot is matched to any character, including newline. 

Remarks 

  • You can use this function with text (not numbers) as input and return text as output. 

  • If you want a number as output. Use the VALUE function in conjunction with this function. 

  • If you want to use a number as input, convert them to text using the TEXT function. 

Examples

spread.options.allowDynamicArray = true; set allowDynamicArray to true for all the examples

SJS.REGEXEXTRACT("I think SpreadJS is Good", ".*(good).*", "i") gives the result Good

SJS.REGEXEXTRACT("There are 195 countries in the world", "\d+") gives the result 195

SJS.REGEXEXTRACT("You can also extract multiple values from text.", "You can also (\w+) multiple (\w+) from text.") gives the result ["extract", "values"]

Errors 

  • returns a #VALUE! error if the value type of text or regular_expression is not text. 

  • returns a #N/A error if the length of the parameters < 2. 

  • returns a #NAME error if any parameter is not valid. 

  • returns a #N/A error if regular_expression does not match the text. 

  • returns a #SPILL error if array result is not expanded.