Solved: Extracting numbers from a string or ignoring an error?

[troelsi]

Hello my dear experts :)

I have the following string and would like to extract the numbers from it, so I can ask if a given number is greater than xx.

I have tryed the following but i can't get around the error.

Can somebody please tell me how i can ignore the error?

I'm under the impression that it's easier to ignore the error than to convert the string in to numbers, but correct me if I'm wrong.

Thank you


Option Base 1
Type xt
recept As String
End Type
Sub string1()
Dim x(3) As xt
Dim i As Integer
x(1).recept = "800800"
x(2).recept = "rec.010"
x(3).recept = "rec.110kr"

For i = LBound(x) To UBound(x)
If x(i).recept >= 800800 Then
If Err Then
Err.Clear
Else
MsgBox x(i).recept
End If
End If
Next i
End Sub

[tstav]

hi troelsi,
you have to type in <on error resume next> to avoid the error message and handle the error
Option Base 1
Type xt
recept As String
End Type
Sub string1()
Dim x(3) As xt
Dim i As Integer
x(1).recept = "800800"
x(2).recept = "rec.010"
x(3).recept = "rec.110kr"
on error resume next
For i = LBound(x) To UBound(x)
If x(i).recept >= 800800 Then
If Err Then
Err.Clear
Else
MsgBox x(i).recept
End If
End If
Next i
End Sub

[tstav]

Ooops! The indentation got a bit messed up. Sorry for that...

[mikerickson]

Another approach is to convert the string to a numerical value so the comparison doesn't error


If Val(x(i).recept) >= 800800 Then

Val("800800")=800800 ; Val("rec.010")=0 ; Val("rec.110kr")=0

Although it doesn't apply in your situation, the Val function is a quick way to seperate the numerical part from a text suffix, since Val("123AB")=123

Or did we misinterpret and you were looking to extract 10 and 110 from the last two strings?

[troelsi]

Thanks for you answers.

Yes I was looking for the numbers 10 and 110, but in this particular context it doesn't matter whether they are 0 or 10 since I'm only interested in numbers greater than 800800.

Thanks!

[Bob Phillips]

Sub string1()
Dim x(3) As xt
Dim i As Long, j As Long, k As Long
Dim midVal As Long
Dim lenX As Long

x(1).recept = "800800"
x(2).recept = "rec.010"
x(3).recept = "rec.110kr"

For i = LBound(x) To UBound(x)

lenX = Len(x(i).recept)
j = 1
Do Until IsNumeric(Mid(x(i).recept, j, 1)) Or j > lenX
j = j + 1
Loop
k = j
Do Until Not IsNumeric(Mid(x(i).recept, k, 1)) Or k > lenX
k = k + 1
Loop
midVal = Val(Mid(x(i).recept, j, k - j))

If midVal >= 800800 Then
If Err Then
Err.Clear
Else
MsgBox x(i).recept
End If
End If
Next i
End Sub

[mikerickson]

Here are a couple of string to number conversion routines

IntegerSubString("abc123.45") = 123
IntegerSubString("rec.010") = 10

NumericSubString("abc123.45") = 123.45
NumericSubString("rec.010") = .1

Option Base 1

Type xt
recept As String
End Type

Sub string1()
Dim x(3) As xt
Dim i As Integer
x(1).recept = "800800"
x(2).recept = "rec.010"
x(3).recept = "rec.110kr"
On Error Resume Next
For i = LBound(x) To UBound(x)
If IntegerSubString(x(i).recept) >= 800800 Then
MsgBox x(i).recept
End If
Next i
End Sub

Function IntegerSubString(inputString As String) As Long
Dim i As Long
For i = 1 To Len(inputString)
IntegerSubString = Int(Val(Mid(inputString, i)))
If IntegerSubString <> 0 Then Exit Function
Next i
End Function

Function NumericSubString(inputString As String) As Double
Dim i As Long
For i = 1 To Len(inputString)
NumericSubString = Val(Mid(inputString, i))
If NumericSubString <> 0 Then Exit Function
Next i
End Function

[troelsi]

Great thanks a lot!