Extract text which begin with the same two characters and end in a numeric character

Question

I would like to extract a code from a larger extract of text, the constants I have is the Code will either start with WP or MD and end in a Numeric value and example of the patterns the code can be in are below;

WP0053

WP053

WP_053

WP_0053

WP 053

WP 0053

MDC_308

WP6

WP6.1

MDC_0308

Please see image of expected output below;

Any help would be much appreciated

Answer 1

Public Function GetCode(data As String) As String

startpos = InStr(data, "WP")
If startpos = 0 Then startpos = InStr(data, "MD")

fisrtNumPos = 0
For i = startpos To Len(data)

    If fisrtNumPos = 0 And LastNumPos = 0 Then
        If IsNumeric(Mid(data, i, 1)) Then
            fisrtNumPos = i
        End If
    Else
        If Not IsNumeric(Mid(data, i, 1)) Then
            LastNumPos = i
            Exit For
        End If
    End If
Next i

Endpos = LastNumPos - startpos

GetCode = Mid(data, startpos, Endpos)

End Function

Add this Code in any Module and try.