I need a Google Sheets native formula (not App Script function) that return true if all characters in string B exist in string A.
For example, if A = ‘CDEFGH’
- If B = ‘EDX’ —> Match = False (character X is not in string A)
- If B = ‘HCE’ —> Match = True (Characters H, C, and E are all in string A)
Note the following:
- The characters in B can be in any order and are not necessarily contiguous - I need to check for the presence of ALL (not any) of B’s characters (in any order) are present in string A.
- I know how to do this in App Script but it is too slow for my application as I need to call this function thousands of times. So the solution has to be using Google Sheets native built-in functions excluding App Script.
- I would love to do this using regular expression. If so, please show me how to get Google Sheets to read strings A and B in cells and return true/false when the condition above is met.
CodePudding user response：
Try
=arrayformula(sum(--REGEXMATCH(B1,split(REGEXREPLACE($A$1,"(.)","$1~"),"~"))))=len(B1)
explanation
REGEXREPLACE($A$1,"(.)","$1~")will add a tilde after each character- then split the result by
~ - compare with
REGEXMATCH(B1,split(REGEXREPLACE(A1,"(.)","$1~"),"~")) - then sum (
--will tranform boolean to number) and compare with the length of A1
extension
to avoid any repetition, try
=arrayformula(sum(--REGEXMATCH(B1,unique(transpose(split(REGEXREPLACE($A$1,"(.)","$1~"),"~"))))))=len(join("",unique(transpose(split(REGEXREPLACE(B1,"(.)","$1~"),"~")))))
