Regex Pattern for Numerical Data beginning with Zero Only

[NimishK]

Hello

Anyone has any idea How can I Match Numerical/Digit beginning with Zero Only (single digit /First digit) . I've come across some links
using the same with REGEX but did not understand as it has gone over my head
in below coding have few numerical data and from below string How to get the following Number only 000106714972555

Dim stringOne As String
Dim regexOne As Object

Set regexOne = New RegExp
regexOne.Pattern ="\b\d{10, }\b"

StringOne = "90200025272451     PYMT OF BILL NO 1634      000106714972555      INR   17,250.00"

Set theMatches = regexOne.Execute(stringOne)
Debug.Print regexOne

It Prints only 90200025272451

Your input and correction will be helpful

Thanks
NimishK

 

[Peter_SSs]

Try this

regexOne.Pattern = "(^|\D)(0\d+)"

stringOne = "90200025272451     PYMT OF BILL NO 1634      000106714972555      INR   17,250.00"

Set theMatches = regexOne.Execute(stringOne)
Debug.Print theMatches(0).submatches(1)

 

[NimishK]

Dear Peter_SSs

Try this

regexOne.Pattern = "(^|\D)(0\d+)"

stringOne = "90200025272451     PYMT OF BILL NO 1634      000106714972555      INR   17,250.00"

Set theMatches = regexOne.Execute(stringOne)
Debug.Print theMatches(0).submatches(1)

Thank you sir for your valuable input and indeed the correction implemented
where I coded the blunder as #1. It worked perfectly

BTW

What is the importance of theMatches(0) Is it the First Occcurance of the Match or is it the Position
how does submatches help. ie .submatches(1).

Explored about Submatches which seems to be in small parts with examples in different URLS. It seems no proper defination for submatch. Becomes slightly difficult for me to grasp
Corrected Code

Dim stringOne As String
Dim regexOne As Object

Set regexOne = New RegExp
regexOne.Pattern = "(^|\D)(0\d+)"

stringOne = "90200025272451     PYMT OF BILL NO 1634      000106714972555      INR   17,250.00"
Set theMatches = regexOne.Execute(stringOne)

Debug.Print theMatches(0).SubMatches(1)

Thanks
NimishK

 

[Peter_SSs]

What is the importance of theMatches(0)

Matches(0) is the first match found (or the only match found if the regexOne Global property is not set to True). You did not show your whole code so I'm unsure about that.

how does submatches help. ie .submatches(1).

It returns the value you are looking for (See note below)
The pattern I used looks for a zero preceded by a non-digit or the beginning of the test string. If we did not include that in the pattern and only looked for a zero followed by other digits then the result would have been the red part below, not what you were actually looking for.
"90200025272451 PYMT OF BILL NO 1634 000106714972555 INR 17,250.00"
So the pattern I was looking for returns the space character just before the number you are looking for - submatches(0) - and the number you are looking for submatches(1)
Another way of looking at it
regexOne.Pattern = "(^|\D)(0\d+)"
The red part of the pattern gives Submatches(0) and the blue gives submatches(1)

Note: The pattern may need adjustment, depending on what data you could encounter. For example, if the test string was as below, the pattern code that I gave would return the red part, not the blue part because the red part is a zero followed by 1 or more digits and preceded by a non-digit and it is the first match.
"17,250.00 BILL NO 1634 000106714972555"

If something like that is possible then we would need to know more about the logic of how we get the number you actually want.

 

[Peter_SSs]

Note: The pattern may need adjustment, depending on what data you could encounter. For example, if the test string was as below, the pattern code that I gave would return the red part, not the blue part because the red part is a zero followed by 1 or more digits and preceded by a non-digit and it is the first match.
"17,250.00 BILL NO 1634 000106714972555"

Looking back at your original code, that could probably be handled differently. You were looking for a number of 10 digits or more. Since you want it to start with zero, the pattern could perhaps have been

regexOne.Pattern = "(^|\D)(0\d{9,})"

 

[NimishK]

Dear Peter_SSs

Matches(0) is the first match found (or the only match found if the regexOne Global property is not set to True). You did not show your whole code so I'm unsure about that.

That was my Whole code only.

Thank you very much Sir, for indeed a valuable explanation and also another option for 10 digit but These 10 digits are not actual length of 10 digits it can vary < 10 digits or > 10 digits.

