Master The FIND Function In 5 Minutes
The FIND Function in Excel is useful for locating specific text within a cell.
It tells you where the text starts. For example, 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: | “text”: The text where you want to search. |
| “search_text”: The text you are looking for.
“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.
Not the function you are looking for? See this article on the Average Function instead.
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 dosen’t contain text, you will get an error.
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
Lets 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 lets 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 two functions together. More on that in this Sum Function article!
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!
What Is The FIND Function Useful For?
The FIND function is perfect for retrieving data you want
| 1. Data Cleaning | Removes non-numeric characters from numbers in text form. |
| 2. Data Conversion: | Turns numeric text into numbers for calculations. |
| 3. Data Validation: | Ensures that data meant to be numeric is actually in number format |
You can also use it alongside the Count Function for data analysis!
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.
- ROUND: Rounds a number to a specified number of digits.
- TRIM: Eliminates extra spaces from text. Check out our guide to the Trim Function here.
Conclusion
The VALUE 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.
Mastering these functions will transform your spreadsheet skills!
