The following code will open the file and test for probable delimiters. If
Split returns an array of more than one element when splitting using a
specific character as the delimiter, that character is a probable delimiter
of the text on a single line of the input file. Change FName to the name of
your file, and change PossibleDelimiters to include all the characters that
might be a delimiter of the data.

Sub AAA()
Dim FNum As Integer
Dim FName As String
Dim Ndx As Long
Dim InputLine As String
Dim Arr As Variant
Dim C As String
Dim PossibleDelimiters As String

PossibleDelimiters = ",;|~" & vbTab

FName = "C:\Test.txt" '<<< CHANGE
FNum = FreeFile
Open FName For Input Access Read As #FNum
Line Input #FNum, InputLine
Close #FNum
For Ndx = 1 To Len(PossibleDelimiters)
C = Mid(PossibleDelimiters, Ndx, 1)
Arr = Split(InputLine, C)
If IsArray(Arr) = True Then
If UBound(Arr) - LBound(Arr) + 1 1 Then
Debug.Print "Likely Delimiter: ", C, Asc(C)
End If
End If
Next Ndx
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Kieran H" wrote in message
ps.com...
To all,

I would like to be able to programatically determine the delimiter
within a txt or csv file so that the user is not faced with the Excel
import text wizard..

The files come from several sources and the delimiter is likely to be
tab, comma or semicolon and less likely a pipe or tilda.

I'm thinking I need to count the number of each potential delimiter in
a given number of rows probably excluding those that occur within
quoted text.

If you have any thoughts or know of any examples they'd be a great
help

Cheers

Kieran