Intercept only number in string

[sal21]

Trovati : 9 - Records

i have this string in a var MyString

I need to extract only the number from ":" and "-" in this case is 9

But the length of number is dinamic, i can have:

Trovati : 249 - Records
Trovati : 149 - Records
Trovati : 145879 - Records

How to?
Tks.

[HansV]

Try this:

Code: Select all

Dim p1 As Long
Dim p2 As Long
Dim lngNumber As Long
p1 = InStr(MyString, ":")
p2 = InStr(p1 + 1, MyString, "-")
lngNumber = Val(Trim(Mid(MyString, p1 + 1, p2 - p1 - 1)))

[Jan Karel Pieterse]

Like this:

Code: Select all

Sub test()
    Dim sTest As String
    sTest = "Trovati : 249 - Records"
    MsgBox Trim(Split(Split(sTest, ":")(1), "-")(0))
End Sub

Explanation:
The inner trim part:
Split(sTest, ":")(1)
gives an array of two strings:
"Trovati :"
and
" 149 - Records"

The (1) then takes the second string
" 149 - Records"
and the outer split function splits it into:
" 149 "
and
" Records"
The (0) takes the first string " 149 " and the trim function removes the leading and trailing spaces.

[sal21]

Like this:

Code: Select all

Sub test()
    Dim sTest As String
    sTest = "Trovati : 249 - Records"
    MsgBox Trim(Split(Split(sTest, ":")(1), "-")(0))
End Sub

Explanation:
The inner trim part:
Split(sTest, ":")(1)
gives an array of two strings:
"Trovati :"
and
" 149 - Records"

The (1) then takes the second string
" 149 - Records"
and the outer split function splits it into:
" 149 "
and
" Records"
The (0) takes the first string " 149 " and the trim function removes the leading and trailing spaces.

Tks! the code work well... and tks for Explanation.

[sal21]

Try this:

Code: Select all

Dim p1 As Long
Dim p2 As Long
Dim lngNumber As Long
p1 = InStr(MyString, ":")
p2 = InStr(p1 + 1, MyString, "-")
lngNumber = Val(Trim(Mid(MyString, p1 + 1, p2 - p1 - 1)))

Wow! nice code. tks