Help with parsing URL link using left and find functions

RKROX907

New Member
Joined
Mar 27, 2017
Messages
17
Hi Everyone,

Cell A1 has this text:
HTML:
https://somewebsite/images/products/SkuName/genricFileName.jpg


I would like to extract SkuName

The SkuName always will come after the 5th "/" and will end by the 6th "/"

the end result should be, B1 has the text: SkuName

Please help
Thanks!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,749
if it is a representative example you can try PowerQuery (Get&Transform)

urlurl
https://somewebsite/images/products/SkuName/genricFileName.jpgSkuName

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"url", each Text.BetweenDelimiters(_, "/", "/", {1, RelativePosition.FromEnd}, 0), type text}})
in
    #"Extracted Text Between Delimiters"[/SIZE]
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try with this formula

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:491px;" /><col style="width:120px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >https://somewebsite/images/products/SkuName/genricFileName.jpg</td><td >SkuName</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b> </b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/","#",5),"/","#",5),"#",REPT(" ",99)),99,99))</td></tr></table></td></tr></table> <br /><br />
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

How about this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">https://somewebsite/images/products/SkuName/genricFileName.jpg</td><td style=";">SkuName</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet647</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A1,"/",REPT(<font color="Purple">" ",255</font>)</font>),255*5,255</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

RKROX907

New Member
Joined
Mar 27, 2017
Messages
17

ADVERTISEMENT



Thank you jtakw, sandy666 & DanteAmor :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,959
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top