Within Excel formulas, double quotes are used to denote the start and end of a text string. Therefore, the obvious question is: how to add double quotes in Excel?
Look at the following formula as an example:
="Here is some sample text"
The double quotes are at the start and end of the text string. But what if we want the text string to include double quotes, how do we do that? We can’t use the following:
="Here is some "sample" text"
If we try this, it will result in an error message: “There is a problem with this formula….”
Excel doesn’t provide a solution here; it just tells us there is an error.
We’ll cover the best options to solve this problem in the remainder of this post.
Using single quotes instead
This first solution is almost too simple to mention. But why go for a more complicated option if this meets your requirements?
If we want to indicate that something is a special name, then maybe single quotes will be a suitable alternative.
="Here is some 'sample' text"
Single quotes have their own special meaning in Excel, but when enclosed within double quotes, they are treated as text
Many times, this won’t be a good solution. For example, if we are using the shorthand method for noting feet and inches, we must use the single-quote and double-quote characters (e.g., 6’2″ is shorthand for 6 feet 2 inches). In this circumstance, this simple solution won’t work, so let’s look at some alternatives.
Double quotes using an escape double quote
Using an additional double quote character serves as an escape character. Take a look at the following formula:
="Here is some ""sample"" text"
By using two “ characters next to each other, the first is an escape double quote. this tells Excel to recognize the second “ as text and ignore the first.
In our example, if the word sample were in cell A1, we could concatenate the string using the & symbol. But we would also need to add additional escape double quotes to indicate the start and end of each text string.
="Here is some """ & A1 & """ text"
In the formula above:
- Blue: the start or end of each text string
- Red: the double quote escape character
- Green: the displayed double-quote character
It works, but it’s starting to become harder to read.
Double quotes with CHAR function
The third solution is to use the CHAR function.
The escape character method demonstrated above can start to become a bit confusing; there are just too many quotation marks to easily understand what’s going on. This is where the CHAR function may be a better option.
The CHAR function returns a specific character based on a code number from the operating system’s character set. In a standard character set, CHAR(34) is the double-quote character.
Let’s look at this using an example:
="Here is some "&CHAR(34)&"sample"&CHAR(34)&" text"
We still need double quotes to start and end the text strings, but we can use the & character and CHAR(34) to add double quotes into the result of our formula.
Add double quotes automatically with custom number format
The fourth option is quite a niche solution. It is only relevant where we wish an entire text string to be enclosed on double quotes. We can create a custom number format that automatically provides the appearance of double quotes.
- Click Home > Number (section) > Drop-down > Select More Number Formats… (or press Ctrl + 1) to open the format cells dialog box.
- Select the Number tab, click Custom
- In the Type box enter: \”@\”
- Click OK to apply the number format.
If you enter any text into a cell with the number format applied, it automatically appears in double quotes.
NOTE: This does not add double quotes to the value, but formats the cell to give the appearance of double quotes.
Well, that’s it. We’ve looked at four options you can choose to insert double quotes into an Excel formula. But which is the best?
Like most things in Excel, there is no “best”. So, it comes down to your personal preferences. Whichever is the easiest for you to use, and meets your requirements, do that.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: