SOLVED
Home

Extracting text between two instances of a character

%3CLINGO-SUB%20id%3D%22lingo-sub-792996%22%20slang%3D%22en-US%22%3EExtracting%20text%20between%20two%20instances%20of%20a%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792996%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20URL%20strings%20in%20cells%20in%20a%20column.%20In%20another%20sell%20I'd%20like%20to%20extract%20a%20portion%20of%20the%20URL%20string.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20the%20string%20in%20A2.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.domain.com%2Flpa%2Foffer%2F1452258783%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.domain.com%2Flpa%2Foffer%2F1452258783%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20URL's%20always%20have%20the%20same%20format.%20I%20want%20to%20extract%20the%20last%20digits%20between%20the%20last%20two%20slashes%2C%20that%20is%26nbsp%3B%2F1452258783%2F%20so%20that%20cell%20B2%20contains%26nbsp%3B1452258783.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20was%20elaborating%20with%20LEFT%2C%20RIGHT%2C%20MID%20and%20LEN%20functions%20but%20couldn't%20get%20it%20right.%20It%20would%20be%20interesting%20to%20have%20two%20formulas.%20One%20extracting%20text%20between%20the%20last%20two%20slashes%20couting%20the%20slashes%20from%20the%20left%20(5%3Ath%20slash%20from%20the%20left)%20and%20another%20forumla%20counting%20the%20slahses%20from%20the%20right%20(between%20first%20and%20second%20slash).%3CBR%20%2F%3E%3CBR%20%2F%3EDoes%20someone%20have%20any%20suggestions%20how%20to%20solve%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-792996%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793055%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20text%20between%20two%20instances%20of%20a%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793055%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F243408%22%20target%3D%22_blank%22%3E%40Peter%20Johansson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20exactly%20the%20same%20format%2C%20i.e.%20same%20number%20of%20slashes%20and%20https%3A%2F%2F%20at%20the%20left%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DMID(A2%2CSEARCH(%22%40%22%2CSUBSTITUTE(LEFT(A2%2CLEN(A2)-1)%2C%22%2F%22%2C%22%40%22%2C5))%2B1%2CLEN(A2)-SEARCH(%22%40%22%2CSUBSTITUTE(LEFT(A2%2CLEN(A2)-1)%2C%22%2F%22%2C%22%40%22%2C5))-1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DLEFT(RIGHT(A2%2CLEN(A2)-8)%2CSEARCH(%22%2F%22%2CA2%2C3)%2B7)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793097%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20text%20between%20two%20instances%20of%20a%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793097%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F243408%22%20target%3D%22_blank%22%3E%40Peter%20Johansson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20universal%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DRIGHT(IF(RIGHT(A2)%3D%22%2F%22%2CREPLACE(A2%2CLEN(A2)%2C1%2C%22%22)%2CA2)%2CLEN(A2)-SEARCH(%22%40%22%2CSUBSTITUTE(A2%2C%22%2F%22%2C%22%40%22%2CLEN(A2)-LEN(SUBSTITUTE(A2%2C%22%2F%22%2C%22%22))-(RIGHT(A2)%3D%22%2F%22))%2C1)-(RIGHT(A2)%3D%22%2F%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DMID(A2%2CSEARCH(%22%3A%2F%2F%22%2CA2)%2B3%2CSEARCH(%22%2F%22%2CRIGHT(A2%2CLEN(A2)-SEARCH(%22%3A%2F%2F%22%2CA2)-2))-1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793133%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20text%20between%20two%20instances%20of%20a%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20great%20answers.%20The%20first%20longer%20formula%20works%20for%20me.%20The%20second%20outputs%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.domain.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.domain.com%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20not%20the%20number%20between%20the%20last%20two%20slashes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20if%20the%20number%20of%20digits%20in%20between%20the%20last%20two%20slashes%20are%20fluctuating%3F%20It%20seems%20the%20formula%20is%20fixed%20in%20that%20sense.%20EDIT%3A%20I%20was%20wrong.%20I%20checked%20the%20wrong%20cell.%20It%20seems%20to%20work%20as%20expected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793167%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20text%20between%20two%20instances%20of%20a%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F243408%22%20target%3D%22_blank%22%3E%40Peter%20Johansson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPeter%2C%20perhaps%20I%20misunderstood%20you%20with%20the%20second%20requirement.%20Anyway%2C%20first%20formula%20in%20second%20post%20shall%20return%20number%20independently%20of%20how%20many%20slashes%20you%20have%2C%20it%20only%20assumes%20URL%20starts%20from%20http%3A%2F%2F%20or%20https%3A%2F%2F.%20But%20that%20also%20could%20be%20ignored%2C%20just%20the%20formula%20will%20be%20bit%20longer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793396%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20text%20between%20two%20instances%20of%20a%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F243408%22%20target%3D%22_blank%22%3E%40Peter%20Johansson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20is%20a%20consisting%20pattern%20you%20could%20try%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fusing-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EFlash%20Fill.%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Peter Johansson
Occasional Contributor

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

@Peter Johansson 

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

@Peter Johansson 

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)

@Sergei Baklan 

 

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.

@Peter Johansson 

 

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.

@Peter Johansson 

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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies