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
  • http://www.excelguru.ca Ken Puls

    Hey Joseph! Got to say, when I saw the single quote method I was wondering what the heck you were doing. LOL!

    • http://www.spreadsheetsmadeeasy.com 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.

  • Naveen Shandilya

    It s fine and simple and good.

    • http://www.spreadsheetsmadeeasy.com Joseph

      Thank you, Naveen!

  • 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

    • http://www.spreadsheetsmadeeasy.com Joseph

      Hi Lisa! I’m glad you like it and I agree. I’m sure someone has definitely used it! :)

  • Zev Spitz

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

    • http://www.spreadsheetsmadeeasy.com Joseph

      Thanks Zev! Forgot about that one

  • Gary Nolan

    The last method only seems to work if you are using quotation marks doubled (at the beginning and end of a phrase). When I attempt to use a single quotation mark, sadly it causes a formula error.

    Example: =”3/8″ drive Socket”

    There seems to be no way to use a singular quotation mark, unfortunately. Since my work involves using a lot of measurements, and inches is represented by “, I’m stuck having to use a separate row and entering into the data as the middle example suggests.

    • Joseph

      Hi Gary, you actually don’t need to have a matching set of two double-quote marks like “”my item””. Maybe I wasn’t clear in my post.

      I believe you’re looking for this formula:
      =”3/8″” drive Socket”

      Every instance of two double-quotes together withing a string formula will result in a single double-quote, so:

      =”3/8….Socket”

      The first and last double-quote mark match up to each other to create a string in the formula. And any single double-quote that you would like in the formula would just need to have two double-quote marks together like “”.

      I hope this helps!
      Joseph

      • Gary Nolan

        Brilliant! Thanks, Joseph!

        • Joseph

          Glad to help :)

  • vinit_jain.90

    Good explanation to use quotes in excel formula, although doesn’t sound very convincing and convenient to me.