Looking for a formula to extract a tax id from a string

try this udf use as =extractstring(a1)
it should pull any 2digit hypen 8digit string in this format xx-xxxxxxxx

Function extractstring(s As String) As String
Static rx As Object
Dim rxMatches As Object
 
If rx Is Nothing Then
    Set rx = CreateObject("VBScript.RegExp")
    With rx
        .Pattern = "\d{2}\-\d{8}"
        .Global = True
        .IgnoreCase = True
    End With
End If
 
With rx
    Set rxMatches = .Execute(s)
    If rxMatches.Count > 0 Then extractstring = rxMatches(rxMatches.Count - 1).Value
End With
End Function

paste it in a new module instead
insert/module that should create and open module 1
paste it in there
this workbook has it in

This User Defined Function (UDF) pulls only valid tax ID numbers (##-########)...ignoring patterns like 123-123456789 or 12-123456789...but allowing for values like: A12-12345678G

To use it:
• ALT+F11...to open the VBA Editor
• Select your workbook from the VBAProject list
• Insert.Module
• Copy the below code and paste it into that module

Function PullTaxID(ByVal CellText As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object
CellText = " " & CellText & " "
result = "no match"

Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "\D\d{2,2}\-\d{8,8}\D"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(CellText)

If allMatches.Count <> 0 Then
    RE.Pattern = "\d{2,2}\-\d{8,8}"
    Set allMatches = RE.Execute(CellText)
    result = allMatches.Item(0)
End If

PullTaxID = result

End Function

To use that UDF to pull the tax ID from cell A1
B1: =PullTaxID(A1)

Is that something you can work with?

This code matches 2 numbers (not preceded by a number) followed by a dash followed by 7 numbers(not followed by a number):

Function PullTaxID(ByVal CellText As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object
CellText = " " & CellText & " "
result = "no match"

Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "\D\d{2,2}\-\d{7,7}\D"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(CellText)

If allMatches.Count <> 0 Then
    RE.Pattern = "\d{2,2}\-\d{7,7}"
    Set allMatches = RE.Execute(CellText)
    result = allMatches.Item(0)
End If

PullTaxID = result

End Function

Whoops it seems that my example data has 1 too many 1's, there are 2 digits before the - and then 7 digits following.

Martin, I tried changing the line:
.Pattern = "\d{2}\-\d{8}"
to
.Pattern = "\d{2}\-\d{7}"

The results are still blank cells. Where else do I adjust? Thanks!

Can you post some samples that should match...but don't?

nothing that reg ex matches the pattern xx-xxxxxxx 2-7 so it should work post a sample like ron said
and i have no idea what US tax numbers look like

Ok...maybe this?

Function PullTaxID(ByVal CellText As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object

result = "no match"

Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "\d{2}\-\d{7}"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(CellText)

If allMatches.Count <> 0 Then
    result = allMatches.Item(0)
End If

PullTaxID = result

End Function