Find different strings in cell

I want to solve this only using Excel formulas (no VB scripts). Here is column A, every row is another cell (I replaced the colons in the URLs to stars because I couldn't post them):

dkfl kdf l sfljdsgfs http*//www.ebay.com/haha.jpg" sfkld fk
dl san djfms djd http*//www.amazon.com/ka.jpg" js ka jfo jdjs http*//www.google.com/lilo.jpd fd sfdg
sdf sdf jpg https*//www.microsoft.com/img.jpg" kkl lla

The goal is to extract all the URLs in each cell. If possible, also separated by a comma (but just getting them would be good, I can add a comma later to the results).

I used in cell B1 (and then to B2 and B3) =LEFT(A1,FIND("jpg"" ",A1)+2) which gave me:

dkfl kdf l sfljdsgfs http*//www.ebay.com/haha.jpg
dl san djfms djd http*//www.amazon.com/ka.jpg
sdf sdf jpg https*//www.microsoft.com/img.jpg

and in cell C1 I further used the results with =RIGHT(B1,LEN(B1)-FIND(" http:",B1))

Results in column C:

http*//www.ebay.com/haha.jpg
http*//www.amazon.com/ka.jpg
https*//www.microsoft.com/img.jpg

All is good except cell A2 has two different URLs and I could only get one. What is a better method to extract all URLs? Is it possible with only Excel formulas?

回答1:

at least you have to put this formula ..

= MID(A1,INSTR(A1,"http"),INSTR(A1,"jpg")-INSTR(A1,"http")+2)

回答2:

I am not entirely clear of your requirement (more than 2 in one cell / .jpd or .jpg / better method is subjective) but the formula below might suit:

=MID(A1,FIND("http",A1),LEN(A1)-FIND("http",A1)+1)&"//"&C1&"/"&LEFT(D1,FIND(".jpg",D1)+3)&IF(ISBLANK(F1),"",",,"&MID(D1,FIND("http",D1),LEN(D1)-FIND("http",D1)+1)&"//"&F1&"/"&LEFT(G1,FIND(".jpg",G1)+3))

if columnA is first separated into columns (Data > Data Tools > Text to Columns, Delimited, Other: /, Finish).