I am importing a text file into excel line by line. I only want to import
lines where the first non-space character is a number or negative sign AND
only those that have two commas in the string. I have the first part
working, I just need to know how to do the AND part (strings with only two
commas).

This would be easy if I were looking at a string in a cell by using countif,
but this is a string from a text file. Is there any way of doing this
without importing the entire file and then using countif on the imported
cells?

This is what I have so far:

Sub Mikesub()
....
Open Filename For Input As #FileNum
Counter = 1
keywords = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "-")
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
For i = LBound(keywords) To UBound(keywords)
fstword = LTrim(ResultStr)
If Left(fstword, 1) = keywords(i) Then
bfound = True
Exit For
End If
Next
If bfound Then
ActiveCell.Value = Trim(ResultStr)
ActiveCell.Offset(1, 0).Select
bfound = False
End If
Counter = Counter + 1
Loop
....
End Sub


For example with the following data I would only like the second and fourth
lines imported.

-1234, 4321, 5.1232, 92.1
90310, 110.0, -1.0
90311, 105.1 -1.0
-90312, 125.4, 12
B2931, 94.1, 0.32


Thanks in advance for your help.

Mike

If Left(fstword, 1) = keywords(i) Then
If Len(fstword) - Len(Replace(fstword,"-","")) =2 Then
bfound = True
Exit For
End If
End If


--
HTH

Bob Phillips

"crazybass2" wrote in message
...
I am importing a text file into excel line by line. I only want to import
lines where the first non-space character is a number or negative sign AND
only those that have two commas in the string. I have the first part
working, I just need to know how to do the AND part (strings with only two
commas).

This would be easy if I were looking at a string in a cell by using
countif,
but this is a string from a text file. Is there any way of doing this
without importing the entire file and then using countif on the imported
cells?

This is what I have so far:

Sub Mikesub()
...
Open Filename For Input As #FileNum
Counter = 1
keywords = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "-")
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
For i = LBound(keywords) To UBound(keywords)
fstword = LTrim(ResultStr)
If Left(fstword, 1) = keywords(i) Then
bfound = True
Exit For
End If
Next
If bfound Then
ActiveCell.Value = Trim(ResultStr)
ActiveCell.Offset(1, 0).Select
bfound = False
End If
Counter = Counter + 1
Loop
...
End Sub


For example with the following data I would only like the second and
fourth
lines imported.

-1234, 4321, 5.1232, 92.1
90310, 110.0, -1.0
90311, 105.1 -1.0
-90312, 125.4, 12
B2931, 94.1, 0.32


Thanks in advance for your help.

Mike

Hi Mike

Sub test()
MsgBox ShouldImport("-1234, 4321, 5.1232, 92.1")
MsgBox ShouldImport("90310, 110.0, -1.0")
MsgBox ShouldImport("90311, 105.1 -1.0")
MsgBox ShouldImport("-90312, 125.4, 12")
MsgBox ShouldImport("B2931, 94.1, 0.32")
End Sub

Function ShouldImport(ByRef S As String) As Boolean
Dim L1 As Long, L2 As Long, L3 As Long
If Val(S) < 0 Then
If Len(S) - Len(Replace$(S, ",", "")) = 2 Then ShouldImport = True
End If
End Function

HTH. Best wishes Harald

"crazybass2" skrev i melding
...
I am importing a text file into excel line by line. I only want to import
lines where the first non-space character is a number or negative sign AND
only those that have two commas in the string. I have the first part
working, I just need to know how to do the AND part (strings with only two
commas).

This would be easy if I were looking at a string in a cell by using
countif,
but this is a string from a text file. Is there any way of doing this
without importing the entire file and then using countif on the imported
cells?

This is what I have so far:

Sub Mikesub()
...
Open Filename For Input As #FileNum
Counter = 1
keywords = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "-")
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
For i = LBound(keywords) To UBound(keywords)
fstword = LTrim(ResultStr)
If Left(fstword, 1) = keywords(i) Then
bfound = True
Exit For
End If
Next
If bfound Then
ActiveCell.Value = Trim(ResultStr)
ActiveCell.Offset(1, 0).Select
bfound = False
End If
Counter = Counter + 1
Loop
...
End Sub


For example with the following data I would only like the second and
fourth
lines imported.

-1234, 4321, 5.1232, 92.1
90310, 110.0, -1.0
90311, 105.1 -1.0
-90312, 125.4, 12
B2931, 94.1, 0.32


Thanks in advance for your help.

Mike

Doh! Delete all the Dim L stuff, it belongs to something totelly different.
Sorry.

"Harald Staff" skrev i melding
...
Hi Mike

Sub test()
MsgBox ShouldImport("-1234, 4321, 5.1232, 92.1")
MsgBox ShouldImport("90310, 110.0, -1.0")
MsgBox ShouldImport("90311, 105.1 -1.0")
MsgBox ShouldImport("-90312, 125.4, 12")
MsgBox ShouldImport("B2931, 94.1, 0.32")
End Sub

Function ShouldImport(ByRef S As String) As Boolean
Dim L1 As Long, L2 As Long, L3 As Long
If Val(S) < 0 Then
If Len(S) - Len(Replace$(S, ",", "")) = 2 Then ShouldImport = True
End If
End Function

HTH. Best wishes Harald

