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;

enter image description here

Any help would be much appreciated

If it always starts with the project id followed by an underscore, you can replace this with an empty string. The code you want will be at the beginning of the remaining string. After that perform a split with space as delimiter and take the first token. — Roland, Jun 17 '15 at 09:44
You should post whatever you have tried. This is not a code writing service but is to help fix broken code. — ChipsLetten, Jun 17 '15 at 10:19

Prabu krishna · Answer 1 · 2015-06-17T11:17:35.343

0

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"

edited Jun 17 '15 at 11:17

answered Jun 17 '15 at 10:25

Prabu krishna

51
4

#prabu k, try your code with 6th row (image sample) the result is:MDC-WP-01003_SRI – Fabrizio Jun 17 '15 at 10:41
Thanks for this - It works really well and I can see the result in the immediate window. However and apologies as this is probably a really "Simple" question how would I convert this code into a (UDF) public function as the usual method I use does not seem to work and is returning "Value". – Sean Bailey Jun 17 '15 at 12:23
I have posted the function as another answer, take a look and let me know. – Prabu krishna Jun 17 '15 at 12:43

score 0 · Answer 2 · answered Jun 17 '15 at 12:40

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

score 0 · Accepted Answer · answered Jun 17 '15 at 12:41

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.

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

3 Answers3