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"
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
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 &”
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:
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:
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 ”
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.
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.
 QUERY formula in google sheets – Link
New to google sheets ? Start here