There are times when you want to locate a specific character or substring within a text string while working on an Excel sheet. To do so, you will need to use the FIND function, which will help you get the location of the text in a string.
When used in the correct manner, the FIND function will return the position of the text string, within another string. In the event that the substring is not found, the FIND function will return a #VALUE error.
Excel FIND function
The formula for the Excel FIND function is expressed as below:
=FIND(find-text, within-text, [start-num])
In the syntax, the first two parts are mandatory, while the last one is optional.
- Find-text– character or the substring you want to find
- Within-text- text string that you want to search within. You can either specify it as a cell reference or type the string directly into the formula
- Start-num- specifies from which character the search should begin. It is optional and if you choose to omit it, then the search will begin from the first character of the within-text string.
If the specified string is found, you will get a return value which represents the location of the find-text. And as already mentioned, if the string is not found, then a #VALUE error shall be returned.
Figure 1. Using the FIND function in Excel
In the above diagram, we want to find a substring within the main string. Note that the main strings are located in column A, and therefore we want to have the return value to be located in column C.
Let us use the FIND function to locate the substring “Players”, located in A2. To do this, proceed as follows:
Step 1: In column B2, type in the formula as below:
Step 2: press “Enter”
After typing in the FIND formula, the next thing is to press “Enter” so that you can get the return.
Step 3: Hold and drag
To FIND the return of other cells, point the cursor to the bottom-left corner of the cell, hold it down and drag downwards across all the other cells. This will look as shown below:
Figure 2. Copy the FIND function across other cells
As you can see above, those cells that do not have the substring returns a #VALUE error, while those that have them bring a number value.
- Case sensitive
You need to remember that the FIND function in Excel is case sensitive. This implies that, if you type the substring in uppercase, while in the original cell is in lowercase, then you will find an error.
- No wildcards
The FIND function in Excel does not allow the use of wildcard characters
- In the event that your argument contains many characters, then the Excel FIND function will return the position of the first character. E.g. =find(“la”,player), the return will be 2. This is because “l” is the second later in the string.
- The Excel FIND formula returns the first character in the search string if the find-text is an empty string.
In conclusion, Excel FIND function is critical if one wants to locate a specific character. It simplifies work especially given that there are times when one will be working with an Excel worksheet that has several entries.