"crazybass2" skrev i melding
...
I am importing a text file into excel line by line. I only want to
import
lines where the first non-space character is a number or negative sign
AND
only those that have two commas in the string. I have the first part
working, I just need to know how to do the AND part (strings with only
two
commas).

This would be easy if I were looking at a string in a cell by using
countif,
but this is a string from a text file. Is there any way of doing this
without importing the entire file and then using countif on the imported
cells?

This is what I have so far:

Sub Mikesub()
...
Open Filename For Input As #FileNum
Counter = 1
keywords = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "-")
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
For i = LBound(keywords) To UBound(keywords)
fstword = LTrim(ResultStr)
If Left(fstword, 1) = keywords(i) Then
bfound = True
Exit For
End If
Next
If bfound Then
ActiveCell.Value = Trim(ResultStr)
ActiveCell.Offset(1, 0).Select
bfound = False
End If
Counter = Counter + 1
Loop
...
End Sub


For example with the following data I would only like the second and
fourth
lines imported.

-1234, 4321, 5.1232, 92.1
90310, 110.0, -1.0
90311, 105.1 -1.0
-90312, 125.4, 12
B2931, 94.1, 0.32


Thanks in advance for your help.

Mike

I was kind of wondering about that...


"Harald Staff" wrote:

Doh! Delete all the Dim L stuff, it belongs to something totelly different.
Sorry.

"Harald Staff" skrev i melding
...
Hi Mike

Sub test()
MsgBox ShouldImport("-1234, 4321, 5.1232, 92.1")
MsgBox ShouldImport("90310, 110.0, -1.0")
MsgBox ShouldImport("90311, 105.1 -1.0")
MsgBox ShouldImport("-90312, 125.4, 12")
MsgBox ShouldImport("B2931, 94.1, 0.32")
End Sub

Function ShouldImport(ByRef S As String) As Boolean
Dim L1 As Long, L2 As Long, L3 As Long
If Val(S) < 0 Then
If Len(S) - Len(Replace$(S, ",", "")) = 2 Then ShouldImport = True
End If
End Function

HTH. Best wishes Harald

"crazybass2" skrev i melding
...
I am importing a text file into excel line by line. I only want to
import
lines where the first non-space character is a number or negative sign
AND
only those that have two commas in the string. I have the first part
working, I just need to know how to do the AND part (strings with only
two
commas).

This would be easy if I were looking at a string in a cell by using
countif,
but this is a string from a text file. Is there any way of doing this
without importing the entire file and then using countif on the imported
cells?

This is what I have so far:

Sub Mikesub()
...
Open Filename For Input As #FileNum
Counter = 1
keywords = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "-")
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
For i = LBound(keywords) To UBound(keywords)
fstword = LTrim(ResultStr)
If Left(fstword, 1) = keywords(i) Then
bfound = True
Exit For
End If
Next
If bfound Then
ActiveCell.Value = Trim(ResultStr)
ActiveCell.Offset(1, 0).Select
bfound = False
End If
Counter = Counter + 1
Loop
...
End Sub


For example with the following data I would only like the second and
fourth
lines imported.

-1234, 4321, 5.1232, 92.1
90310, 110.0, -1.0
90311, 105.1 -1.0
-90312, 125.4, 12
B2931, 94.1, 0.32


Thanks in advance for your help.

Mike

Thanks for both Bob and Harald for the quick response. You both basically
displayed the same solution. This will work great!

Mike

"Harald Staff" wrote:

Hi Mike

Sub test()
MsgBox ShouldImport("-1234, 4321, 5.1232, 92.1")
MsgBox ShouldImport("90310, 110.0, -1.0")
MsgBox ShouldImport("90311, 105.1 -1.0")
MsgBox ShouldImport("-90312, 125.4, 12")
MsgBox ShouldImport("B2931, 94.1, 0.32")
End Sub

Function ShouldImport(ByRef S As String) As Boolean
Dim L1 As Long, L2 As Long, L3 As Long
If Val(S) < 0 Then
If Len(S) - Len(Replace$(S, ",", "")) = 2 Then ShouldImport = True
End If
End Function

HTH. Best wishes Harald

"crazybass2" skrev i melding
...
I am importing a text file into excel line by line. I only want to import
lines where the first non-space character is a number or negative sign AND
only those that have two commas in the string. I have the first part
working, I just need to know how to do the AND part (strings with only two
commas).

This would be easy if I were looking at a string in a cell by using
countif,
but this is a string from a text file. Is there any way of doing this
without importing the entire file and then using countif on the imported
cells?

This is what I have so far:

Sub Mikesub()
...
Open Filename For Input As #FileNum
Counter = 1
keywords = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "-")
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
For i = LBound(keywords) To UBound(keywords)
fstword = LTrim(ResultStr)
If Left(fstword, 1) = keywords(i) Then
bfound = True
Exit For
End If
Next
If bfound Then
ActiveCell.Value = Trim(ResultStr)
ActiveCell.Offset(1, 0).Select
bfound = False
End If
Counter = Counter + 1
Loop
...
End Sub


For example with the following data I would only like the second and
fourth
lines imported.

-1234, 4321, 5.1232, 92.1
90310, 110.0, -1.0
90311, 105.1 -1.0
-90312, 125.4, 12
B2931, 94.1, 0.32


Thanks in advance for your help.

Mike