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