How do I Parse a Portion of a URL

Depending on the version of Excel you have, you might be able to use VBScript, or some other scripting language, to parse through each character.

How I've seen similar things done is to have the script take the string length of the URL, and with a loop, start from the right most character, moving to the left, look until you've got a "/" character, and using the number of times it went through the loop to, work with the Right function to pass everything to the right of that character. In VBScript, it could be done with InStrRev, which would forgo most of the above (it could be used to automatically give you the position of the / without the need for loops or anything else). You'd get the position from InStrRev, then use Right to get everything to the right of that position, which gives you the file name.

Barry - I have seen some Excel formulas, but yours beats them all. It works but I'm totally at a loss to see how - would you please explain what on earth is going on there? I'm staggered :)

Hello andrewssd3,

Say you have this data in A1

xxx/yyy/bb bb/ZZZZ

then this part

=ROW(INDIRECT("1:"&LEN(A1)))

just generates an array of integers 1 to the length of A1, so in my example that will give this

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}

because there are 18 characters

That array forms the 3rd argument of FIND, so you get

=FIND("/",A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})

so FIND looks for "/" in A1 starting at char 1, char 2, char 3 etc. so that also returns an array like this

{4;4;4;4;8;8;8;8;14;14;14;14;14;14;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Note that the last number, 14, is the position of the last "/" in A1

When you lookup a "BigNum" in such an array the last number is always returned, so

=LOOKUP(2^15,{4;4;4;4;8;8;8;8;14;14;14;14;14;14;#VALUE!;#VALUE!;#VALUE!;#VALUE!})

returns 14

That's the position of the last backslash so we can use REPLACE function to replace all the characters from 1 to 14 with nothing

=REPLACE(A1,1,14,"")

so clearly that returns everything after the last /

I think I first saw that formula used by Harlan Grove, I don't know if he originated it...

Here's a completely different way to do the same thing

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))

regards, barry

Amazing, and it was so simple...

Wholly crap!  That's an amazing formula!  

Usually I can follow along, but you have blown me away, barry.

If I could assign bonus points, I would, but I can't.  So, in order to be somewhat fair, I am going to give points to the three formulas that technically worked, but weigh it heavily toward barry's, because, well, that was just bad-ass!

No wonder his name is barry-friggin-houdini!  :)

Nothing short of awe-inspiring.  

Thanks for all the help, guys!

Very clever, Barry.  You took a tally table approach to the problem, which I didn't think was possible in Excel.

Once you've found the last slash, you can also use the Mid() function.

=MID(A1,LOOKUP(2^15,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1)))))+1,255)

Select allOpen in new window

aikimark,

What barry did was essentially to create an array formula--which allows Excel to work table-wise--without using the usual Ctrl+Shift+Enter.  Some functions allow that array functionality without actually using the 'CSE'; LOOKUP is one example, and SUMPRODUCT and INDEX are others.

barry does come up with mind-blowing formulas :)

Patrick

@Patrick

I see little difference between a table of sequential numbers (Tally Table) and the array of sequential numbers that the heart of Barry's most clever formula.

I don't think I was disagreeing with you :)