How to Put Quotation Marks in Excel Formulas

September 18, 2012

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.

Quotation Marks

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 :)

Does this article help you? If so, please consider supporting me with a coffee ☕️

Buy Me a Coffee at ko-fi.com

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:

Quotation Marks part 2

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!)).

Come on, Joseph! 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.


Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Buy Me a Coffee at ko-fi.com

Written by Joseph who loves teaching about Excel.

© 2021, Spreadsheets Made Easy