Banish quotation marks from text files exported from Excel

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.

If you enjoyed this post, please share it!

    One comment on “Banish quotation marks from text files exported from Excel

    1. Matt White says:

      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!

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>