The FIND Function is used to locate a specific string of characters within a larger string and returns the starting position of the first occurrence of the searched string. If the text is not found within the larger text, then the function returns a #Value error. The FIND Function can be useful for various tasks such as data analysis, string manipulation and data cleaning. In this post, we would touch on everything about the FIND function and how to integrate it in financial modeling. Let's start with the Syntax & Argument
Syntax & Argument
The FIND Function syntax is as follows:
=FIND(find_text, within_text, [start_num])
There are 2 required arguments and 1 optional argument:
find_text: This is the substring you are searching for in a larger string of text.
within_text: The text to search for appearance of the substring.
Start_num: This is the optional argument with which you are able to dictate the starting position in the text to search for.
Usage Notes
There are some things you must note when using the LEN function:
- The FIND Function returns the position of the substring (find_text argument) inside the string (within text argument) as a number.
- If the substring is not found within the string, the LEN function returns a #VALUE error.
- If there are two occurrences of the substring within the string, the function returns the position of the first occurrence.
- The FIND Function is case sensitive meaning that if the find_text appears in different cases in the within_text argument, the FIND function will return a #VALUE Error.
- The FIND Function does not support wildcards.
FIND Function Examples
Basic Example
The FIND Function is used to search inside a text string for a specific substring and return the position number if found or a #VALUE error if not found.
=FIND("c","drip") // Returns #VALUE!
Logical Test (True or False)
The FIND Function can be combined with the ISNUMBER Function to create a logical test to return either TRUE or FALSE. The logic is that since the FIND Function returns a number when the substring is found, we can be assured that the ISNUMBER function would also return TRUE if the substring is found.
=ISNUMBER(FIND("f","phone")) // Returns FALSE
Dictate Start Number
The start_num is an optional argument that can instruct excel to start searching for the substring at a particular point in the main text string. In our example for this section, we have a code in 3 sections separated by the slash delimeter
There are 2 slashes and as we discussed in the notes section, the FIND rule returns the position of the first occurrence. Suppose we want the position of the second occurence, we can instruct to start searching after the first occurence with the start_num argument. Let's instruct excel to start searching from character 7.
Using the FIND Function in a Financial Modeling Scenario
In our example for this section, we have a data set showing the average sales by city. However, we need to perform an analysis by state for the decision-making process. Luckily for us, we have the city listed alongside their 2-digit alpha code. Of course, this can be checked manually, but we need to automate this in order to allow time to perform the real analysis.
We know the 2-digit alpha code for each state, using a lookup for the actual name would however return errors in the present state of our data. The 2-digit code comes after the city name with a comma acting as the delimiter. Using a combination of the RIGHT Function, LEN Function and FIND Function, we can extract the exact 2-digit code regardless of the length of the city name. The function combination to extract the 2-digit code only can be seen below:
Explaining the Formula
To explain it, let's start with what the LEN Function does. The LEN function returns the number of characters in each text. In cell B3, the total number of characters is 16, let's note that somewhere. Next function is THE FIND Function. We need to locate the position of the delimiter which is the comma in our case (,).
Now time for a little Math. We know the total number of characters in each cell, and we know the position of the delimiter. All we have to do is to subtract the position of the delimiter from the total character count to get all text after the delimiter. We are adding "-1" to remove the space after the delimiter.
Finally, we need to return the code after using the LEN and FIND function to somehow figure out the position of the first letter of our 2-digit state code. This is where our RIGHT Function comes in. The previous functions would tell excel how many characters from the extreme right the function should capture. The LEN and FIND Function calculation would return 2.
Next Steps
We have the 2-digit state codes for each sale, and we can now lookup the actual names from our state code sheet.
We are looking up from the sheet above into our previous sheet using the XLOOKUP Function. The formula to achieve this is as follows:
Final Note
In our example above, all state codes are 2 digits so these entries may not fully display how much the combination of the RIGHT, LEN AND FIND Functions made our formula dynamic. Now let's have some varying lengths of hypothetical codes to ensure that the formula is working correctly.
Copy and paste the formula in the previous section from cells C3:C6
As you can see from the above, regardless of the length of the code, our formula returns the exact state code only.