Need help on a formula to extract specific content between different characters

[rajeesh31]

Hi,

In the following example, I wish to extract all the content from the last "/" and upto the "_".

product/r3.8/mr1.0/module/epic/1234_userstoryname.

So here i need to extract only 1234. (That is the value from the last "/" till the "_")

Please note that there could be any number of "/" in text. I need to extract only from the last "/" till the first "_"

Please help

[sandy666]

removed by me
i didn't check massive attack of "/"

[rajeesh31]

Thanks for your quick response. Your formula returns value from the first "/" up until the first "_". I need to extract the text from the last "/" up until the first "_"

[FDibbins]

Hi, welcome to the forum :0

This is really long-winded, Im sure someone will give you a better method. With your string in A1...
=LEFT(MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))+1,99),FIND("_",MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))+1,99))-1)

[sandy666]

correction
=IFERROR(MID(SUBSTITUTE(A1,"/","#",5),FIND("#",SUBSTITUTE(A1,"/","#",5))+1,FIND("_",A1,FIND("#",SUBSTITUTE(A1,"/","#",5))+1)-FIND("#",SUBSTITUTE(A1,"/","#",5))-1),"Check characters")
but i see Ford did it

[shukla.ankur281190]

Or Try some shorter

B1

Please Login or Register  to view this content.

[rajeesh31]

Thanks a lot Ford and Sandy. Both the formulas work splendidly. Exactly how I wanted. You guys are truly stars

[sandy666]

Check it with more examples because my formula works only if there is 5 "/"s
If that takes care of your original question, please click Add Reputation then select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[shukla.ankur281190]

Good to hear Rajeesh that both formula works great, I have provided shorter version.

Mr. Ford & Sandy , Numbers are retrieving in text format still....

[FDibbins]

Happy to help and thanks for the feedback

[FDibbins]

Good to hear Rajeesh that both formula works great, I have provided shorter version.

Mr. Ford & Sandy , Numbers are retrieving in text format still....

If OP needs them to be real numbers, just add -- at the start...
=--LEFT(MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))+1,99),FIND("_",MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))+1,99))-1)

[rajeesh31]

That's right Sandy. With one of the samples I have it has retrieved the value from the fifth "/" and not from the last "/". Ford's formula seems to work for the samples I have tried.

[sandy666]

Mr. Ford & Sandy , Numbers are retrieving in text format still....

OP said:

I wish to extract all the content...

but nothing about format.


enough add in front of formula double unary "--" and it will be as number

[rajeesh31]

Thanks a lot Ankur. The shorter formula you gave worked for the example I have given. But it threw up a Value error for other samples I have.

Ex) Product / R3.8 / MR1.0 / CAR/ 4.5.2.17_Create Booking From Cart / ABCD34-44645_Booking

The above example, threw a value error.

[FDibbins]

I should have asked if you had more than 1 _

[rajeesh31]

Hi Ankur,

I don't want it as a number as it would be a mix of alphabets and numbers. Sorry that I missed to mention that in the example I had given initially.

[rajeesh31]

There could be more than 1_ Ford. But all I want is the value from the last "/" to the first "_"

[JohnTopley]

removed by JT

[shukla.ankur281190]

More shortest try

Please Login or Register  to view this content.

[rajeesh31]

That works Ankur. Thank you..

[shukla.ankur281190]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Please add reputation by clicking star icon left corner on solution post.