Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Split Excel cell How to find last occurrence of character in string – Excelchat

How to find last occurrence of character in string – Excelchat

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc