How to Put Quotation Marks in Excel Formulas

When you add text in a formula in Excel, you have to enclose the text with quotation marks (“…”).  This is useful for when you want to create a dynamic string that changes based on some conditions.  But what happens when you want to put quotation marks as part of the formula’s result?  In this post, I’ll be talking about how to put quotation marks in Excel formulas.

Let’s take a look at an example that we can work with.

And let’s also say we want to create a simple formula that concatenates (or puts together) the Title and what a newspaper company is saying about it.  However, we’re going to also say that we want to put whatever the quote from the NY Times is inside quotation marks, so how would we do that?

Here are some possible options you can use.

Use Two Single Quotes

This is not the best choice, but you could use two single quotes like so:

="NY Times calls the movie "&A2&" ''"&B2&".''"

If we break down this formula, it looks like the following:

="NY Times calls the movie "
&A2&
" ''"
&B2&
".''"

The first line starts off with the beginning of the static text that we have, which is wrapped by quotation marks.  Then it combines the value in A2 (by using the ampersand &) which is Some random title.  Followed by more static text, but this time it is a space followed by two single quotes.  Then the value found in B2 which is amazing.  And finally, a period and then another set of single quotes.

This results in:

NY Times calls the movie Some random title ''amazing.''

While this does work, it’s just cheating if you ask me :)

Put Quotation Marks Inside the Data

This is another clever, but cheap attempt at getting the solution we want, but it does work.  We have to change the data to enclose each quote inside quotation marks like so:

And the formula would be:

="NY Times calls the movie "&A2&" "&B2&"."

This results in:

NY Times calls the movie Some random title "amazing".

Now we have the period on the outside of the quotes, which isn’t so bad.  And yet again, while this method does work, seriously, who wants to go through all of their data and put quotes around each cell’s value (yes, yes, I know there are easy VBA hacks for this, but that’s outside the scope of this blog post (psst…if you want me to post about VBA, please comment below!)).

C’mon, Joseph.  Just get to it already!

Alright, fine :)

Adding Quotation Marks in Formulas

The magic to putting quotation marks in an Excel formula to have them show up as quotation marks in the result is actually in the quotation marks themselves!  Confusing?  Sorry.  This example should clear it up:

="NY Times calls the movie "&A2&""""&B2&"."""

Let’s split this up and take a closer look:

="NY Times calls the movie "
&A2&
" """
&B2&
"."""

This time around, to create the quotation mark, you put two quotation marks together inside the text portion of the formula.  The previous formula results in:

NY Times calls the movie Some random title "amazing."

That’s much better.

If that example is a little confusing, check this one out:

="I said it was ""awesome"" not ""awful""!!!"

This results in:

I said it was "awesome" not "awful"!!!

If you notice, the entire text is surrounded by quotation marks.  This tells the formula that everything inside those surrounding quotation marks is text.  When Excel runs into the two quotation marks together, it processes it as a single quotation mark in the result.

Learn Excel Dashboard Course Advanced Web Hosting for only $4/month
JosephHow to Put Quotation Marks in Excel Formulas

Comments 8

    1. Post
      Author
      Joseph

      Hi Ken! hahaha I bet that’s what you were thinking! I was just being cheeky :) I was actually trying to think of what people would try to do to get it to work if they didn’t know how and those came to mind.

    1. Post
      Author
  1. Lisa

    I liked the way you put it…”While this does work, it’s just cheating if you ask me :)” yes, definitely cheating lol but I am sure that someone out there had used it… lol

    1. Post
      Author
  2. Zev Spitz

    There’s also the CHAR(34) method: ="I said it was " & CHAR(34) & "awesome" & CHAR(34) & " not " & CHAR(34) & "awful" & CHAR(34) & "!!!"

    1. Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *


seven − = 3