search criteria where contains other than specified condition using Regex

abenitez77

Board Regular
Joined
Dec 30, 2004
Messages
149
I have a variable with a string...and I want to know if it contains any value other than single quote, comma and a space ("', ") I'm using vba in excel. for example, i have a variable

This should return > 0:
strA = "'test', 'player'"

I want to check to see if strA has any characters other than "', " (single quote, comma and space).

This should return 0
second example:
strA = ",'"

I tried using regex but I'm getting a match when i only have comma, single quote and space. Can someone help ?

Code:
If RegexCountMatches(" ',',' ',''", "\S[^,']") > 0 Then
  msgbox("found match")
Endif 

Public Function RegexCountMatches(str As String, reg As String) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg: regex.Global = True
    If regex.test(str) Then
        Set matches = regex.Execute(str)
        RegexCountMatches = matches.Count
        Exit Function
    End If
ErrHandl:
    RegexCountMatches = CVErr(xlErrValue)
End Function
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Without regex :
Code:
If Len(Replace(Replace(Replace([A1], "'", ""), ",", ""), " ", "")) > 0 Then MsgBox "Found"
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

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