Master Excel’s FIND Function In Under 3 Minutes – [2 Clear Examples]
Contents
The FIND Function in Excel is useful for locating specific text within a cell.
It tells you where the text starts.
FIND can tell you where the word “apple” begins in the sentence “I like apple pie.”
FIND Function Details
| Available in: | All versions of Microsoft Excel |
| User Level: | Beginners |
| Inputs: | “find_text”: The text you are looking for. |
| “within_text”: The text where you want to search. | |
| “start_num”: Optional argument that lets you specify a start point. | |
| Outputs: | Position of the search text. Data Type: Number |
| Wildcards: | No |
| Case Sensitive: | Yes |
Where To Find The FIND Function
To find the FIND function: Click Formulas > Text and click the FIND Function.
Using FIND With Other Data Types
FIND is a very specific function and only works with text.
If you enter something that isn’t text, or reference a cell that doesn’t contain text, you will get an error.
We cover data types in our Excel courses, as they are the foundations Excel was built on!
Simple Example
Imagine you have a set of text and are looking for the word “dog” within.
To find where the word dog is located, enter the following:
=FIND("Dog",A2)This will return 10, as that is where word starts in the given string!
Advanced Example
Let’s say you have some text you want to omit from your search.
You can use the optional argument start_num for this!
For this example lets say we have” Chapter 1: ” before our text.
This is 11 characters long so let’s specify the start point at 11.
To find where the word dog is located, enter the following:
=FIND("Dog",A2,11)This formula takes £100 in A2 and returns 100 as your final result!
Another way to make a function more advanced is to combine it with other techniques!
A great example is using advanced filters to sort your data, then using FIND to retrieve specific data.
Troubleshooting & Errors
Error 1: #VALUE! Error
Cause: If the text can’t be changed to a number.
Solution: Check the text. It should look like a number.
Error 2: Unexpected Result
Cause: Using FIND on non-numeric text.
Solution: Use FIND only on text that looks like numbers.
Error 3: No Change
Cause: The text is already a number.
Solution: Check if your text is actually a number. FIND isn’t needed if it’s already a number!
Knowing how to audit your formulas is a great skill to have when errors start cropping up.
What Is The FIND Function Useful For?
The FIND function is perfect for retrieving data, which feeds into:
| 1. Data Cleaning | Removeing non-numeric characters from numbers in text form. |
| 2. Data Conversion: | Turning numeric text into numbers for calculations. |
| 3. Data Validation: | Ensuring data meant to be numeric is actually in number format |
If you combine it with the Count Function you can analyse your data.
Similar Useful Functions
Other functions similar to the excel value function that are easy to use include:
- TEXT: Converts numbers into text.
- NUMVALUE: Transforms text with number formatting into numbers.
- INT: Rounds down a number to the nearest integer.
- SEARCH: Locates one text string within another, but not case-sensitive.
- TRIM: Eliminates extra spaces from text.
The FIND function in Excel helps turn text that looks like numbers into real numbers.
It’s simple to use and very helpful for cleaning and converting data.
Whether you’re a beginner or an expert, knowing how to use the FIND function can make your Excel tasks easier.
