Within Excel formulas, double quotes are used to denote the start and end of a text string. 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:
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 they are treated as text when enclosed within double quotes.
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.
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
Double quotes using an escape character
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 character, which tells Excel to recognize the second “ as text.
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 double quotes to indicate the start/end of each piece of the text string.
="Here is some """ & A1 & """ text"
In the formula above:
- Blue: the start or end of each text string
- Red: the escape characters
- 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 quote 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.
Well, that’s it. We’ve looked at 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, do that 🙂
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet 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: