tuli brought next idea :
I have a file with the following format (n rows of):
Freq, Real+jImag which sometimes looks like
Freq, Real-jImag
I can tell excel to consider the coma as delimiter and only ONE option
for an arbitrary delimiter, say "+j". I would need another option for
an arbitrary delimiter for "-j".
Am I missing something, or this is a real limitation?
Thanks
Tuli
Actually, the first delimiter is a comma and a space; the second
delimiter may be '+j' or '-j'.
I recommend using standard VBA file I/O to read the file into an array
and parse the array as if the lines contained value pairs. This means
you could check each line for the '+j' or the '-j' delimiter and take
appropriate action to split the pair. For example:
Sub ParseFileText()
Dim i As Long
Dim vTextIn As Variant
Const sFilename As String = "C:\MyFile" '//use actual file & path
vTextIn = Split(GetTextFromFile(sFilename), vbCrLf)
'Replace the 2nd delimiter with ", "
For i = LBound(vTextIn) To UBound(vTextIn)
If InStr(vTextIn(i), "+j") > 0 Then
vTextIn(i) = Replace(vTextIn(i), "+j", ", ")
ElseIf InStr(vTextIn(i), "-j") > 0 Then
vTextIn(i) = Replace(vTextIn(i), "-j", ", ")
End If
Next 'i
'Parse the lines into separate cells
For i = LBound(vTextIn) To UBound(vTextIn)
Cells(i + 1, 1).Resize(1, 3) = Split(vTextIn(i), ", ")
Next 'i
End Sub
Helper function...
Function GetTextFromFile(sFileName As String) As String
' Opens and reads the contents of a text file
Dim iNum As Integer, bOpen As Boolean
On Error GoTo ErrHandler
iNum = FreeFile() 'Get the next file number
'Read the entire file
Open sFileName For Binary Access Read As #iNum
bOpen = True '//if we got here then file opened successfully
GetTextFromFile = Space$(LOF(iNum)): Get iNum, , GetTextFromFile
ErrHandler:
If bOpen Then Close #iNum
End Function '//GetTextFromFile()
HTH
