Countif for a string

[Guest]

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

 

[Bob Phillips]

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

 

[Harald Staff]

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

 

[Harald Staff]

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

 

[Guest]

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

Mike

 

[Guest]

I was kind of wondering about that...

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