Filtering a number from a String

[basura]

Hello friends,

My problem is that I have a string with text and a single number in it.
The size of this number can variate in number of digits but is always located on the right side of the string.
I want to retrieve this number out of this string.

Are there any nifty functions for this ? I've been looking at the Format() functions but as far as I could see this isn't gonna help me out.

Thanks a lot for any responds

 

[namliam]

Look at the "right" function and probably the "instrrev" function.

Together they can probably do what you need to do.

Some (made up) samples would go a long way into helping in more detail

 

[doco]

Is the 'string prefix' fixed length?

EG. theTargetList

AG1234
BA378945
DC99113

Then the number portion may be retrieved by
Dim NumberOnly As Variant

NumberOnly = Right(theTargetList, Len(theTargetList) - 2)

 

[raskew]

Hi -

Function SaveNumer2() will remove alpha characters from a string.
To use, copy/paste to a standard module.

Call as shown:

Function SaveNumer2(ByVal pStr As String) As Long
'*******************************************
'Purpose:   Removes alpha characters from
'           a string
'Coded by:  raskew
'Calls:     Function IsNumeric()
'Inputs:    ? SaveNumer2("ABC234BB")
'Output:    234
'*******************************************

Dim strHold As String
Dim intLen  As Integer
Dim n       As Integer

    strHold = Trim(pStr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
        n = n - 1
       End If
       n = n + 1
    Loop Until val(strHold) > 0
    SaveNumer2 = val(strHold)

End Function

HTH - Bob

 

[doco]

Now that is a definite copy, paste to notepad, save as *.bas file and store away.

Thanks

 

[basura]

Thank you all very much for the help, I got it running with NamliaM hints (I can program but I am new to VBA so don't know where to search)
Little by little the doors of VBA are opening up for me ;-)