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.

Answer 2

If your code doesn't have space then the below code will help.

Sub Test()

Data = "A850085_MDC-WP-01003_SRI Phase 2 - Programme Manager - Dionysios Psachoulias"

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

fisrtNumPos = 0
LastNumPos = 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

Debug.Print Mid(Data, startpos, Endpos)

End Sub

This should work now. But if the text contains "MD" followed by "WP" then it will take the code from WP only.

for example: data= "A850085_WPC-MD-01003_SRI Phase 2 - Programme Manager - Dionysios Psachoulias"

then the result will be result= "WPC-MD-01003"

Answer 3

Try something along the lines of this:-

Dim cell
Dim tmp as string

For each cell in activesheet.columns(1).usedrange.cells
    If InStr(1, cell.Value, "_MDC_", vbTextCompare) > 0 Then
        tmp = Right(cell.Value, Len(cell.Value) - InStr(1, cell.Value, "_MDC_", vbTextCompare))
        tmp = Left(tmp, InStr(1, tmp, " ", vbTextCompare) - 1)
        cell.offset(0,2).value = tmp
    End If
next cell