Extract image paths from within html in a column

[tdunford7]

I've been trying to figure this out and just have been unable to. It's important that I figure out how to extract image paths that are in html code in all cells in a column. The image paths are not full url's. Here's an example:

<img src="images/stories/example.jpg" width="450" height="300" alt="Example" />

...and that code would be in the html code of an entire page and the html code is within a cell in a long column. What I need to figure out is how to exact this from that image code:

images/stories/example.jpg

...and that's it. Normally I would just Find & Replace something like "images" with a "|" and then copy the column into notepad and then paste it back into a new column in Excel and then open the text import manager thingy and then specify "|" and then I would have a column that starts with /stories/example.jpg. Then I would do the same thing with "jpg" and then I would have "/stories/example." then I would just add "images to the front of that and "jpg" to the back and be done with it, but this method isn't working because of the html code or something I don't know. So any suggestions would be GREATLY appreciated. Thanks.

[tigeravatar]

tdunford7,

Something like this should work for you:

Please Login or Register  to view this content.

[tdunford7]

Thanks, what column does the html cells need to be in and do I place that just in the adjacent column and copy down?

[tigeravatar]

I just opened a workbook and copy/pasted your example html string into cell A8, that formula went into cell B8. So if your html strings are in column A starting in A1, change the A8 to be A1 and put the formula in B1 and copy down.

[tdunford7]

Thank you, it worked on about 25% of the column. Here are some examples of ones that it didn't work on:

<img style="float: right;" alt="410fa" src="http://www.excelforum.com/images/stories/example/example/example/example.gif" width="200" height="200" />
<img style="float: right;" src="http://example.org/images/example.gif" />

I'm guessing that it didn't work on the ones above because the paths are different. I can use different functions like this over and over until they're all done, so how do I adjust that function so it will work on the examples above?

[tdunford7]

I suppose if I had to I could relocate all the images on my server to that same path, then it may work on all of them. Good thing here is that these paths don't need to stay the same because the site is within a different folder on my server, so moving the images would work. But if I end up doing that, will it also work on .gif images? On the ones that it did work on there are no gif images, just jpg and png.

[tdunford7]

Just want to also add that I really appreciate the fast response to my post!

[tigeravatar]

tdunford7,

In the three examples. the third one isn't an image, but it has a hyperlink reference to an image path. Do you need that extracted also, or would it be ignored because its not actually an image?

[tdunford7]

Oh, right, no i guess i don't.

[tdunford7]

I just learned about the left, mid and right functions of Excel and I should've learned about those a long time ago! This is a good video about it:

http://www.youtube.com/watch?v=jI5-A87Lv8I

[tigeravatar]

In that case, this should work for you:

Please Login or Register  to view this content.

[tdunford7]

ABSOLUTELY PERFECT!!!!!!!!!!! A million thanks. That column was in the hundred thousands, so this probably saved me about a week worth of work! Thanks so much for your help and also for helping so quickly. This forum is awesome!

[tigeravatar]

You're very welcome