CODEWITHSUNDEEP
excelurlextract
Jesse
Jesse

Reputation: 3

How could I pull out multiple URLs from an Excel Cell?

In excel 2007 how could I go about extracting just the URLs from a cell containing the following?

"ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/73001099/images/ap1240__a.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/73001099/images/ap1240__b.jpg"

Some cells have up to 12 different URLs.

Ideally I could extract URL 1 & place it in its own column, then URL 2 and place it in its own column.

Upvotes: 0

Views: 2253

Answers (3)

user4039065
user4039065

Reputation:

With the first multi-URL in A2 put this formula into B2,

=MID(TRIM(MID(SUBSTITUTE(","&$A2, ",ITEMIMAGEURL", REPT(" ", 999)), COLUMN(A:A)*999, 999)), 3+(COLUMN(A:A)>9), 999)

Fill right an additional 11 columns. Fill down as necessary.

Upvotes: 2

nutsch
nutsch

Reputation: 5962

With the first multi-URL in A1, put this formula in B1

=MID($A1,FIND("IMAGEURL"&COLUMN()-1,$A1)+10,IF(ISERR(FIND("IMAGEURL"&COLUMN(),$A1)),LEN($A1),FIND(",",$A1,FIND("IMAGEURL"&COLUMN()-1,$A1))-10-FIND("IMAGEURL"&COLUMN()-1,$A1)))

Fill right an additional 11 columns. Fill down as necessary.

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

Without getting in to VBA, here's something that would work.

Have your cell(s) selected, and go to the Data tab, "Text to Columns". Choose "Delimited", and for the delimiter, in the "Other" blank, put =. Click "Finish" and Excel will separate the URLs in the same row.

Then, under those, you can enter this formula (assuming your data is in Row 1): =IF(LEFT(B1,4)<>"Item",IFERROR(LEFT(B1,SEARCH(",",B1)-1),B1),"") and drag over.

Your A1 will just have "ITEMIMAGEURL1", but in B1, it'll be blank. B2 will just be the URL of the first URL, C2 will be second URL, etc.

Here's a .gif to show you what I did.

Upvotes: 0

Related Questions