Extract numbers from cell after 5th / character

[eaxlns]

Hi All,
Hope some of you clever folk can help me. I wanted to extract the number in the following URL, it is after the fifth "/", and before the "?":

https://www.ebay.co.uk/itm/TomTom-GO-520-5-Sat-Nav-Wi-Fi-Lifetime-World-Maps-Traffic-Speed-Cameras-Updates/201742112656?hash=item2ef8c45790:g:3gEAAOSwBzpbjrNH

<colgroup><col width="87" style="width: 65pt;"></colgroup><tbody>
</tbody>

The number in this case is 201742112656.

Thanks in advance!

 

[sandy666]

with PowerQuery aka Get&Transform:

url	Text Between Delimiters
https://www.ebay.co.uk/itm/TomTom-GO-520-5-Sat-Nav-Wi-Fi-Lifetime-World-Maps-Traffic-Speed-Cameras-Updates/201742112656?hash=item2ef8c45790:g:3gEAAOSwBzpbjrNH	201742112656

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Between = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([url], "/", "?", 4, 0), type text)
in
    Between[/SIZE]

or

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.TransformColumns(Source, {{"url", each Text.BetweenDelimiters(_, "/", "?", 4, 0), type text}})
in
    Extract[/SIZE]

 

[Eric W]

Try:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"/","|",5))+1,LEN(A1)),"?",REPT(" ",LEN(A1))),LEN(A1)))

 

[eaxlns]

Thank you for your speedy reply, sandy666, but how can I do this as a formula, as I need the result in another sheet's cell?

 

[eaxlns]

Try:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"/","|",5))+1,LEN(A1)),"?",REPT(" ",LEN(A1))),LEN(A1)))

It worked, thanks a lot!

 

[sandy666]

this is a PowerQuery so you'll need Excel 2010/2013 and PQ add-in or 2016 and above with PQ (Get&Transform) built-in

Formula is in post #3 by Eric W

 

[Eric W]

Glad we could help.

 

[DanteAmor]

How about:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"/",REPT(" ",500),5),"?",REPT(" ",500)),500,500))

 

[Rick Rothstein]

One more...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"?","/"),"/",REPT(" ",500)),2500,500))