Some times we need to use a formula in order to seek the position of a word in a string, in order to subtract a part of that string. In the following article I will show you the way to detect the position.

Finding the position of the first occurrence

Using the find() formula we should be able to get the position of the first occurrence of a word or character in the string.

The formula is: find(characters we seek, string being analyzed, position to start from), where the position is optional if we search from the start of the string.

Search for the second position of a word in a string

Having the first position generated, we can now seek the string one more time starting from the first position +1, to get the second one.

In the picture bellow the word “green” is being searched for the second appearance.

finding the position of the second appearace

You need to use only the second formula:

=FIND(“green”,string being analyzed, FIND(“green”,string being analyzed)+1)

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.