CODEWITHSUNDEEP
google-sheets
Adam Clifford
Adam Clifford

Reputation: 25

Google Sheets/Excel: IMAGE() formula into plain URL

I am converting a Google Sheets file into a CSV format.

Some cells have an image inserted into them via =IMAGE("https://placekitten.com/200/300")

I need to be able to extract in plain text https://placekitten.com/200/300

Is this possible? I've tried SUBSTITUTE but of course that doesn't work.

Thanks to whoever can solve this!

Upvotes: 1

Views: 398

Answers (2)

Martí
Martí

Reputation: 2861

I thought that explaining @player0’s answer would be nice, especially for future people.

His answer can be split in 2 parts:

  • Using FORMULATEXT to get the formula
  • Using a regular expression to extract the URL from it

The first part is FORMULATEXT. It is pretty self explanatory: it takes a cell and returns the formula as string.

The second part is using a regular expression (see wikipedia article and documentation on the exact syntax used by Google) to extract the URL. In this case @player0 relies on the fact that the URL is the only quoted text of that formula. So using the regex "(.+)" will match the quoted part inside the quotes of the formula, and thus only the URL. When using literal strings in a formula, you need to surround them with quotes (eg hello as "hello"). Double quotes need to be doubled (eg aaa"aaa is written as "aaa""aaa"). So "(.+)" becomes """(.+)""".

References

Upvotes: 1

player0
player0

Reputation: 1

try:

=REGEXEXTRACT(FORMULATEXT(A2); """(.*)""")

enter image description here

Upvotes: 2

Related Questions