I have this formula: =IFS(SEARCH("a";A1)>0; "This contains the letter a"; SEARCH("b";A1)>0; "This contains the letter b")
This works when A1 contains “a”, but not if A1 contains “b”. How come? Or are there better ways to achieve this?
From the definition of SEARCH:
If the text is not found, returns error 519 (#VALUE).
Your formula expected that your first search returned some number, which you compared to 0. That is incorrect. An error in the formula component usually sets the whole formula result to error (unless you use functions that explicitly handle errors, like IS* family).
1 Like
=IFERROR("This contains the letter "®EX(A1;"[a-b]");"This does not contain the letter a or b")?
1 Like
=IFS(ISNUMBER(SEARCH("a";A1)); "This contains the letter a"; ISNUMBER(SEARCH("b";A1)); "This contains the letter b")
This actually works and is expandable as well, but I can’t understand why adding a check if its a number helps… anyways thanks!
Because it catches the error value that is returned by the failing SEARCH() and converts it to a boolean FALSE, and returns TRUE if SEARCH() was successful returning a position. Mike mentioned in his comment above with the "IS* family".
What should happen if both letters appear in the cell?