How to Add Quotes to Your Cells in Excel Automatically

April 30, 2012 / Updated: July 14, 2016 / Lena Shore

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

Lena is a full-time freelancer and nerd that specializes in web development, graphic design, and illustration. She enjoys building things, learning new things, pursuing creative endeavors, and giving free advice.


  1. V on December 19, 2019 at 5:47 pm

    Thank you for this!!!

  2. JohnT. on August 9, 2019 at 3:22 pm

    I couldn’t see the quotes on number fields either, so I appended a letter to the front of each number and then applied the quotes. Because I was generating auto-incrementing numbers, I didn’t have to worry about adding the letter one-by-one; I just dragged the cell down. I exported this from Excel to csv format.

    The quotation marks are different and probably won’t work if you’re doing DB or API work. I got around this by opening my quoted csv file in Notepad, and doing a Replace of the curved quotation mark (that you copied and pasted from the beginning of the this page) and replaced it with a straight double quote (ie a normal quotation mark).

    For getting rid of the letter I didn’t want, in Notepad I did a Replace, FIND ”a1000 (this is the curved quote mark+a+1000) and REPLACE “1000 (a straight quote and 1000). My numbers were 8 digits long and I only wanted to get rid of the a and keep the least significant digits, so that did the trick.

  3. JohnT. on August 9, 2019 at 2:40 pm

    I could not get to work in Excel 365 until I actually copied and pasted what she listed above – then it worked.

    Might have to do with the varieties of double quotes Microsoft has.

    I also noticed Microsoft is no longer displaying the version in Help>About.

  4. Maha on July 26, 2019 at 4:21 am

    Great help! But does not work for numbers. Example – Results


  5. stephanie on April 30, 2019 at 1:15 pm

    SO INCREDIBLY HELPFUL!!! Thank you 🙂

  6. Doug on April 21, 2019 at 3:47 am

    For LibreOffice – Calc

    Format -> Cells -> Text -> Format Code -> “@”

    Will do the trick for anyone using LibreOffice

  7. irperera on March 24, 2019 at 9:50 am

    when doing this in office 365 it create “””BAGS”””

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

    Thanks !

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

    Thank you!

  10. 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!

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

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

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


  13. 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!

  14. 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!

  15. 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.

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

    This solved my problem!! Thank you Lena!

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

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

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

    Thank you so much its works amazing

  19. 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!

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

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

  21. 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?


    • 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.

  22. 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”?

    • jm on June 11, 2019 at 10:47 pm

      Use this custom format – \”m/d/yyyy\”

      • ELK on July 2, 2019 at 10:39 am

        Try opening the CSV after that. I get three quotes on each side.

  23. 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.

    • Ranjith Gowda on June 7, 2019 at 10:11 am

      Even “@” is not working for me to add codes to a number.. can you help?

      • Sze Ming Philip Chan on July 29, 2019 at 3:14 pm

        Try \”#\” instead for numbers.

        • Lalle on November 20, 2019 at 3:58 am

          Worked for number!

        • Jim on February 11, 2020 at 10:54 am

          Perfect the \”#\” worked great for number thanks.

  24. 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.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.