Check if cell has given tag. Great for formula-based conditional formatting.
Syntax: \HAS.TAG(tag_text, [reference]) => logical1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | =LAMBDA(tag,[reference], LET( txt, IF(ISOMITTED(reference),INDIRECT("RC",0),reference), ct, UNICHAR(917631), IF(RIGHT(txt,1)=ct, LET( ht, UNICHAR(917539), to_tag_char, LAMBDA(c,IF(c="",c,UNICHAR(UNICODE(c)+917504))), ttag, TEXT(tag,"0"), prefix1, to_tag_char(left(ttag,1)), prefix2, to_tag_char(right(left(ttag,2),1)), prefix3, to_tag_char(right(left(ttag,3),1)), prefix, ht & prefix1 & prefix2 & prefix3, hastagprefix, NOT(ISERROR(FIND(prefix,txt))), IF(hastagprefix, NOT(ISERROR(FIND(ht & REDUCE("",SEQUENCE(LEN(ttag)),LAMBDA(acc,val, LET( c, UNICODE(RIGHT(LEFT(ttag,val),1)), acc & IF(AND(c>31,c<127),UNICHAR(c+917504),"") ) )) & ct, txt))) ) ) ))) |
Documentation
Checks if the current cell or cell passed in the reference contains a tag equal to tag_text.
If reference is passed, it checks the cell from the reference. If reference is omitted, checks the cell in which the formula resides (for use in conditional formatting rules manager).
Usage of the \HAS.TAG function in the worksheet with the reference argument omitted will result in circular references.
The function will only return true if there is a tag appended to the end of the cell value. It does not have to be the tag that is being checked for.
Commentary
This function has recently been updated to be a little faster. It checks the cell in 3 steps. First, it checks if the last character in the cell is the tag cancel character. Then, it constructs the prefix of the tag, i.e. #exa – 3 first letters of the tag and a tagged # (each tag should start with a #) and checks the cell for that. If it’s there, it proceeds to convert the entire tag that is being looked for into the Tag unicode characters and uses FIND() to find it.
I didn’t know whether to use SEARCH or FIND to look for the tag within the cell, so I did some performance testing, and it turns out FIND is over 3 times faster than SEARCH.
A caveat of this approach is that if someone was to construct a tag like some#tag, a cell with such a tag would return TRUE for \HAS.TAG(“tag”). Oh well.
