Thread: Searching value of specific length

Originally Posted by StephenR

Sorry, where does the 210521 come from? Subtract what from what?

OK, I see now, it was off the edge of the code window.

I see the problem. I thought about removing the spaces but since they are there in the workbook I don't know what the effect would be in a formula. So I left it at the original form.

There is no way to enlarge the code box, is there?

I hope you can help me with this question.

Marco

Thank you, this works! But there is one thing I'm worried about.
I tested this for cell A1. The extracted value should be 210521.
But whenever I add more number to the value, like: 21052111111, it still subtract 210521, which shouldn't be the case. It should only be subtracted when the length is exactly 6. Could this be achieved?

Thanks in advance,

Marco

I think this works:

Function x(rCell As Range) As Variant

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([^0-9]+)(21[0-9]{4})([^0-9]+)"
    If .Test(rCell) Then
        x = .Execute(rCell)(0).SubMatches(1)
    Else
        x = ""
    End If
End With

End Function

Originally Posted by StephenR

I think this works:

Function x(rCell As Range) As Variant

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([^0-9]+)(21[0-9]{4})([^0-9]+)"
    If .Test(rCell) Then
        x = .Execute(rCell)(0).SubMatches(1)
    Else
        x = ""
    End If
End With

End Function

Thanks again for the quick reply. It seems to work for a few rows.
I added an example with above VBA, the output and a column with the number which it should be. I hope this helps!

Marco

Sorry for the double post, but I discovered the following.
The numbers which doesn't get subtracted from the rows all are the end of the data in that cell. Whenever I add a space to the end it does get subtracted, but that shouldn't be necessary. Any idea?

Try changing the two '+' to * in the Pattern line.

Originally Posted by StephenR

Try changing the two '+' to * in the Pattern line.

Although this works, the previous problem comes back again where it subtracts numbers containing 7 digits which start with 21, which it shouldn't do. I added a new example. Cell A9 in that sheet contains data with a number which shouldn't be subtracted, however, it does.

Thank you.

Marco

Ah yes missed that. This seems to work:

Function DEB(rCell As Range) As Variant

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(^|[^0-9]+)(21[0-9]{4})($|[^0-9]+)"
    If .Test(rCell) Then
        DEB = .Execute(rCell)(0).SubMatches(1)
    Else
        DEB = ""
    End If
End With

End Function

may be so (as an option)

With CreateObject("VBScript.RegExp")
    .Pattern = "\b21\d{4}\b"
    If .Test(rCell) Then DEB = .Execute(rCell)(0) Else DEB = ""
End With

Originally Posted by StephenR

Ah yes missed that. This seems to work:

Function DEB(rCell As Range) As Variant

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(^|[^0-9]+)(21[0-9]{4})($|[^0-9]+)"
    If .Test(rCell) Then
        DEB = .Execute(rCell)(0).SubMatches(1)
    Else
        DEB = ""
    End If
End With

End Function

Thank you very much! That works indeed!
I have one little question though. Could you please add notes with ' explaining in short what the code does exactly? Especially this line:

    .Pattern = "(^|[^0-9]+)(21[0-9]{4})($|[^0-9]+)"

I kinda understand what it does, but I'm not sure what the "^", "+", etc. mean.
Thank you!

Originally Posted by nilem

may be so (as an option)

With CreateObject("VBScript.RegExp")
    .Pattern = "\b21\d{4}\b"
    If .Test(rCell) Then DEB = .Execute(rCell)(0) Else DEB = ""
End With

This seems to work as well! Thanks for your response.
Could you also please add notes like I asked StephenR, especially for this line:

    .Pattern = "\b21\d{4}\b"

Same here, I kinda understand what it does, but not everything.

Thanks in advance,

Marco

There are plenty of websites on regular expressions. It's quite an involved but powerful method for matching text strings. In outline:

(^|[^0-9]+)

Says look either at the beginning of the cell or for any characters which are not numbers (which may or may not be there)

(21[0-9]{4})

Says look for 21 followed by 4 numbers (this is the bit we want)

($|[^0-9]+)

says look either at the end of the cell or for any characters which are not numbers (which may or may not be there).

nilem's code works on a similar principle, but he his code is more succinct.

Thanks for your help StephenR!
Now another thing regarding this came around.
First the condition was that no numbers could be infront the "21". Now something changed and it seems numbers are no problem.
I was trying things out and I removed a "^" in the code, making it work.

Function DEB(rCell As Range) As Variant

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(|[^0-9]+)(21[0-9]{4})($|[^0-9]+)"
    If .Test(rCell) Then
        DEB = .Execute(rCell)(0).SubMatches(1)
    Else
        DEB = ""
    End If
End With

End Function

I removed the first "^" in the following line:

    .Pattern = "(^|[^0-9]+)(21[0-9]{4})($|[^0-9]+)"

Why does this work? And is it a good way to solve it? What difference does "^|" make over just the "|"?

Another question. Although it rarely happens, the format could also be like this:
0000 0000 0021 1234

Is there a way to adjust to code to either look for a space and than 4 numbers, just 4, no 5, or look as the code already works, 4 adjacent numbers without a space?

Thanks for all your help!

Marco