How to Add Quotes to Your Cells in Excel Automatically
April 30, 2012 / Updated: October 24, 2021 / Lena Shore
Filed under: Tutorials
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.
Thank you so much for this help. I had to format a large amount of data for import into a Snowflake table which involved adding parenthesis, commas, and single quotes. After playing around with your formula, i was able to format all of it without typing it all by hand. THANKS SO MUCH!!!
You are so welcome! Glad it helped! (:
In my case, fully 2/3 of the TEXT cells get replaced by #################################. No. The column is not too narrow. No, Wrapping/multi-line doesn’t make a difference. Neither does “Hidden” under Protected. If try to use ANY delimiter with @ (|@|, ‘@’, ***@***) I get all hashtags. And the POS application EXPORTS IT THAT WAY too!
Are you copy/pasting? Perhaps it is replacing quotes with curly quotes? Or maybe in included a gremlin or a space? Might be worth trying to retype by hand.
First of all: thanks for that little trick, helped me a lot.
I have a question regarding the same issue, showing #################### as content after formatting the cell. I figured out, that as long as the char count is equal or smaller than 253, the original content will be shown, adding another char it shifts to#. My guess is, that after formatting the cell with \”@\” the cell count somehow adds those chars in edition to the 253 text chars and any sum over 256 will be displayed as #.
Can you or someone confirm that in any way? Also, is there a workaround to make texts with more than that 253+x chars?
Thanks in advance!
I did a search and found that text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&). (:
Thanks for your research and your answer! Sorry, I just saw your reply.
I will try to work with the function or the operator, for now I just copied and pasted the cut off text manually in the file after saving it as *.csv.
Thank you for this!!!
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.
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.
Great help! But does not work for numbers. Example – Results
”spifashion1992”
44262549
”jjoshva”
”skm1522”
55821503
It worked for me! But when saving as CSV, the quotes were saved in threes, because it thought I wanted them in my data as opposed to using them to delimit my strings.
SO INCREDIBLY HELPFUL!!! Thank you 🙂
For LibreOffice – Calc
Format -> Cells -> Text -> Format Code -> “@”
Will do the trick for anyone using LibreOffice
when doing this in office 365 it create “””BAGS”””
Thanks !
Thank you!
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!
Unix-like escape characters work as well. Format –> Cells –> Custom \”@\” works
Thanks!
This is awesome! I have been looking for this solution for weeks. This will save me tons of time. Many thanks!
So happy it was helpful!
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!
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.
This solved my problem!! Thank you Lena!
your post is really awesome, It do a lot of help ty !
Thank you so much its works amazing
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.
Thanks Dan! I really appreciate your input. Great information!
To print ONE DOUBLE QUOTE character you type FOUR DOUBLE QUOTE characters into the formula.
EG:
=”””” & “Hello” & “”””
=”””” & A1 & “”””
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
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.
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.
Hmmm. Did you check to make sure the cell’s properties are set to “date”?
Use this custom format – \”m/d/yyyy\”
Try opening the CSV after that. I get three quotes on each side.
“””6/12/2019″””
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.
Fantastic. Thanks for the tip. I didn’t even occur to me since I assumed Excel would be different.
Even “@” is not working for me to add codes to a number.. can you help?
Try \”#\” instead for numbers.
Worked for number!
Perfect the \”#\” worked great for number thanks.
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.