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 how to escape quotes in google sheets:

– To escape a single quote or apostrophe, add double quotes around it (“).

– To escape double quotes, use CONCATENATE method or add additional double quotes


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"
Escape quotes in Google sheets


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"
Escape quotes in Google sheets - issue


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:

Escape quotes in Google sheets - solution


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"
Escape single quote in Google sheets


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))
Escape quotes in Google sheets - concatenate formula


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.

Escape quotes in Google sheets - Query formula

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""")
Escape quotes in Google sheets - Query formula examples


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

How to add single quotes in google sheets?

To add single quotes in a cell, you have to escape it with double-quotes. Simply add double quotes around the whole sentence and google sheets will interpret the whole sentence as is.

How to escape commas in google sheets?

You can escape commas in google sheets using double quotes around it.

How to add quotes to a column in google sheets?

If you want to add double quotes around the value of the cells in a column, then you can use one of the 3 methods discussed here. You can do it manually like this =””””& A1 &”””” or using the concatenate method with char(34) prefix and suffix.


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

30+ smart tools to supercharge your sheets