The SEARCH function has a couple of issues that make it difficult to use. This makes it a prime candidate for a custom function to fix its limitations.
The SEARCH function has two limitations.
- It returns an error of it doesn’t find what is being searched for.
- It doesn’t handle blank/empty cells correctly.
The SEARCH function returns a number representing the position of what is being searched for. The SEARCH function will “find” a blank cell and return the number 1.
Examples
In the table below we can see examples of these shortcomings in column C – the formula is shown in column D.
Note cell B8 is empty yet the SEARCH function returns a 1.
To get around the error issue we can use the IFERROR function (column E), but that still shows 1 for the empty cell B8.
The ultimate solution is in column G. You can see we have now used three separate functions to return the position number of the entry in column B in column A.
Custom function
To simplify the SEARCH calculation and return 0 instead of an error we can create a custom function to replace the SEARCH function.
The LAMBDA accepts two inputs find-txt and in_txt.
If find_txt is blank then 0 is returned. If an error is encountered (any error) then 0 is returned. Otherwise the SEARCH function result is returned.
The custom function is shown being used in column E above, and its definition is shown in the image below.
To see the LAMBDA laid out differently see the image below which uses line breaks to split up the LAMBDA.
Here is another way to write your LAMBDA (it is not a better way, just a different way)…
=LAMBDA(find_txt,in_txt,LET(f,SEARCH(find_txt,in_txt&find_txt),f*(f<=LEN(in_txt))*(find_txt””)))
Wow Rick you really do think about things differently.
Thanks for sharing
I think it needs =”” on the end.
That last expression was supposed to be find_txt NOT EQUAL TO “”… I think your forum’s comment processer “ate’ the less than symbol followed by a greater than symbol. Is there any way you can edit that into my formula?
Yep, that’s probably a HTML issue on the forum.
Here is a modification to your lambda which adds an optional third argument named ignore_case. If omitted or if TRUE (or 1) is passed into it, the function will use the SEARCH for the search; otherwise, if you pass in FALSE (0r 0), the the FIND function will be used for the search…
=LAMBDA(find_txt,in_txt,[ignore_case],IF(find_txt=””,0,IFERROR(IF(OR(ISOMITTED(ignore_case),ignore_case),SEARCH(find_txt,in_txt),FIND(find_txt,in_txt)),0)))
Hmm! On my computer, the formula I just posted runs out of the comment box and into the ads on the right. Here is the formula with deliberate line breaks to ensure it fits the comment box properly….
=LAMBDA(find_txt,in_txt,[ignore_case],
IF(find_txt=””,0,IFERROR(IF(OR(ISOMITTED(ignore_case),ignore_case),
SEARCH(find_txt,in_txt),FIND(find_txt,in_txt)),0)))
Hi Rick
Thanks for taking the time to add in ignore case option tweak with the FIND. Two searches in one!
This one looks better – as you say the previous formula runs over to the right.
Regards
Neale