MS Excel: SEARCH() Function
Microsoft’s Excel has a function called SEARCH(), this function can be used to find the location of a particular character or string within a string.
Understanding the way Excel uses SEARCH()
This formula required 3 different inputs; A NEEDLE, A HAYSTACK and a START LOCATION.
The formula is as follows:
SEARCH(NEEDLE, HAYSTACK, START LOCATION)
Using SEARCH()
For example, if I had the string “Ian Blott, Sydney Australia” in cell A1 and I wanted to find the location of the comma then I would use the formula:
SEARCH(",",A1,1)
This would return the value 10.
You might think that having a START LOCATION is odd, however, the START LOCATION can come in very handy if you know that you may have multiple positives in the search results.
For Example, If the string was “Ian Blott, Web Developer, Sydney Australia” and you wanted the location of the second comma you can use the START LOCATION to effectively change the HAYSTACK to start at position 11.
The reason I use position 11 and not 10 is because position 10 is the first comma and the search feature includes the position you nominate for the START LOCATION. Thus if I used position 10, the search result would come back as finding it in position 1.
START LOCATION 10 will make the HAYSTACK “, Sydney Australia”.
START LOCATION 11 will make the HAYSTACK ” Sydney Australia”.
Of course, in the real world this isn’t ever going to be this easy. If I am using text strings, the position of the first comma could be anywhere so using a hard number for the START LOCATION is impractical. So get fancy!
Getting Fancy
I can double up on my search to find the position of the second NEEDLE:
By replacing the START LOCATION with another SEARCH() function I make a child search look for the first comma, then use that value to start the location of the parent search.
SEARCH(NEEDLE1, HAYSTACK1, SEARCH(NEEDLE2, HAYSTACK2, START LOCATION))
In action:
SEARCH(",",A1,SEARCH(",",A1,1)+1)
Note that there is a +1 after the START LOCATION search. This goes back to when I explained that I used position 11 and not 10, where if I where to use the position of the first comma it would include that in the HAYSTACK for the second search.
Side note:
As with all my posts, quite often they are going somewhere and require 2,3 or even 4 different functions to perform the final task. Bellow are the related posts that tell an overall story