Is below sufficient enough
regexOne.Pattern = "(^|\D)(0\d+)" irrespective of 10 digit Length < 10 digit length or >10 digit length or we still need to perfect the pattern

Note: The pattern may need adjustment, depending on what data you could encounter. For example, if the test string was as below, the pattern code that I gave would return the red part, not the blue part because the red part is a zero followed by 1 or more digits and preceded by a non-digit and it is the first match.
"17,250.00 BILL NO 1634 000106714972555"

Sir it would be better to look for Blue part Only for this case ie by adding "(0\d{9, })" or "(0\d+)"

Have we added the blue part in order not to detect the red part
regexOne.Pattern = "(^|\D)(0\d+)"
"17,250.00 BILL NO 1634 000106714972555"

Sir If i am not mistaken below will always look for = and > 9 digit length though 1 digit less
regexOne.Pattern = "(^|\D)(0\d{9,})"

Thank you very much Sir
NimishK

 

[Peter_SSs]

That was my Whole code only.

No, the code would have started with Sub ...() and ended with End Sub and since that was not included there was no way for me to know if other code might also have been omitted. In future I suggest that you post the whole code.

but These 10 digits are not actual length of 10 digits it can vary < 10 digits or > 10 digits.

Well, your code in post 1 was looking for 10 digits or more ..

regexOne.Pattern ="\b\d{10, }\b"

.. so I replicated that in post 5 since you had not mentioned length anywhere else.

Sir it would be better to look for Blue part Only ..

Then we need to know a minimum length for the digits you want or else a complete list of characters that cannot appear before the zero.

Sir If i am not mistaken below will always look for = and > 9 digit length though 1 digit less
regexOne.Pattern = "(^|\D)(0\d{9,})"

You are mistaken. That pattern is looking for a zero followed by at least 9 digits. That means it is looking for at least 10 digits.

 

[Akuini]

@NimishK
I believe using regex is the best way to approach this issue, but I'd like to try a different way.

Sub NimishK_1()
Dim x
Dim tx As String
tx = "90200025272451     PYMT OF BILL NO 1634      000106714972555      INR   17,250.00 011111"

For Each x In Split(tx, " ")
    If Len(x) <> 0 Then
        If Left(x, 1) = "0" Then
            If x Like WorksheetFunction.Rept("#", Len(x)) Then
                Debug.Print x 'returns 000106714972555
                Exit For
            End If
        End If
    End If
Next
End Sub

What the code does:
1. Splits the string into multiple words using a space as the delimiter.
2. Checks if a word begins with "0" and if it consists solely of numbers (without dots, commas, etc.).
3. If multiple words meet the above criteria, only the first one is selected.

However, I think it would be better if you could provide, say, 10-15 rows of various data for inspection — assuming your actual data isn’t sensitive. This way, we can determine the best criteria to use.

 

[NimishK]

Peter_SSs

No, the code would have started with Sub ...() and ended with End Sub and since that was not included there was no way for me to know if other code might also have been omitted. In future I suggest that you post the whole code.

Sure Sir will submit the full Sub Routine and Function

Then we need to know a minimum length for the digits you want or else a complete list of characters that cannot appear before the zero.

Even I am not sure what no of LEN(digits )will Pop up in StringOne at present we are conisdering 9 and above

You are mistaken. That pattern is looking for a zero followed by at least 9 digits. That means it is looking for at least 10 digits.

and thank you sir for Correcting me. Now Picture becomes clearer

Last Question on this Thread

Set theMatches = regexOne.Execute(stringOne)
Debug.Print theMatches(0).SubMatches(1)

At present only this Match as defined is perfectly Coming out of StringOne
Can i add a Variable As String which can = theMatches(0).SubMatches(1)
BTW theMatches(0).SubMatches(1) could be defined as What
The above query could help me if the varaible is string inorder to display message or for further reference of theMatches(0).SubMatches(1) as string

Akuini
Thanks Friend for completely showing us new method and deriving the same in new maner.

Thanks
NimishK

 

[Peter_SSs]

we are conisdering 9 and above

Can i add a Variable As String which can = theMatches(0).SubMatches(1)

Yes. Try something like this

Dim myNum As String
regexOne.Pattern = "(^|\D)(0\d{8,})"

stringOne = "90200025272451     PYMT OF BILL NO 1634      000106714972555      INR   17,250.00"
myNum = "Not found"
If regexOne.test(stringOne) Then myNum = regexOne.Execute(stringOne)(0).submatches(1)