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 looking for a single label e.g. "security"
If I use the table syntax =IF(SEARCH("security",[@Labels],0)>0,"Y","") then I get @VALUE errors
but if use grid syntax =IF(SEARCH("security",P2,0)>0,"Y","") then it works as expected. However this is not as useful due to the variable number of rows from the external source.
Are there certain Excel functions which simply don't work with column fields?
4 Replies
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*")Mike Williams It's the ,0 part at the end of the SEARCH that causes the #VALUE! error (also in the formula with the direct cell reference). The start number should be at least 1. But if you don't want to skip any characters, just leave it out. Change that and it will work, however, it will still give a #VALUE! error when the word "security" is not found (see picture).
To avoid the error you can write the formula as follows:
=IFERROR(IF(SEARCH("security",[@Labels]),"Y"),"")- Hi. I don't get an error with ,0 in formula with direct cell reference (which is why I wrote it like that in my post), but I did work out that it does cause the error with column labels.
Mike Williams That's odd. I get an error with both methods. But happy you got it to work within a structured table.
