MrExcel Publishing
Your One Stop for Excel Tips & Solutions

removing annoying quote marks from appearing

Posted by Josh on January 03, 2001 7:15 PM

Whenver I edit my tab delimited txt file in excel and then save, these quotation marks appear. This is very bad because I am interfacing it with my website and it gives me errors when the quotation marks appear. Does anyone know how to remove these (if I open up notepad, I can do it by hand), but I have 300+ items and I dont want to sit there all day.

Posted by Scott McGee on January 04, 2001 9:55 AM

Try using the "Find & Replace" command. We've got a database I get exports from that does the same thing. I've been able to use Find & Replace to clean these in an instant. (Edit-->Replace and then put your undesired quote in the "Find what" box and leave the "Replace with" box empty.

Hope this works for you!

Posted by Dave on January 04, 2001 6:40 PM

I don't think Edit>Replace will work, but I'm not sure. You may have to use =Clean(A1) and copy down and across, then PasteSpecial as values over the top.


OzGrid Business Applications

Posted by Josh Wex on January 05, 2001 9:06 AM

I am very new to excel. I searched documentation and I wasn't sure what you meant by using =Clean(A1) and then using PasteSpecial. How exactly do I go about this? Thanks!

You may have to use =Clean(A1) and copy down and across, then PasteSpecial as values over the top.

Posted by Dave on January 05, 2001 3:54 PM

Hi Josh

CLEAN is an Excel function that removes all non printable characters frm text. In my example "=Clean(A1)" this would remove any single quotations preceding the text in cell A1.

Here is what you should try:

Insert a new Worksheet and then put:=TRIM(Sheet1!A1) Of course you would substitute "Sheet1" with your sheet name.

Copy this down rows and across columns until you have referenced all your cells that have text with the single quote preceding them.

Now highlight your entire sheet (grey square in the corner of "A" and "1") then push Ctrl+C (to copy), now go to Edit>PasteSpecial-Values-OK. This will replace all formulas with their values.

Hope this helps

OzGrid Business Applications