When exporting Excel worksheets to CSV (comma-separated values) or tab-delimited text, it will add quotation marks (“..”) around any fields containing commas or quotes.
While such files can be re-imported into Excel without problems, it can cause other applications to choke. It is possible to open the files in a text editor to remove the problematic characters, or create a Word macro to do the same job automatically, but the simplest solution is to bypass Excel’s own file export filters and use a macro such as the following to perform the export:
Public Sub TextNoModification() Const DELIMITER As String = "," 'or "|", vbTab, etc. Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "Test.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, Columns.Count).End(xlToLeft)) sOut = sOut & DELIMITER & myField.Text Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum End Sub
The delimiter and output filename can be changed by editing the code. The macro above is taken from this very useful page from McGimpsey & Associates which contains a whole host of Excel tips.
One comment on “Banish quotation marks from text files exported from Excel”
Or, just use OpenOffice. Calc is much better for managing a list and exporting it. You can actually see the quotes in the cells (WYSWYG), and when you save as a .csv you can specifiy the delimiter and whether to use quotes to enclose text. What a relief!