• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1314
  • Last Modified:

How do I Parse a Portion of a URL

I am looking to see how I can take a full URL in one column and take only part of that URL and populate it in the next column.  

For example, column b would have www.mysite.com/this/is/the/URL_1234.html.  From that I would populate column c with just URL_1234.html.

And yes, the URL_ portion is consistent in all URLs.

Any ideas?

Thank you for any help you can provide!
0
jhoekman
Asked:
jhoekman
  • 3
  • 2
  • 2
  • +4
3 Solutions
 
barry houdiniCommented:
This formula takes everything after the last "/" for data in A1

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

regards, barry
0
 
andrewssd3Commented:
If you're saying that theurl will always start with URL_ you could enter into column C:
=RIGHT(B1,LEN(B1)-SEARCH("/url_",B1))

Open in new window

0
 
HellmarkCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
andrewssd3Commented:
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 :)
0
 
kgerbChief EngineerCommented:
Good one Barry,
The best I could come up with was this mess.

=RIGHT(A1,LEN(A1)-FIND("***",SUBSTITUTE(A1,"/","***",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

Kyle
0
 
barry houdiniCommented:
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
0
 
andrewssd3Commented:
Amazing, and it was so simple...
0
 
jhoekmanAuthor Commented:
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!  :)
0
 
jhoekmanAuthor Commented:
Nothing short of awe-inspiring.  

Thanks for all the help, guys!
0
 
aikimarkCommented:
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)

Open in new window

0
 
Patrick MatthewsCommented:
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
0
 
aikimarkCommented:
@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.
0
 
Patrick MatthewsCommented:
I don't think I was disagreeing with you :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now