CODEWITHSUNDEEP
excel
Greg
Greg

Reputation: 1

Extract Text from URL in Excel

I'd like to do the following in Excel:

http://www.examplesite.com/ABCD123.php --> /ABCD123.php
http://www.examplesite.com/folder/EFG567.php --> /folder/EFG567.php

Any help will be highly appreciated.

Upvotes: 0

Views: 3992

Answers (3)

barrowc
barrowc

Reputation: 10689

This is quite hacky as it assumes that the address will always start with http://

=MID(A1,FIND("/",A1,8),LEN(A1)+1-FIND("/",A1,8))

Translated:

  • character position 8 is the first position after the http:// part
  • starting from pos 8, find the position of the first occurence of "/"
  • now subtract that position from the overall length of the string and add 1 to avoid losing the final character
  • now extract the substring from the position of that first "/" extending for the number of characters that we just calculated

Upvotes: 0

Alex P
Alex P

Reputation: 12497

This is more generic and is based on assuming that .com/ will always be in a web address (though clearly that assumption is not robust when one considers .co.uk etc.)

=RIGHT(A1,LEN(A1)-(FIND(".com/",A1,1) + 4))

Upvotes: 3

Greg
Greg

Reputation: 1

Actually figured it out:

=RIGHT(H8,LEN(H8)-26)

where 26 is the first 26 characters (http://www.examplesite.com).

There must be a more elegant/general solution for this though (i.e. Finding the number of characters before the ".com/")

Upvotes: 0

Related Questions