CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
bpy
bpy

Reputation: 1180

How to get string after 3 slash in excel

I'm having difficulties to find a way of getting the string before the last slash in excel 2007 formula.

https://www.example.com/text13611283/url_complement

The string I need is this: text13611283

Upvotes: 2

Views: 1685

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

The String Between the Last Two Occurrences of a Slash (/) in Cell A1

Formula

=MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))+1,FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))-1)

How?

enter image description here

How Formulas

=LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))
=SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))
=FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))
=LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1
=SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)
=FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))
=MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))+1,FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))-1)

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

=INDEX(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),IF(ROW(INDEX($A:$A,1):INDEX($A:$A,255))=1,1,(ROW(INDEX($A:$A,1):INDEX($A:$A,255))-1)*99),99)),4)
  • Replace each / with 99 spaces
  • Using MID, create an array of each 99-space separated element in the string
    • =IF(ROW(INDEX($A:$A,1):INDEX($A:$A,255))=1,1,(ROW(INDEX($A:$A,1):INDEX($A:$A,255))-1)*99) creates an array to be used for the start argument of the MID function. It returns an array of {1,99,198,297,...}
  • TRIM to get rid of the extra spaces
  • INDEX to extract the correct element. In this case, it would be 4.

enter image description here

Upvotes: 1

kmShelke
kmShelke

Reputation: 91

There are multiple ways to do this task based on input value Case 1: you can Delimit the column by using menu "DATA/Text to Columns"

Case 2: Assuming your text is in A2 Cell the formula in B2 will be "=LEFT(MID(A2,FIND("/",A2,10)+1,100),FIND("/",MID(A2,FIND("/",A2,10)+1,100),1)-1)"

Upvotes: 4

Related Questions