asked on

way to determine the last numeric value after the last ALPHA character AND after the last "-" <dash>

I have a column in a table that are similar to the following:
SLA-00100225-RSLR-A1-3-R15
SLA-00100225-MAIN-A1-3-R3
SLA-00100225-GOVT-A1-3-I20
SLA-00100225-RSLR-A1-3-R11
SLS-000225-MAIN-R5
SLS-000225-RSLR-R2
SLS-000225-MAIN-I1

is there a way to determine the last numeric value after the last ALPHA character AND after the last "-" <dash> ?
example-- Some of the records
after R2 the numeric is "2"
after R15 the numeric is "15"
etc...

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

Here's a function that should do what you want.

Function GetLastNum(strValue As String) As Long
Dim idx As Long
Dim pos As Long

    pos = InStrRev(strValue, "-")

    If pos <> 0 Then
        For idx = pos + 1 To Len(strValue)
             If IsNumeric(Mid(strValue, idx, 1)) Then
                 LastNum = Val(Mid(strValue, idx))
                 Exit Function
             End If
        Next idx
    End If

End Function

Open in new window

Gustav Brock

A fancy one-liner will do:

Code = "SLA-00100225-GOVT-A1-3-I20"
Number = StrReverse(Mid(CStr(Val("1" & StrReverse(Code))), 2))
Number -> "20"

Open in new window

Fordraiders

ASKER

Thanks all

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
       Experts Exchange Top Expert VBA 2018, 2019
       Experts Exchange Distinguished Expert in Excel 2018

way to determine the last numeric value after the last ALPHA character AND after the last &quot;-&quot; &lt;dash&gt;

way to determine the last numeric value after the last ALPHA character AND after the last "-" <dash>