PowerUp Description
The pwrREGEXMATCH PowerUp function applies the provided regular expression to the value.
PowerUp Syntax
pwrREGEXMATCH(value, expression)
The pwrREGEXMATCH PowerUp function has the following arguments:
value – Required. This is the source text to be searched by expression.
expression – Required. This is the regular expression to be used to find a matching pattern of text within value.
Return Value
pwrREGEXMATCH will return the text from value that matches expression.
Remarks
None.
Example
=pwrREGEXMATCH(A1,"[a-zA-Z]")
can you tell if this function respect /i clause to ignore case sensitivity?
Hi, the default behavior of this particular function is to ignore the case of text.
So, given some JSON:
{
“Global Quote”: {
“01. symbol”: “MSFT”,
“02. open”: “151.7500”,
“03. high”: “154.8900”,
“04. low”: “149.2000”,
“05. price”: “149.7000”,
“06. volume”: “57042291”,
“07. latest trading day”: “2020-03-27”,
“08. previous close”: “156.1100”,
“09. change”: “-6.4100”,
“10. change percent”: “-4.1061%”
}
}
The regular expression:
(.*05. price\”: \”)([0-9]*\.[0-9]*)
Will parse everything up to but not including the price into group 1 and will put the price in group 2. How do I retrieve group 2?
Assuming the JSON is in cell A1, I would have expected the syntax of pwrREGEXMATCH to look like this:
=pwrREGEXMATCH(A1, “(.*05. price\”: \”)([0-9]*\.[0-9]*)”, 2)
Where the 3rd operand (i.e. the 2) specifies which group, defined by the regular expression, to return. But it doesn’t appear to work that way. How would I return only the price (i.e. how would I get back only the text 149.7000)?
Hi, in the pwrREGEXMATCH implementation the full match is being returned. If you were to use a non capturing group (?:regex) the full match would still return. In this specific example using FIND with the pwrREGEXMATCH can work (or perhaps just FIND if the values are consistent in size all the time).
For example,, FIND(“05. price”, ), 20),”([0-9]*+\.[0-9]+)”)
=pwrREGEXMATCH(MID(
HTH,
Charlie