incorrectly formatted reference number

Good day,

I have been trying to get this verification done by formula and than highlighting the values but cannot seem to find how since my formulas do not work at all. Maybe somebody can explain to me what I am doing wrong when providing an answer?

C-2019-2020-Q2-00001
C-2019-2020-Q2-00002
C-2019-2020-Q2-00003

Please Login or Register  to view this content.

B50ZM-000001/001/BD
B50ZM-000001/001/BD
B50ZM-000001/002/BD

Please Login or Register  to view this content.

For your expertise,

many thanks

Ignore this reply, I'm not thinking straight...

May be...

Formula:

Please Login or Register  to view this content.

But note that ? will accept any character in this case (alphabet or numeric). So you may want additional checks.

Personally, I think it's easier to use VBA and RegExp pattern matching.

I thought ? first of all but it didnt seem to work with my test values.

Maybe this for the first one?

=AND(LEFT(B2,1)>="A",LEFT(B2,1)<="Z",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"0","9"),"1","9"),"2","9"),"4","9"),"5","9"),"6","9"),"7","9"),"8","9"),2,99)="-9999-9999-Q9-99999")

This checks for
first character alphabetic
and converts all digits to 9

though this would allow for Q5-Q9 which are presumably quarters, in which case you'll need an additional condition based on the first alphabetic check but to check for the range 1-4 for the quarters AND(MID(B2,13,1)>="1",MID(B2,13,1)<="4")

Thank you folks, and yes VB is always better but I have a colleague who prefers formulas so I have both of them done.

Here's what came out of this post that worked.

Please Login or Register  to view this content.

thx again

You are welcome and thanks for sharing your solution

Originally Posted by bassinator

Thank you folks, and yes VB is always better but I have a colleague who prefers formulas so I have both of them done.

Here's what came out of this post that worked.

Please Login or Register  to view this content.

thx again

This wont work if they put:

alphanumeric in the year, e.g. C-2a19-2020-Q2-00001 or C-2019-202a-Q2-00001 or
alphanumeric in the last 5 digits, e.g. 'C-2019-2020-Q2-00b01

These values all result in "ok" though they are all an incorrectly formatted reference number