Regular Expression to extract an string-CodePudding

I'm currently struggeling with a regular expression.

I got this pattern:

^(?:.*?_)?(No\d )?.*$

to extract only the letters No with the following number sequence of these Values:

test352532_No223
testfrrf43tw_No345figrie_ge
test123_no32_fer
teftgef_No23564.345
test_Nhuis34

Now I need the same thing for values like this:

test674.re_test_no3243.ffe
test238.re_test_no1253e.jfe
test_43.e_test_no535264

The best would be to have an pattern that extracts no with all following numbers and if there are some letters after, then output them with it. the pattern that should be extracted always ends with a "_". It should work on any Value.

I have already tried my best:

PPM[1-9][\.\d] [a-z]?

but it doesn't work...

CodePudding user response：

As you mentioned the rules, I assumed:

Substring will always start after underscore;
Substring can start with no and No;
Substring is followed by at least 1 digit;
Substring is followed by 0 lowercase alpha chars.

Try:

_([nN]o\d [a-z]*)

_ - Match an underscore;
([nN]o\d [a-z]*) - A capture group to match 'No' or 'no' with 1 digits and 0 lowercase alphachars.

See an online

CodePudding user response：

I can offer a non-Regex solution. IMHO, Regex make a lot of simple task more complicated and are hard to read if you don't deal with them all the time.

The following function searches for the Prefix ("No" as default, but you can use other strings if you want) and checks the following code for a number. If you want, you can use it as UDF: If A2 contains your string, put the formula =getNumberCode(A2) into B2.

Function getNumberCode(s As String, Optional prefix As String = "No") As String
    Dim p As Long
    p = InStr(1, s, prefix, vbTextCompare)
    If p = 0 Then Exit Function   
    
    Dim number As Long
    number = Val(Mid(s, p   Len(prefix)))
    If number > 0 Then
        getNumberCode = Mid(s, p, Len(prefix)   Len(CStr(number)))
    End If
End Function

Update - haven't read the question close enough, the function above will only return the "No" plus the number, not trailing characters until the underscore. However, it's not that hard to change:

Function getNumberCode(s As String, Optional prefix As String = "No") As String
    Const EndCharacter = "_"
    
    Dim p As Long
    p = InStr(1, s, prefix, vbTextCompare)
    If p = 0 Then Exit Function
    
    Dim number As Long
    number = Int(Val(Mid(s, p   Len(prefix))))
    If number > 0 Then
        getNumberCode = Mid(s, p)
        Dim endPos As Long
        endPos = InStr(getNumberCode, EndCharacter)
        If endPos > 0 Then
            getNumberCode = Left(getNumberCode, endPos - 1)
        End If
    End If

End Function