Left Function Help

[james252]

HI Guys,

I am looking for a formula to only extract the category name form the following URLs.

1. http://www.directbargains.com.au/exe...tness-1-1.html
2. http://www.directbargains.com.au/binoculars-95-1.html
3. http://www.directbargains.com.au/kettles-4477-1.html

From the above URLs i need to extract only exercise-and-fitness, binoculars, kettles respectively.
I have also attached the example sheet.

Please Help.
example.xlsx
Thanks

[sktneer]

Assuming that the URL is in A2 then

Please Login or Register  to view this content.

[james252]

Sorry, i forgot to attach the file, =MID(A2,FIND("*",SUBSTITUTE(A2,"/","*",3))+1,FIND("-",A2)-FIND("*",SUBSTITUTE(A2,"/","*",3))-1), only extracts the first name like exercise but not exercise-and-fitness. Here i am looking to extract the content between "http://www.directbargains.com.au/" and "1.html". Please also refer to the attached example.xls file.

Thanks

[sktneer]

Try this.......

Please Login or Register  to view this content.

[james252]

Almost works, but still the formula is not working for the URLS below,

http://www.directbargains.com.au/fra...39s-156-1.html - Please refer to row no. 116
http://www.directbargains.com.au/men...ies-275-1.html - Please refer to row no. 213
http://www.directbargains.com.au/tap...ets-202-1.html - Please refer to row no. 147 and so on....

In above case the formula is only giving "fragrances-womenan", "menan", "tapsan" instead of "fragrances-womenand39s", "menand39s-accessories","tapsand44-misc-and-faucets" etc. Otherwise this the formula is working gr8.

Thanks

[AZ-XL]

Hi
My function is an array. Paste this to b2 cell and hit Ctrl+Shift+Enter

=LEFT(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))),""),MATCH(TRUE,ISNUMBER(IF(ISERROR(--MID(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))),""),ROW($1:$50),1)),"",--MID(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))),""),ROW($1:$50),1))),0)-2)

[AZ-XL]

My previous formula will give you same error.
Try this one
=LEFT(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))),""),FIND("#",SUBSTITUTE(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))),""),"-","#",LEN(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))),""))-LEN(SUBSTITUTE(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))),""),"-",""))-1))-1)