Replace specific text in cells (unique in each cell) with text in the column to the right?

[JackBTM]

Hi,

Long shot - but I have a sheet with around 700 rows.

In column A, there is paragraphs of text in each one. Inside these, there is various links ending in .jpg, .png etc - however I'm specifically looking to replace the .gif ones with the new .webp ones I have in column B.

I have done a VLOOKUP for the cells to the right, and they're in the correct order - I just need a way of replacing the URLs ending in .gif with the .webp ones.

Anyone able to help please? Would be hugely appreciated! Example below of how the cells look with column A and B.

<p>Test Test Test Test "https://mrexcel.com/test.jpg", Test Test "https://mrexcel.com/testing.gif".</p>

https://mrexcel.com/test1234.webp

Thanks
Jack

 

[Legacy 143009]

Try this in C1:

=IF(COUNTIF(A1,"*http*.gif*"),SUBSTITUTE(MID(A1,FIND("""",A1)+1,(FIND(".gif",A1)+4)-(FIND("""",A1)+1)),".gif",".webp"),SUBSTITUTE(MID(B1,FIND("""",B1)+1,(FIND(".gif",B1)+4)-(FIND("""",B1)+1)),".gif",".webp"))

 

[JvdV]

Book1
	A	B	C
1	<p>Test Test Test Test "https://mrexcel.com/test.jpg", Test Test "https://mrexcel.com/testing.gif".</p>	https://mrexcel.com/test1234.webp	<p>Test Test Test Test "https://mrexcel.com/test.jpg", Test Test "https://mrexcel.com/test1234.webp".</p>
Sheet5

Cell Formulas
Range	Formula
C1	C1	=LET(x,TEXTSPLIT(A1,""""),TEXTJOIN("""",,IF(RIGHT(x,4)=".gif",B1,x)))