Extract all 5 character alphanumerics in a string that where the 1st char is a letter, and the 2nd char is a number

[andrewmshoe]

I found a few related questions, but waasn't sure how to modify the script for my purposes.
E.g: Extract multiple 5-digit numbers from alphanumeric text

String	Desired Output
Hello there P0D1C, im great,% U1212	P0D1C U1212
My name is P6717 and I happy	P6717

They will always start with a P or U. Also, there may be long strings where 10 or more of these alphanumerics need to get extracted.

 

[Fuji]

vba, data in col.A

Sub test()
    Dim r As Range, m, s$
    With CreateObject("VbScript.RegExp")
        .Global = True
        .Pattern = "\b[PU]\d[A-Za-z\d]{3}\b"
        For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp))
            For Each m In .Execute(r)
                s = Trim(s & " " & m)
            Next
            r(, 2) = s: s = ""
        Next
    End With
End Sub

 

[andrewmshoe]

Thank you, works great! I realized some of the 5 char start with lowercase so I made a small adjustment:

Sub test()
    Dim r As Range, m, s$
    With CreateObject("VbScript.RegExp")
        .Global = True
        .Pattern = "\b[PUpu]\d[A-Za-z\d]{3}\b"
        For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp))
            For Each m In .Execute(r)
                s = Trim(s & " " & m)
            Next
            r(, 2) = s: s = ""
        Next
    End With
End Sub

 

[andrewmshoe]

Looks like it didn't like that I tried to bold the "pu" I added... Anyway, thank you so much!

 

[Fuji]

Post few string that the code does not extract.

I believe your string is something different from what you posted in your initial post.

 

[Joe4]

Looks like it didn't like that I tried to bold the "pu" I added... Anyway, thank you so much!

You cannot use Formatting options within VBA code tags, but you can within Rich code tags.
I changed the code in your post to use Rich code tags.

 

[andrewmshoe]

Post few string that the code does not extract.

I believe your string is something different from what you posted in your initial post.

u1343

p14rc

I was mistaken in saying they all start with P or U. There were also some the start with lowercase p or u.

 

[Georgiboy]

You could use the 'IgnoreCase' option:

Function GetFive(str As String)
  Dim regEx As Object, rm As Object, m As Object, r As String
 
  Set regEx = CreateObject("VBScript.RegExp")
  With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "[pu]\d[a-z\d]{3}"
    Set rm = .Execute(str)
  End With
  For Each m In rm: r = r & m.Value & " ": Next m
  GetFive = Application.Trim(r)
End Function

 

[Fuji]

No, what I wanted to see is whole string.
try remove \b from the pattern

       .Pattern = "[PUpu]\d[A-Za-z\d]{3}"

 

[Cubist]

Try this for formula option.

Book1
	A	B
1	Hello there P0D1C, im great,% U1212	P0D1C U1212
2	My name is P6717 and I happy	P6717
3	This has both upper P6231 and lower p12312, plus U123Ud, minus u543d0d	P6231 p12312 U123Ud u543d0d
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(t,TEXTSPLIT(A1,{" ",","},,1),x,LEFT(t,1),TEXTJOIN(" ",,FILTER(t,((x="P")+(x="U"))*(ISNUMBER(--MID(t,2,1))))))