Use regex to compare cells

richiwatts

Board Regular
Joined
Aug 27, 2002
Messages
131
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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
In standard module
Code:
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
Code:
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
 
Last edited:
Upvote 0
You can achieve this without REGEX

In Standard Module
Code:
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
Code:
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")
 
Upvote 0
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),"")
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top