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

--
Fragg

-----------------------------------------------------------------------
Fraggs's Profile: http://www.excelforum.com/member.php...nfo&userid=807
View this thread: http://www.excelforum.com/showthread.php?threadid=39291

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.

--
steveB

Remove "AYN" from email to respond
"Fraggs" wrote in
message ...

How do folks,

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

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

Anyone got any suggestions ?


--
Fraggs


------------------------------------------------------------------------
Fraggs's Profile:
http://www.excelforum.com/member.php...fo&userid=8077
View this thread: http://www.excelforum.com/showthread...hreadid=392919

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



*** Sent via Developersdex http://www.developersdex.com ***

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.


Edward Ulle wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

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

"Fraggs" wrote:


How do folks,

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

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

Anyone got any suggestions ?


--
Fraggs


------------------------------------------------------------------------
Fraggs's Profile: http://www.excelforum.com/member.php...fo&userid=8077
View this thread: http://www.excelforum.com/showthread...hreadid=392919