Exctrract only number in string 1

[sal0003]

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.

 

[RBSTR]

Hi

You could use mid() to extract the section containing the number.

You then need to concern yourself with the start and end position of the number in your string.

If it always start in position 11 then you can state 11. Otherwise you can use Instr() to find the position of ":"

Similarly you can use Instr() to find the position of "-" and then subtract 1 away from this position to get the end of your numeric string (unless your number maybe a negative in which case you may want to look for the second instance)

Alternatively you could loop through the LEN() (length)of the string - using mid() to check each character at a time checking to see whether it is numeric with IsNumeric(). The first time IsNumeric() returns true would be your first number. When IsNumeric() next returns false that would be the position of the end of your numeric string. Again you may need to look to handle "-" if your number may be negative.

Hope this helps.

 

[strongm]

We can leverage a little-used feature of the Val function to do this (specifically that it "stops reading the string at the first character it can't recognize as part of a number")

If it is always "Trovati :" at the beginning we can get the result with:

MsgBox Val(Right(strInfo, Len(MyString) - 9))

 

[MajP]

If it is not always "Trovati" but it is always "SomeName:" you could modify strongm's to

val(mid(strIngo,instr(strIngo,":")+1))

 

[SkipVought]

hi,

Dim s As String
s = "Trovati : 249 - Records"
MsgBox Trim(Split(Split(s, ":")(1), "-")(0))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

String operations are relatively slow in VBA, so keeping them to a minimum is generally beneficial.

>If it is not always "Trovati"

Ah - you spoiled my "Step 2" plan ...