Multiple Deliminators

[Fraggs]

How do folks,

I have a text file with several different deliminators [ >, /, TAB
SPACE & more ]. What I need to do is create column seperators betwee
these deliminators.

I know I could use "Text to columns" from the DATA menu, but I have
lot of files to work through so some VBA would be more helpful.

Anyone got any suggestions

 

[STEVE BELL]

Have you tried to do a file manually and use the macro recorder to
record your steps. Than edit the code to be more generic.

 

[Edward Ulle]

Unfortunately the Split function will only take one delimiter but the
function I have shown below will work with any number of user
delimeters.

Option Explicit

Sub Test()

Dim sText As String
Dim vArray As Variant
Dim i As Integer

sText = "a=b+c-d"
vArray = MySplit(sText)

sText = ""
For i = 1 To UBound(vArray)
sText = sText + vArray(i - 1) + vbCrLf
Next
MsgBox sText


End Sub

Function MySplit(sBuffer As String) As Variant

Dim i As Integer
Dim j As Integer
Dim vArray As Variant

j = Len(sBuffer)
i = 1
While i < j
Select Case Mid(sBuffer, i, 1)
Case Is = "="
Mid(sBuffer, i, 1) = ","
Case Is = "+"
Mid(sBuffer, i, 1) = ","
Case Is = "-"
Mid(sBuffer, i, 1) = ","
End Select
i = i + 1
Wend

vArray = Split(sBuffer, ",")

MySplit = vArray

End Function

 

[Dave Peterson]

I think I'd just replace those characters with the delimiter I wanted:

Option Explicit
Sub Test2()

Dim sText As String
Dim vArray As Variant
Dim iCtr As Long
Dim myDelims As Variant

myDelims = Array("=", "+", "-", ";", "|") 'add as many as you need

sText = "a=b+c-d"

For iCtr = LBound(myDelims) To UBound(myDelims)
sText = Replace(sText, myDelims(iCtr), ",")
Next iCtr

vArray = Split(sText, ",")

End Sub

But I was confused by the OP's message.

 

[Guest]

Oi Fraggs,

What you can do is set up a workbook where you have the list of all your
files in a worksheet column, name and path such as "D:\myfile1" in A1,
"D:\myfile2" in A2...

Then, your macro would loop through file references until the end of the
list. Inside the loop you will open (for input access read) each source file,
and also a destination file, with the same name (for append access write),
but with the extension XLS.

Then send each line of the source file (do until eof) to a function that
will search the file for any ocurrence of the characters used as delimiters
(using ASCII code ranges like 32 to 47, or arrays with the delimiters used),
and substitute them by TABS.

The function will return the line delimited with tabs, and you will print it
to the destination file.

Then, when you open the destination file, VOILA, the fields will be already
split.

Best,

Rafael