CODEWITHSUNDEEP
google-sheets-formula
bboooooyintheuk
bboooooyintheuk

Reputation: 143

How to get Google Sheets to ignore quotation marks inside a formula

I am trying to create a formula in Google Sheets that will take a HTML tag as text, and lookup a value in another cell to add a unique URL to it.

For example:

<img class="test-tmn-pxl" data-src="[impression_url_here]" width="1" height="1" style="display:block;margin-bottom:-1px;" />

where impression_url_here is replaced with a URL from cell A1 (as an example).

However, because everything else apart from impression_url_here will stay exactly the same, when I try to add it to my formula as text it's just picking up the first set of qutation marks and closing the text portion of the formula early.

Here's what I have so far:

="<img class="test-tmn-pxl" data-src="["&=A1&"]" width="1" height="1" style="display:block;margin-bottom:-1px;" />""

Can someone please point me in the right direction?

Thanks!

Upvotes: 5

Views: 24373

Answers (2)

Badreddine Leghrib
Badreddine Leghrib

Reputation: 187

You can use the formula, this make the text same as it looks in the google sheet preview.

clean(A1)

where the Parameter A1 is the content that you want to clean.

Upvotes: 1

Karthik Sivasubramaniam
Karthik Sivasubramaniam

Reputation: 673

The first double quote in your formula indicates the start of the text, and the next double quote indicates the end of it, even if you put it in the middle of your text. As far as Google sheet is concerned, your text ended at the second double quote. If you want to pack some HTML string values within the final output of the cell, use single quotes to differentiate them.

="<img class='test-tmn-pxl' data-src='[" & A1 & "]' width='1' height='1' style='display:block;margin-bottom:-1px;' />"

I don't get what the square brackets are there for, but I'll leave it to you.

[Update]

If you do want to use double quotes for your HTML strings as well, the escape character in Google sheets is, [sigh], double quote. So if you want to output " without breaking the string, your formula should say "".

Here's the full suggested formula:

="<img class=""test-tmn-pxl"" data-src=""[" & A1 & "]"" width=""1"" height=""1"" style=""display:block;margin-bottom:-1px;"" />"

Upvotes: 10

Related Questions