Insert double quotes in an Excel formula

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:

Error message for incorrect use of double quotes

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.

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

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.

Conclusion

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 🙂

Don’t forget:

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “Insert double quotes in an Excel formula

Leave a Reply

Your email address will not be published. Required fields are marked *