Find Last Number Within String

[halcyon1]

I'm currently working on some string manipulation functions. I need to find the last number within a string, and return the position of it.

I can use InstrRev to find the last position of a single number but not sure how to do it to show the last position of any number (or if there is a better way).

Say my example data is as follows

1. Some text Would go Here 123
2. 321 Some text would go here
3. Some 456 Text would go here

I would like the function to return

1. 27
2. 3
3. 8

Any help would be greatfully appreciated!

 

[GeofW]

Hi and welcome to MrExcel,
Try

Sub GetLastNum()
    Dim lngCounter As Long
    Dim lngPosn As Long
    Dim strVal As String
    Dim strValRev As String
    
    strVal = "abc 123 defg"
    strValRev = StrReverse(strVal)
    For lngCounter = 1 To Len(strValRev)
        If IsNumeric(Mid(strValRev, lngCounter, 1)) = True Then
            Exit For
        End If
    Next lngCounter
    
    lngCounter = Len(strVal) - (lngCounter - 1)
      
End Sub

 

[MickG]

Hi, Try this:-

Function Lnum(Rng [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,Lst as Integer[/COLOR]
    [COLOR=navy]For[/COLOR] n = 0 To 9
        Lst = InStrRev(Rng, n)
        Lnum = IIf(InStrRev(Rng, n) > Lnum, Lst, Lnum)
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] Function

Regards Mick

 

[halcyon1]

Thank you both for your quick replies, both examples worked exactly as I wanted, much appreciated!

 

[bosco_yip]

1] You can try this array formula :

{=MATCH(1,-MID(A1,ROW($1:$99),1))}

2] Or, this non-array formula :

=LOOKUP(1,-MID(A1,ROW($1:$99),1),ROW($1:$99))

Regards
Bosco