retrieve numeric part of string

[cpampas]

Hello,

I would like to retrieve the numeric part of the following string into 2 integer values:

dim y as integer, h as integer, mystr as string
mystr="Year 2001 Mileage 2500 m/h Power "

2001 would go to the variable y, and 2500 to the variable h
i gather that if i use replace to gtrim the string og the non numeric values could work, but what if i encounter a mystr with diferent words in it?

 

[Gasman]

You could use Split(), but unless you know the position of the numerics, that will not help you much?

 

[The_Doc_Man]

Is this a one-time thing? Or will you be doing this frequently? I have a solution but it takes a little bit of work to implement it and you would have to still implement some VBA code of your own. If you aren't comfortable with at least SOME use of VBA then my method isn't right for you.

 

[arnelgp]

the function will return Collection of numbers from the string:

Public Function getNumbers(ByVal value As String) As Collection

    Dim coll As New Collection
    Dim m, z
    
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "[+-]?(\d*\.)?\d+"
        
        Set m = .Execute(value)
        For Each z In m
            coll.Add z
            'Debug.Print M.value
        Next
    
    End With
    Set getNumbers = coll

End Function

to test:

private sub test
    dim c as collection
    dim i as integer
    dim mystr as string
    mystr="Year 2001 Mileage 2500 m/h Power "
    set c = getNumbers(mystr)
    for i = 1 to c.count
        debug.print c(i)
    next
end sub

 

[gemma-the-husky]

@arnelgp That's very slick!

So, to us humble mortals, you could use split and just iterate the split array.

Sub findnumbers()
Const mystr = "A B C 2000 X Y 1200 Z B 2250"

Dim a() As String
Dim x As Long
Dim values(2) As Long
Dim item As Long

a = Split(mystr, " ")

values(1) = 0
values(2) = 0
item = 1

For x = 0 To UBound(a)
   If IsNumeric(a(x)) Then
      values(item) = CLng(a(x))
      item = item + 1
      If item = 3 Then GoTo done
   End If
Next

done:
    MsgBox values(1) & " " & values(2)
    'returns 2000,1200
End Sub

 

[Gasman]

@arnelgp That's very slick!

So, to us humble mortals, you could use split and just iterate the split array.

Sub findnumbers()
Const mystr = "A B C 2000 X Y 1200 Z B 2250"

Dim a() As String
Dim x As Long
Dim values(2) As Long
Dim item As Long

a = Split(mystr, " ")

values(1) = 0
values(2) = 0
item = 1

For x = 0 To UBound(a)
   If IsNumeric(a(x)) Then
      values(item) = CLng(a(x))
      item = item + 1
      If item = 3 Then GoTo done
   End If
Next

done:
    MsgBox values(1) & " " & values(2)
    'returns 2000,1200
End Sub

Yes, @arnelgp elgp is the RegExp maestro.

 

[Abo Abd_Allah]

Put this in new module

Function ExNumFromTxt(ByVal mystr As String)
Dim y As Integer, h As Integer
'mystr = "Year 2001 Mileage 2500 m/h Power "
'================================
On Error Resume Next
Dim ary() As String
Dim I As Integer

ary = Split(mystr, " ")

For I = 0 To UBound(ary)
    If IsNumeric(ary(I)) Then
        If y = 0 Then
        y = ary(I)
        Else
        h = ary(I)
        End If
    End If
Next I
Debug.Print y & " " & h
End Function

And call it Like this

ExNumFromTxt("Year 2001 Mileage 2500 m/h Power ")

Good Luck

 

[cpampas]

arnelgp, thanks for your example that works like a charm
also great examples with split and isnumeric(), , that i will certainly try

 

[arnelgp]

additional info, whether there is a "space" on each world or none,
it will still retrieve those numbers, eg:

private sub test
    dim c as collection
    dim i as integer
    dim mystr as string
    mystr="Year2001Mileage2500m/hPower "
    set c = getNumbers(mystr)
    for i = 1 to c.count
        debug.print c(i)
    next
end sub

result:
2001
2500