Using REGEX for field validation

Question

I am writing a macro with multiple sheet and multiple validations. One of the requirement is to limit 1000 characters in range of cells. You are allowed to enter any character. I am achieving it through regex. My code reads as -----> Public Const QUAL_REGEX = "^.{1,1000}$"

I also have an error handling if the above condition s not met. For e.g. if the length exceeds 1000 characters. This works fine. However, it gives an error when there is line break or enter character. I want to allow users to be able to use enter or line breaks. How can I achieve using REGEX.

you can check the cell value length instead : IF Len(cell.Value) > 1000 Then — Slai
– Slai, Commented Feb 23, 2019 at 10:51
You can try this negative lookahead based regex:similiar to ^(?:(?! *, *\^)[\s\S])* Further `(?! *, *\^) # negative lookahead that fails the match if next pattern is 0 or more spaces # followed by a comma and optional spaces and literal ^ [\s\S] # matches any character including newlines. As explained in the SO Link<stackoverflow.com/questions/30785808/…> — skkakkar
– skkakkar, Commented Feb 23, 2019 at 11:09

Ron Rosenfeld · Accepted Answer · 2019-02-23 12:00:32Z

2

The problem is that, in VBScript Regex, dot does not match newline. To get around that in a Regex, try "^[\s\S]{1,1000}$"

Note that this would be the same as if you just tested that the length of the entry was in the range of 1-1000, and would not involve the extra overhead of the Regular Expression engine.

answered Feb 23, 2019 at 12:00

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using REGEX for field validation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related