Morefunc REGEX.FIND

[DonkeyOte]

In relation to thread: http://www.excelforum.com/excel-prog...wildcards.html and my very first question...

First let me start off by stating as clearly as possible that this thread is concerned solely with the creation of a Regular Expression Pattern ... I am not looking for "alternative" non-REGEX methods.

What I want to do is build a REGEX pattern that will establish as to whether or not a string matching a specified pattern can be found in another cell... the string rules are such:

Please Login or Register  to view this content.

Thus far I came up with the below (where A1 holds string of interest)

=REGEX.FIND(A1,"(30[0-9]{4}-[0-9]{2})")

Now this is all fine up to the point of the last character... ie the variable 10th Char... given it may or may not exist and where it does it may or may not be numeric.

I need to work out how to alter the Expression to look for a numeric 10th Char but only where Char 10 exists... ie if I change the {2} to {3} in the Expression that FIND will fail as you would expect whereas in reality if Chars 8 & 9 are numeric and no Char 10 exists the FIND should return 1 given it is a valid "ending" for the string in question...

Sample values for which REGEX.FIND should return 0

Please Login or Register  to view this content.

Sample values for which REGEX.FIND should return 1

Please Login or Register  to view this content.

I guess I "could" cheat and alter the source String if < 10 chars and append a 0 to the end, ie:

=REGEX.FIND(A1&IF(LEN(A1)<10,0,""),"(30[0-9]{4}-[0-9]{3})")

but I'm guessing there must be a way of doing this using the pattern itself... I'm keen to learn!

TIA
The Donkey

[shg]

I don't use MoreFunc, but maybe "(30[0-9]{4}-[0-9]{2,3})"

[DonkeyOte]

Thanks shg, but that doesn't (seemingly) cater for those ending say: ##@

[NBVC]

How about?

=REGEX.FIND(A1,"(30[0-9]{4}-[0-9]{2}[0-9]{1})")

[NBVC]

Sorry... that doesn't work with sample 1

[shg]

In post#1 you said

Chars 8-9/8-10: must be numeric (where 10 Chars present)

Then you said

ie the variable 10th Char... given it may or may not exist and where it does it may or may not be numeric.

Clarify?

[NBVC]

Maybe then?

=REGEX.FIND(A1&" ","(30[0-9]{4}-[0-9]{2}[^a-z,A-Z]{1})")

[DonkeyOte]

I had hoped the samples would have clarified my poor narrative... basically what I meant was - in relation to the last part of the pattern:

if the string is of 10 chars in length in addition the last three chars must be numeric
if the string is of 9 chars in length the last two chars must be numeric

the issue with the {2,3} approach is where you have a 10 char string ending with ##@, ie:

301234-12X

the {2} will match even though the {3} does not.

[shg]

With the prior question still pending, clamp to entire string:

^(30\d{4}-\d{2,3})$

EDIT: This is responsive to your last post

[DonkeyOte]

=REGEX.FIND(A1&" ","(30[0-9]{4}-[0-9]{2}[^a-z,A-Z]{1})")

Yes that would do it I think... though I'd perhaps be lazy and set to non case sensitive

=REGEX.FIND(A1&" ","(30[0-9]{4}-[0-9]{2}[^A-Z]{1})",,FALSE)

What is the key for any non-numeric (ie instead of A-Z) - presumably there is one ?

Thanks again all... the learning continues.

[shg]

[^0-9] or \D

See also my prior post.

[DonkeyOte]

^(30\d{4}-\d{2,3})$

This works also - no surprise there ... back to the books

[NBVC]

This works also - no surprise there ... back to the books

I am pretty much the same when it comes to regex... I learn on as needed basis and accumulate from there...