Excel to text file

Question

If I have antable of four columns:

    A           B             C           D
apple        Macintosh    granny smith   delicious
wines        merlot      zinfedel        cabernet

and I want to put it into a text file (continuous row) as shown below for text mining:

Apple | McIntosh |Granny smith | delicious| Wines | merlot zinfedel | cabernet| How do I do this?

Answer

Bob,

You didn't ask me this question - I just found it in the question pool where it was apparently deposited by whomever you did ask for one reason or another. So you want to produce a text file with with one row per row in Excel, and each cell separated by a vertical line. Some of your separators have a space next to it and some don't. I will assume you don't want a space

Sub ABD() Dim r As Range, cell As Range Dim sName As String, s As String Set r = Range("A1").CurrentRegion.Resize(, 1)

sName = "C:\working\data\datamine.txt" On Error Resume Next Kill sName On Error GoTo 0 Open sName For Output As #1 For Each cell In r

 s = cell.Text & "|" & cell.Offset(0, 1).Text & "|" & _
     cell.Offset(0, 2).Text & "|" & cell.Offset(0, 3).Text & "|"
     Print #1, s

Next Close #1 End Sub

Tested and that worked for me.

It assumes that your data starts in cell A1 and there are no blank rows in your data.