Use regex to compare cells

[richiwatts]

I need to carry out 3 checks between 2 cells

Check 1 - Inconsistent capitalization
In another tool we use ^[A-Z] (In the filter source field) and ^[a-z] (In the target field)

Check 2 - Inconsistent punctuation
In aother tools we use \.$ (In the filter Source field) and [^.]$ (In the Target field)

Check 3 number mismatch
A1 - I am 20 today
A2 - I am 30 today
Show error that there is number mismatch

Can anyone help with the formula to catch any of these?

 

[Yongle]

In standard module

Option Explicit
Public Function ParseNum(ByVal strInput As String) As String
    Dim regex As Object
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "[^\d]"
        ParseNum = .Replace(strInput, Empty)
    End With
End Function

Calling the function

Sub CompareTwoStrings()
    Select Case ParseNum(Range("A1")) = ParseNum(Range("A2"))
        Case True:  MsgBox "OK"
        Case Else:  MsgBox "MISMATCH ERROR", vbExclamation
    End Select
End Sub

UDF used as formula in worksheet
=IF(ParseNum(A2)=ParseNum(A1),"OK","Mismatch Error")

Note
This function creates a string of ALL numbers in the original string
"I am 20 on the 23rd March" returns 2023

 

[Yongle]

You can achieve this without REGEX

In Standard Module

Public Function NumbersOnly(ByVal txt As String) As String
  Dim c As Long
  For c = 1 To Len(txt)
    If Not IsNumeric(Mid(txt, c, 1)) Then Mid(txt, c) = " "
  Next c
  NumbersOnly = Application.Trim(c)
End Function

Calling function

Sub CompareTwoStrings2()
    Select Case NumbersOnly(Range("A1")) = NumbersOnly(Range("A2"))
        Case True:  MsgBox "OK"
        Case Else:  MsgBox "MISMATCH ERROR", vbExclamation
    End Select
End Sub

Worksheet formula
=IF(numbersonly(A2)=numbersonly(A1),"OK","Mismatch Error")

 

[Yongle]

You could achieve this without a UDF
- this formula extracts numbers only from cell A1

=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"")