Escape quotes in Google sheets

Double Quotes are used in google sheets to represent text or strings.

When you are working with formulas and queries, many times you will deal with quotes and special characters. If you do not handle them with care then the formula or query will throw errors while executing. The common error type is parsed errors.

In this tutorial, we will learn how to escape quotes in google sheets. Let’s jump in.

Escaping is a method where you are telling google sheets to interpret the characters literally. 

Here is a quick video showing this in action:

Let’s explore some real-life problems and see escaping in action. 

How do escape double quotes in a formula?

You have the below text that you want to see in a cell.

Tom likes Jerry

To show this in cell A1, all you have to do is, write:

="Tom likes Jerry"

Now lets say you want to show like this:

Tom “likes” Jerry

If you change the formula like below, it will throw a parse error.

="Tom "likes" Jerry"

The reason is the additional double quotes. So how do you solve this?

You need to escape the quotes that appear around the word “likes”.

Here is how to do this:

="Tom ""likes"" Jerry"

We have added an extra set of double quotes around. Here is the result:

When you use two quotes ( “ ) next to each other, Google sheets recognises the first one as an escaping quote and the second one as a plain text. 

How to escape single quotes in a formula?

To escape single quotes, add an extra pair of double quotes around the text.

Lets say you want to show a text like below in cell A1:

Tom ‘likes’ Jerry

If you write like below, then it will throw a parse error:

=Tom ‘likes’ Jerry

The correct way to do this is to add a pair of double quotes surrounding the whole sentence. You can write:

="Tom 'likes' Jerry"

Because we have a double quote that surrounds the whole sentence, google sheets will consider the entire sentence as a text value and will print as is.

Escape quotes in google sheets while referencing a cell value

Method 1:

Let’s say A1 has a text like below

Tom likes Jerry

In cell B1, you want to refer to cell A1 but also add a double quote around it to get a result like this

“Tom likes Jerry”

Doing these won’t work

• =”A1”
• =”& A1 &” 

You need to do like this

=""""& A1 &""""

Here is the step by step explanation?

First, let’s reference the cell A1 value. To do so you can write: “”& A1 &””

When you do this, cell B1 will output  Tom likes Jerry

Now, all you need is a pair of double quotes.

To achieve this you need to add 2 pairs of double quotes. One is for adding a double quotes and another is for escaping the quotes of step-One.

So the final formula will look like this  “”””& A1 &””””

Method 2: using char value and &

You can also achieve the above by doing this:

=char(34)&A1&char(34)

Note that char(34) is the same as double-quotes.  

So this formula will add a prefix and suffix as double quotes around the value of A1.

Method 3: CONCATENATE formula to escape quotes in google sheets

How to concatenate double quotes with text in google sheets?

Use the CONCATENATE formula like this:

=CONCATENATE(char(34),A1,char(34))

Here, the CONCATENATE formula will add the first and last parameter around the value of cell A1.

Escape quotes in google sheets inside a query formula

Let’s discuss the below example,

Let’s say we have a table like this.

In cell B1 you want to run a query to select the row in column A where the text “Tom likes Jerry’s toy” is not present.

The query would look like this:

=QUERY(A1:A3,"Select A Where A<>""Tom likes jerry's toy""")

Notice the double quotes around the sentence. Here 2 double quotes are next to each other.

So as per the rule, google sheets will escape anything inside the inner double quote and treat it as plain text.

“ The query formula in google sheets has an inbuilt method of adding a backslash (\) before single quotes to escape them but it does not work with double quotes ”

FAQ

Wrapping up

In this tutorial, we learned how to escape quotes in google sheets. The concept of escaping is very important to learn when working with formulas and queries. 

I am sure you have learned how to do this and are confident to try it in your next project. Till then keep learning.

Appendix

[1] QUERY formula in google sheets – Link

Further Reading

New to google sheets ? Start here

More related to Formulas:

Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets