Extracting a url from an IMAGE field

[algeciras]

Hi there,

So I have a column full of IMAGE data e.g.

=IMAGE("http://pbs.twimg.com/profile_images/479251403328585728/0uLakxHx_normal.png",1)

Now I am trying to load this into Tableau but it does not recognize the data and NULLs it.

I need to extract the url from this column.

Is there a formula that could copy the url and save it on a different column i.e.

http://pbs.twimg.com/profile_images/479251403328585728/0uLakxHx_normal.png

Thank you!

 

[Webbarr]

Hi,

Is the column containing the image data preceeded with a ' mark? Otherwise Excel reads this as a formula due to the "=" in there, which makes it slightly more difficult to extract information.

Let me know!

 

[algeciras]

Hello

Thanks for responding, not sure what you mean exactly. So I copied a screen grab of the said column.

 

[Webbarr]

Okay, should be a quick fix for this.

Highlight the entire of Column B, then you do a find & replace (ctrl f), you want to find a "=" (without speech marks) & replace with " '= " (without spaces, notice the apostrophe in front of the equals sign. Because of the equals sign in the cells in column B, Excel thinks you are trying to write a formula. If we put an apostrophe in front of it, it will recognize it as text.

Then, in C1, paste this formula -

=MID(B1,SEARCH(CHAR(34),B1,1)+1,SUM(LEN(B1)-SEARCH(CHAR(34),B1,1)+1)-5)

This formula will take the middle of the now text from B1, find the first set of speech marks (that is what char(34) is doing, and then find the rest of the text, up until .png / .jpg etc... whatever your file format is.

Let me know if this works for you, I hope this helps!

 

[algeciras]

Adding the apostrophe did the trick, thank you Webbarr! I can now load the data into Tableau easily.