How to find last occurrence of character in string

  • Post author:
  • Post category:Uncategorized

Think of a situation where you have thousands of URLs and you want to extract the last word from it. This would be quite a difficult task if you were to find last occurrence manually. In order to find position of last character in a string, you can use an Excel formula. This post looks at how we can find position of character in string from right.

It is easy to simply extract anything from a string as long as you have the position of its last occurrence. This can be done using the RIGHT function. The formula looks like the one below;

=RIGHT (A2, LEN (A2)-FIND (“@”, SUBSTITUTE (A2, “/”, “@”, LEN (A2)-LEN (SUBSTITUTE (A2, “/”, “”))), 1))

The formula can be used as illustrated in the figure 1 below;

Figure 1: Excel formula to find last occurrence

As you can see from the above figure, we have used the Excel formula to extract the last string from the URLs given. Our criteria is to get the last word after the forward slash, “/”.

This could have been very difficult, had we chosen to go the manual way and extract the last string individually.

Using VBA to get position of last occurrence

We can also use a formula that is based on Excel VBA to get the position of last occurrence. The function created to help us with this is called LastPosition, created using the VBA.

The LastPosition helps us find the position of specified character in a string. Taking the above example for this illustration, we shall have the formula below to help us get the Excel index of character.

=RIGHT(A2, LRN(A2)-LastPosition(A2, “/” + 1)

When we apply this formula, we shall have the last string from the URLs listed as the result as shown in figure 2 below;

Figure 2: LastPosition function