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
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
B2 which is
amazing. And finally, a period and then another set of
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!)).
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.