Forum Discussion
Extracting text between two instances of a character
I have a URL strings in cells in a column. In another sell I'd like to extract a portion of the URL string.
This is the string in A2.
https://www.domain.com/lpa/offer/1452258783/
The URL's always have the same format. I want to extract the last digits between the last two slashes, that is /1452258783/ so that cell B2 contains 1452258783.
I was elaborating with LEFT, RIGHT, MID and LEN functions but couldn't get it right. It would be interesting to have two formulas. One extracting text between the last two slashes couting the slashes from the left (5:th slash from the left) and another forumla counting the slahses from the right (between first and second slash).
Does someone have any suggestions how to solve this?
More universal
=RIGHT(IF(RIGHT(A2)="/",REPLACE(A2,LEN(A2),1,""),A2),LEN(A2)-SEARCH("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-(RIGHT(A2)="/")),1)-(RIGHT(A2)="/"))and
=MID(A2,SEARCH("://",A2)+3,SEARCH("/",RIGHT(A2,LEN(A2)-SEARCH("://",A2)-2))-1)
6 Replies
More universal
=RIGHT(IF(RIGHT(A2)="/",REPLACE(A2,LEN(A2),1,""),A2),LEN(A2)-SEARCH("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-(RIGHT(A2)="/")),1)-(RIGHT(A2)="/"))and
=MID(A2,SEARCH("://",A2)+3,SEARCH("/",RIGHT(A2,LEN(A2)-SEARCH("://",A2)-2))-1)SergeiBaklan This is excellent thank you.
Thank you for the great answers. The first longer formula works for me. The second outputs:
http://www.domain.com
And not the number between the last two slashes.
What if the number of digits in between the last two slashes are fluctuating? It seems the formula is fixed in that sense. EDIT: I was wrong. I checked the wrong cell. It seems to work as expected.
Peter, perhaps I misunderstood you with the second requirement. Anyway, first formula in second post shall return number independently of how many slashes you have, it only assumes URL starts from http:// or https://. But that also could be ignored, just the formula will be bit longer.
If you have exactly the same format, i.e. same number of slashes and https:// at the left, that could be
=MID(A2,SEARCH("@",SUBSTITUTE(LEFT(A2,LEN(A2)-1),"/","@",5))+1,LEN(A2)-SEARCH("@",SUBSTITUTE(LEFT(A2,LEN(A2)-1),"/","@",5))-1)and
=LEFT(RIGHT(A2,LEN(A2)-8),SEARCH("/",A2,3)+7)
