What is the regex for the escaping []?

Question

I am trying to match square brackets i.e. [] in regex VBA in excel. I am trying with the below code but it is not working.

 Public Function IsSpecial(s As String) As Long
    Dim L As Long, LL As Long
    Dim sCh As String
    IsSpecial = 0
    For L = 1 To Len(s)
        sCh = Mid(s, L, 1)
        If sCh Like "[0-9a-zA-Z/;@%,'‚.+&/\(): ]" Or sCh = "_" Or sCh Like "[-]" Or sCh Like "\[" Then
        Else
            IsSpecial = 1
            Exit Function
        End If
    Next L
End Function

If sCh Like "[0-9a-zA-Z/;@%,'‚.+&/\(): ]" Or sCh = "_" Or sCh Like "[-]" Or sCh Like "\[" this is my actual condition and like is working fine, only the last OR condition is not working — newbie0007, Apr 01 '21 at 10:06
Nope, still not working. I've updated my question. Accordingly if the code finds [] it should return 0 but after using your above suggestion it is still returning 1 — newbie0007, Apr 01 '21 at 10:16
Ok, it seems regex is still a valid choice here as you need to check sequences of chars, not just individual chars. — Wiktor Stribiżew, Apr 01 '21 at 10:42

Wiktor Stribiżew · Answer 1 · 2021-04-01T10:41:21.847

2

According to Using the Like operator and wildcard characters in string comparisons:

You can use a group of one or more characters (charlist) enclosed in brackets ([ ]) to match any single character in expression, and charlist can include almost any characters in the ANSI character set, including digits. You can use the special characters opening bracket ([), question mark (?), number sign (#), and asterisk (*) to match themselves directly only if enclosed in brackets. You cannot use the closing bracket (]) within a group to match itself, but you can use it outside a group as an individual character.

So, you need to use

Ch Like "[[]"

However, the function you have is not following your logic, since it checks each char individually, and you want to make sure [] is checked as a char sequence.

With a regex, it will look like

Public Function IsSpecial(s As String) As Long
    Dim L As Long, LL As Long
    Dim rx As New regExp
    rx.Pattern = "^(?:[0-9a-zA-Z/;@%,'‚.+&/\\(): _-]|\[])*$"
    IsSpecial = 0
    If Not rx.Test(s) Then IsSpecial = 1
End Function

edited Apr 01 '21 at 10:41

answered Apr 01 '21 at 10:16

Wiktor Stribiżew

607,720
39
448
563

sorry i need to match both the opening and closing brackets not only any one of them – newbie0007 Apr 01 '21 at 10:19
If the pattern is found then my code returns 0 as else it returns 1. Now my problem is that i also want to match [] (both of them opening and closing square brackets) – newbie0007 Apr 01 '21 at 10:24
@newbie0007 It seems you want to check if there is `[]` substring in the input, right? But right now, you check each char individually. – Wiktor Stribiżew Apr 01 '21 at 10:25
yes correct, and after using your code Ch Like "[ [ ]" function is still returning 1 because your code is not matching the closing bracket – newbie0007 Apr 01 '21 at 10:27
@newbie0007 It will be much simpler with a regex. – Wiktor Stribiżew Apr 01 '21 at 10:29
please tell how? if you could replace my whole if statement with a simpler regex which should include all the characters which i have – newbie0007 Apr 01 '21 at 10:30
@newbie0007 Check the new function. – Wiktor Stribiżew Apr 01 '21 at 10:41
It is giving compile time error at line 3. It says user defined type not defined – newbie0007 Apr 01 '21 at 10:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230630/discussion-between-newbie0007-and-wiktor-stribizew). – newbie0007 Apr 01 '21 at 10:53
figured it out but it is still returning 1 for [ ] – newbie0007 Apr 01 '21 at 11:02
@newbie0007 See https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops. If you need to allow `[ ]`, add the space between `\[` and `]` – Wiktor Stribiżew Apr 01 '21 at 11:02

What is the regex for the escaping []?

1 Answers1