The FIND function in Excel is useful when you need to find the position of the nth space on a given string.
Users can use FIND to locate a substring within a larger text string. This same method can be used to find the nth occurrence of any arbitrary character.
The rules for using the
FIND function in Excel are as follows:
- The function requires two arguments: the string to look for and the text we’ll search.
- The function then outputs the starting position of the first found instance within the given text string.
- You can also add an optional argument to specify a different position to start your search.
Let’s take a look at a quick example of a situation where we can use the
FIND function to look for a particular instance of a character.
Suppose we have a collection of numbers separated by a space. For example, the string “4 8 15 16 23 42” has six numbers separated by spaces.
We can use a function like
MID to return a substring of this number. But before we get the substring, we must first know the positions the substring begins and ends.
To get the second number, we must first know two values: (a) the position of the first space and (b) the position of the second space. The substring containing the second number must start one place after (a) and must end one space before (b).
We can use the
FIND function to get the first and second occurrences of the space character in a given string. This use case is just one way where finding the position of the nth occurrence of a character can be helpful.
Now that we know when we can use the
FIND function let’s dive into how we can use it on an actual sample spreadsheet.
A Real Example of Finding the Position of Nth Space on String in Excel
Let’s take a look at a real example of the
FIND function being used in an Excel spreadsheet to find an occurrence of the space character.
In the example below, we’ve used the
FIND function to find the first instance of a space character in the provided text. Because of the formula, we now know that the space is the fifth character in the string.
To get the values in cell B13, we just need to use the following formula:
The first argument is the substring we want to search for. In this case, we simply want to look for a single space.
In this second example, we are looking for a hyphen instead. We’re also looking for the second instance of the hyphen rather than just the first. Since the
FIND function only returns the first instance, we’ll have to specify a different position to start our position.
The formula to get the value in cell B17 is as follows:
To find the second hyphen, we would first have to find the position of the first. Once we get this position, we can increment the result by 1 to get the new position to start our substring search.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out the
FIND function in Excel, try writing it yourself using the step-by-step guide in the next section!
How to Find the Position of the Nth Space on String in Excel
This section will guide you through each step needed to start using the
FIND function in Excel. You’ll learn how we can use this function to get the first occurrence of the space character in a string.
Follow these steps to start using the
- First, select the cell where we will place our
FINDformula. In this example, we’ll place our first formula in cell B2.
- Next, we type in our
FINDformula with the necessary arguments. In this example, we’ll be finding the space character in the text found in cell A2.
- Hit the Enter key to return the position of the first occurrence.
- We can drag down the formula in cell B2 to fill up the rest of the column.
- We can continue to find the next few occurrences of the space character by tweaking the formula. To get the first, and second occurrence, we can use the formula
Since we already got the position of the first occurrence, we can increment it by one to specify a new starting position to find the substring.
Frequently Asked Questions (FAQ)
- Why does my formula return a #VALUE! error?
FINDfunction returns a #VALUE! error, this tells you that the substring could not be found in the provided text. If you want to catch this error, you can use the
IFERRORfunction to return something else when the substring is not found.
That’s all you need to remember to start using the
FIND function in Excel to find the position of the nth space in a given string. This step-by-step guide should be enough for you to find substrings in cells with text accurately.
FIND function is just one example of a text function you can use in Microsoft Excel. With so many other Excel functions out there, there are surely a few that can help improve your spreadsheets.
Are you interested in learning more about what Excel can do? Stay notified of new guides like this by subscribing to our newsletter!