censo,
@ how many unique supplier URLs are there?
Perhaps something like this? Line breaks at each choice.
Formula:
=IF($A2="","",CHOOSE($O2,
TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE($A2,"-",REPT(" ",99)),99)),".",REPT(" ",99)),99)),
TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,".html",""),".",REPT(" ",99)),99)),
TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE($A2,"/product.html",""),"/",REPT(" ",99)),99)),
TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE($A2,"/",REPT(" ",256)),256)),"-",REPT(" ",99),1),99)),
SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE($A2,"/prod",REPT(" ",99)),99)),".ip","")))
With a lookup table in Y1:Z5 this helper column formula would go in O2 and filled down.
Formula:
=IF($A2="","",LOOKUP(2,1/ISNUMBER(SEARCH($Y$1:$Y$5,$A2)),$Z$1:$Z$5))
It's only function is to assign the first argument to the CHOOSE formula above.
It could probably be trimmed down somewhat.
Bookmarks