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
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment