Forum Discussion
Solved
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 al...
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)
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.
