Position of Last Occurrence of Symbol

 

How to Get Position Of Last Occurrence of Symbol In Microsoft Excel

If you want to know the position of a symbol in Excel then you should read this article. In this article, we will learn how to find position of last occurrence of certain character.

Question): How can I find the position of the last occurrence of a symbol?

Example: If a cell contains website address then it will have multiple (.) dots & in this scenario, we need a formula to return the position of the last dot.

Following is the snapshot of data we have; now we need a formula to extract the position of the last dot (not the position of first dot)

 

img1

 

  • We will use a combination of FIND, SUBSTITUTE & LEN functions to get the output
  • In cell B2, the formula is
  • =FIND(“*”,SUBSTITUTE(A2,”.”,”*”,LEN(A2)-LEN(SUBSTITUTE(A2,”.”,””))))
  • After dragging down the formula in column B, we will get the desired output

 

img2

 

In case you have a similar requirement & the specific symbol or character is different i.e. other than dot then in that case, you are required to simply replace the dot with your special character.

 

In this way, we can retrieve position of the last occurrence of a specific symbol.

 

image 7

Download – how to get position of last occurrence of symbol – xlsm



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>