CODEWITHSUNDEEP
excelvbaparsingexcel-formulaformula
Chris Vasquez
Chris Vasquez

Reputation: 63

Extract URL's from a Cell of Text in Excel

I have an Excel document with a column that contains cells of text (small paragraphs usually). I'd like to find a way (preferably a formula) to extract any url's those cells by row and add them to another column.

I've been messing around with MID and FIND quite a bit and can very easily get to the begginning of those URLs by searching for "http", but I can't figure out how to then find the length of the url so I can grab it.

Really looking forward to any help anyone could offer. It's driving me nuts!

Upvotes: 5

Views: 15712

Answers (4)

guitarthrower
guitarthrower

Reputation: 5834

To take into account the URL happening at the end of a string you'll need to add some error handling.

This should work for both mid string and end of string:

=MID(C11,FIND("http",C11),IFERROR(FIND(" ",C11,FIND("http",C11))-1,LEN(C11))-FIND("http",C11)+1)

Upvotes: 5

Chris Vasquez
Chris Vasquez

Reputation: 63

Ok, I think I've got it working. Check it out:

=MID(C11,FIND("http",C11),(FIND(" ",C11,FIND("http",C11))-FIND("http",C11))-4)

Upvotes: 1

durbnpoisn
durbnpoisn

Reputation: 4669

Your most likely option is to make sure you can rely on the URLs being formed the same way. Like, always start with "http" and always end with "/" or a ".com". Then you can do formulas to find the index start, index end, and grab the MID for everything in between.

It should look like this:

=FIND("http",D3)

returns the location of the first part.

=FIND(".com",D3)

returns the location of the end

=MID(D3,D9,D10-D9+4)

Returns the string provided by the start to the end+4 (to allow for those 4 characters)

Upvotes: -1

OTTA
OTTA

Reputation: 1081

Once you've identified the start position of the URL look for the first space after that, should be the end of the URL.

Upvotes: 0

Related Questions