Split Text file data

[sujittalukde]

I have a text file and want to split each and every data after the delimiter ^ for each line and take the values in an array for each line.
For eg, Line 1 is like this:
1^FH^SL1^R^16032011^1^D^MUMN77777A^1^NSDLRPU2.4^^^^^^^
The array should store value like this:
1,FH,SL1,R,16032011,1,D,MUMN77777A,1,NSDLRPU2.4
and dump this data in each colum of Row 1 of Sheet 1
Then Line2
Do the same thing. In this case, each column of Row 2 and so on till the last line of the file.

regards

[mdmackillop]

Record a macro using Data/Import Text File. Select your delimiter and you should end up with something like this
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\aa\mytext.txt", _
Destination:=Range("A1"))
.Name = "mytext"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "^"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub