Formula Friday #11
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
Today we’re looking at the FIND Function
I first started using the FIND function when I needed to standardise output information for product uploads. We all know how adventurous it can get when naming products. But ultimately a dress is a dress, whether it’s bodycon, skater or maxi. So I used the FIND function to help me make sure everything to be uploaded fitted in with the standardised options in the CMS.
The FIND function is case sensitive, which could trip you up if the data you are trying to standardise has a complete freeform input.
Let’s get into an example so it makes more sense
If you have ever worked with a large data set that needs to be organised into predetermined inputs, that can’t be changed, this is the formula for you. By combining the IF function with the FIND function you can quickly add in the standardised options.
=IF(FIND(“concealed back zip“,N2),”CONCEALED BACK ZIP FASTENING (25)“,”DEFAULT (9999)“)
In this example we are looking to find “concealed back zip” in cell N2. If it can be found the output needs to read “CONCEALED BACK ZIP FASTENING (25)” and if it can not be found, the output needs to be “DEFAULT (9999)”.
What the FIND function does
Is return the position of the start of the ‘lookup text’ within the specified cell. This position is presented as a value.
So in our example the returned value would have been 1, because the phrase we were looking for was the only data in the specified cell.
If we were looking for “zip” the returned value would have been 16. Because the letter z is the 16th character in the specified text string.
As with the LEN Function, FIND counts the spaces as characters.
But as I said, it’s case sensitive. So if my ‘lookup text’ did not match the data in the specified cell it wouldn’t return the correct answer. An alternative to use to get around this is the SEARCH function. It will work the same way and mean if you, or someone else, mistypes something the formula will still work.
=IF(SEARCH(“concealed back zip“,N2),”CONCEALED BACK ZIP FASTENING (25)“,”DEFAULT (9999)“)
And that’s it
I’m serious. I’m totally serious there is nothing more to be said about the FIND function. You tell Excel what you want to find, where it needs to look and that’s it.
Chances are we will revisit FIND, in another post. But for now that’s all folks.
Until next time,
Ax
