Forum Discussion
Calculated Column using SEARCH
I have an Excel table with a column $P of comma-separated labels [@Labels]. The table is linked to an external data source I want to create a column which can be used for filtering, where I am l...
Mike Williams I agree with Riny_van_Eekelen in regard to SEARCH returning #VALUE! in both cases (by using either the structured table reference [@Labels] or the cell reference P2). I'm not sure how you got it to work with the optional [start_num] parameter set to 0, as the minimum accepted value is 1...
As per the remarks for SEARCH in Help (by pressing F1 on your keyboard):
- If the start_num argument is omitted, it is assumed to be 1.
- If start_num is not greater than 0 (zero) or is greater than the length of the within_text argument, the #VALUE! error value is returned.
Alternatively, if you're not opposed to filtering your calculated column by TRUE/FALSE or 1/0 instead of "Y"/"", here's a couple of simplified formulas you could try:
=ISNUMBER(SEARCH("security",[@Labels]))
=COUNTIF([@Labels],"*security*")