Regex pattern to enter 4 digit number which can start with 0 as well

[sn152]

Hi All,

I have a regex function which will restrict the user from entering more than 4 digits in a cell. But the problem is, the user should be able to enter 4 digit number which can also start with 0. This is not possible using the below function. Please help me here. Thanks!

Function CheckSite(ByVal txt As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "^\d{4}"
        CheckSite = .test(txt)
    End With
End Function

 

[shrivallabha]

If I put "0123" in cell A1 and if I use
=CheckSite(A1)

then it returns TRUE and so it does for 1234. Can you describe little more as how you are implementing this in your workbook?

 

[NARAYANK991]

Hi ,

See if this pattern works :

^0*[1-9]{4}$

Narayan

 

[Chihiro]

I assume it's more to do with cell formatting rather than issue with RegEx pattern. Your pattern will validate for string starting with 4 digit number including 0 to 9. Though like Narayan did, I'd recommend adding $ sign at end to signify end of string.

If your cell is formatted as General/Number, try formatting it as text and see if you can enter leading 0.

One other way I can think of is to use single quote in front of zero. RegEx pattern will ignore the single quote and will return true for '0123.

 

[NARAYANK991]

I assume it's more to do with cell formatting rather than issue with RegEx pattern. Your pattern will validate for string starting with 4 digit number including 0 to 9. Though like Narayan did, I'd recommend adding $ sign at end to signify end of string.

If your cell is formatted as General/Number, try formatting it as text and see if you can enter leading 0.

One other way I can think of is to use single quote in front of zero. RegEx pattern will ignore the single quote and will return true for '0123.

Hi ,

The point to be noted is that the CheckSite function is taking a text string as a parameter ; thus , instead of being called as :

=CheckSite(A1)

it will be called as :

=CheckSite("000001234")

and it should return TRUE.

Narayan

 

[NARAYANK991]

Hi ,

Probably a more correct pattern would be :

^0*[1-9][0-9]{3}$

Narayan

 

[Chihiro]

Yes, but from what I've read from Op, he wants to restrict to 4 digit number, including those with leading zero. User is not able to enter leading zero (or rather display) in cell, from what I've read.

That issue is more to do with HOW you enter leading zero into the cell or how the cell is formatted rather than pattern checking. \d stands for any digit (i.e. 0-9) and pattern will accommodate for any string that start with 4 leading numeric character.

So even "00123455dddifs" will return true. When checked against "^\d{4}"

Where as "d0123" will return false.

Pattern can be "^\d{4}$" or any other variants there of.

 

[sn152]

Thanks for your reply Narayan, Shrivallabha, Chihiro. But it doesn't works.

To give you a brief about what I am trying to do is, I have an excel sheet where in column D users have to enter a 4 digit number. The number could be anything between 0000 to 9999. I tried doing this with regex function mentioned above. Using this function, the users are able to enter numbers starting from 1000 to 9999. But 0000 to 0999 doesnt work.

 

[shrivallabha]

Thanks for your reply Narayan, Shrivallabha, Chihiro. But it doesn't works.

To give you a brief about what I am trying to do is, I have an excel sheet where in column D users have to enter a 4 digit number. The number could be anything between 0000 to 9999. I tried doing this with regex function mentioned above. Using this function, the users are able to enter numbers starting from 1000 to 9999. But 0000 to 0999 doesnt work.

Precisely what has been discussed. It has got nothing to do with the regex function really and "it doesn't work" doesn't carry any meaning unless you describe the desired behavior.

The users not being to input numbers cannot be attributed to the UDF. See attached file for implementation if above responses are unclear to you.

 

[sn152]

Hi All,

To explain it in a better way I have attached a sample sheet. In the attached sheet, in column C,user should be able to enter only 4 digit numbers between 0000 to 9999. But with the current function that I use, users will be able to enter numbers between 1000 to 9999. Please see the data validation and the function code.


Thanks!

 

[Chihiro]

Like it's been explained many times...

You either need to format the cell as "Text".
OR
User should use ' (Single Quote) as first character when they type in the numbers.

Ex: '0001, '1234 etc