How to Add Quotes to Your Cells in Excel Automatically

This tip is going to be mostly helpful to people who working with databases. But, if you work with Excel and find yourself needing to deal with repetitive formatting, you might also appreciate the technique.

When working with CSV files you may need to add quotes to either side of a cell’s contents in order to upload them properly to your database. You can easily find yourself working with an Excel file from a workmate that needs to be formatted.

You got this:

But really need this:

This can be a big damn deal if you have hundreds of fields. No one wants to add this by hand. Fortunately, the solution is a snap! It all has to do with applying specific formatting to your cells.

  • Highlight the cells you want to add the quotes.
  • Go to Format –> Cells –> Custom
  • Copy/Paste the following into the Type field: \”@\”
  • Click “okay”
  • Be happy you didn’t do it all by hand.

NOTE: It’s always a good idea to open your file in a text editor to double-check your CSV export. Sometimes you need to do a little find/replace action to get your file picture perfect.

Posted in

Lena Shore

22 Comments

  1. Jonathan Grand on August 29, 2012 at 7:47 pm

    Thank you for the nice looking article, but as usual, Microsoft software is a complete disgrace. Only the text field show up with quotes in excel, and the exported .csv shows – not quotes – DOUBLE APOSTROPHES for each text field. Number fields are still the same.

    Once again, Microsoft’s software design is a diarrhea. First, what kind of ridiculous markup language is this? Quote-double-apostrophe-quote = ghost quote that will be translated as double apostrophe? wtf? I don’t follow the programming logic of whoever created this system.



  2. CSDN on September 27, 2012 at 10:33 am

    The PROPER way to do this is to use an escape character to inject the double quotes.

    The following will do what you need:
    “@”

    Unfortunately only a programmer would think to do this, leaving the average user out in the cold unless they had exceptional training in excel.



    • Lena Shore on September 27, 2012 at 11:10 am

      Fantastic. Thanks for the tip. I didn’t even occur to me since I assumed Excel would be different.



  3. PSNG on November 26, 2012 at 7:30 am

    That’s really help! Thanks.

    I would like to ask what if the field is not a word or text, it is a date with time, for example: 07/02/2013 08:00:00

    How do I get the double quotes in front and at the end? I have tried the method above but it just turned out changing it back to a numeric number like this 41312.3333333333.

    Thank you.



    • Lena Shore on November 26, 2012 at 9:07 am

      Hmmm. Did you check to make sure the cell’s properties are set to “date”?



  4. Robert Mac on December 17, 2012 at 11:52 pm

    Help! I’m so close here–I tried Lena’s solution, which gave me two single quotes, but not the double quotes that the CSV needs.

    I tried CSDN’s solution, but it gave me three sets of double quotes on each side of my field. Yeesh! I agree with Jonathan: this is a disgrace. It would be quicker for me to do it manually.

    Any other suggestions?

    -Robert



    • Lena Shore on December 18, 2012 at 8:05 am

      Just use the CSDN’s solution without the extra quotes in my example. Alternately, you could open your CSV and do a quick search/replace. Good luck.



  5. Dan on April 15, 2013 at 10:20 pm

    To print ONE DOUBLE QUOTE character you type FOUR DOUBLE QUOTE characters into the formula.
    EG:
    =”””” & “Hello” & “”””
    =”””” & A1 & “”””



  6. Dan on April 15, 2013 at 10:24 pm

    You cannot copy and paste my example because the comment was formatted and some of the quote characters changed from simple vertical double quote characters to diagonal speech mark quote characters. Just manually type four double quote characters into Excel and it should work.



    • Lena Shore on April 16, 2013 at 8:10 am

      Thanks Dan! I really appreciate your input. Great information!



  7. Ramesh on May 10, 2014 at 7:50 am

    Thank you so much its works amazing



  8. namubear on August 1, 2014 at 8:17 am

    your post is really awesome, It do a lot of help ty !



  9. Leif Gullberg on September 15, 2014 at 7:33 pm

    This solved my problem!! Thank you Lena!



  10. Jan De Man on October 24, 2014 at 3:19 pm

    In the excel version I use (Excel 2010 SP2), I had to write the format like this: \”@\” in order to get double quotes around my cell content.



  11. Brian on January 18, 2015 at 10:57 pm

    Hi. If any of the Excel experts is still monitoring this thread, I have a question. For Excel 2003 (yes, VERY OLD), how can I automatically add Double Quotes to text in all cells within a column such that those quotes will be recognized as text for a concatenation formula? When I tried the first suggestion, the quotes weren’t recognized. And, the other suggestions (implemented via Custom) didn’t add any quote marks. Ultimately, I can manually add the quotes around the content in the column but prefer to avoid that data/text entry requirement. Thanks!



  12. Nate on April 2, 2015 at 6:30 pm

    This is awesome! I have been looking for this solution for weeks. This will save me tons of time. Many thanks!



    • Lena Shore on April 3, 2015 at 7:59 am

      So happy it was helpful!



  13. rh on July 3, 2015 at 11:58 pm

    Thanks!



  14. AC on July 22, 2015 at 11:35 am

    Unix-like escape characters work as well. Format –> Cells –> Custom \”@\” works



  15. MegaTROM on August 12, 2015 at 12:32 pm

    Format –> Cells –> Custom \”@\”
    in Excel 2013 the csv file then gets saved with double-quoted double quotes: “””derp”””,”””herpaderp”””

    Find replace it is but damn!



  16. Nunyo on October 14, 2015 at 7:03 pm

    Thank you!



  17. Sunil on November 19, 2015 at 1:31 pm

    Thanks !



Archives