Microsoft Excel–Search From The Right

If you ever need to search a text in an Excel cell for a specific character starting from the right and cannot find the formula to do it, this might be some help to you.

I found this nice formula on MREXCEL.COM by “IML”.

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

It is a rather long formula, so let’s break it up and see how it works. Using the following text in cell A2 as an example.

D:\Backup\Documents\Pictures\Bike.jpg

The objective is to extract the remaining of the text after the last “\”. So the desire result is Bike.jpg and the formula will be

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))))

This is how the formula works.

First, replace the last “\” with another character. The SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))) portion of the formula does that. The LEN(SUBSTITUTE(A2,"\","")) returns the length of the text without any “\” character which will be 33. The LEN(A2)-LEN(SUBSTITUTE(A2,"\","")) returns the number of “\”characters which is 4. As you can see, 4 is the last instance of the “\” character in the text. Therefore, by using the formula SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))), it replaced the 4th instance of the “\” character with “@'” character.

Next, find the position of the “@” character using FIND function. The FIND("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))) portion of the formula does that and returns 29.

Now use the RIGHT function to extract out the remaining of the text after the “@” character. We need to tell the RIGHT function how many characters we want to extract starting from the right. To get the number of character, take the length of the text and minus away the position of the “@” character. The LEN(A2)-FIND("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))) portion of the formula does that and returns 8. Finally the RIGHT function will return Bike.jpg.

If we evaluate the formula stop just before the RIGHT function, it will be RIGHT(37, 8) where 37 is the length of the text and 8 is the number of characters we want to extract starting from the right.

Pin It