 • 511K Members
• 4,981 Online
• 608K Conversations
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 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?

5 Replies

# Re: Extracting text between two instances of a character

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)``
Solution

# Re: Extracting text between two instances of a character

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)``

# Re: Extracting text between two instances of a character

Thank you for the great answers. The first longer formula works for me. The second outputs:

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.

# Re: Extracting text between two instances of a character

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.

# Re: Extracting text between two instances of a character

If it is a consisting pattern you could try Flash Fill.

Related Conversations