Home > Mobile >  Which formulas support characters like the "&" "|" " * "
Which formulas support characters like the "&" "|" " * "

Time:03-24

How would I check which formulas support characters like &,|, and *?

This source lists all but "&" as REGEX characters. https://support.google.com/analytics/answer/1034324?hl=en&ref_topic=1034375

Does this mean that they are only usable in REGEX formulas? (R.-MATCH -EXTRACT -REPLACE) and QUERY? Is the & sign only applicable then, in patching text together like this - A1&"whatever" -?

I could not find a definitive source for this topic, can you help me out? :)

(I'm asking because this is clearly not working while being the more clear solution: IF(A1=1 | 2, TRUE) and needs to be done redundantly like this IFS(A1=1; TRUE; A1=2; TRUE) )

CodePudding user response:

pipe | is a product of regex language mimicking OR function and can be used in

REGEXMATCH
REGEXEXTRACT
REGEXREPLACE
IMPORTXML
QUERY (only in conjunction with "matches")

asterisk * is a wildcard character that can be used in the above-mentioned functions and even further within

COUNTIF
COUNTIFS
MATCH
VLOOKUP

character & is common across various functions used mainly instead:

CONCAT
CONCATENATION

coz it's short and joins stuff nicely.

then there is ARRAYFORMULA which does not support functions like

AND
OR

so we use * multiplication for AND and sign instead of OR

so your IF(A1=1 | 2, TRUE) should be:

=REGEXMATCH(A1&"", "1|2")
=OR(A1=1, A1=2)

and for arrayformula:

=ARRAYFORMULA(REGEXMATCH(A1&"", "1|2"))
=ARRAYFORMULA((A1=1) (A1=2))
  • Related