Excel custom format data validation 4

[renigar]

I want to limit errors on item ID entry to this exact alpha numeric format: AANANANNN The entry will always be 9 characters.
Example ID: WD5A4V336

I searched through the posts and found a problem similar to mine but don't quite understand the formula used and the formula is to long for data validation. The link to that post is below.

Limit Format in Cell to NAANNAA Format Using Data Validation in Excel?
[URL unfurl="true"]https://www.tek-tips.com/viewthread.cfm?qid=1757756[/url]

I wrote this formula but the first section (highlighted) does not work correctly. The rest works great.
=AND([highlight #FCE94F]NOT(ISNUMBER(VALUE(LEFT(B5,2))))[/highlight],ISNUMBER(VALUE(MID(B5,3,1))),NOT(ISNUMBER(VALUE(MID(B5,4,1)))),
ISNUMBER(VALUE(MID(B5,5,1))),NOT(ISNUMBER(VALUE(MID(B5,6,1)))),ISNUMBER(VALUE(RIGHT(B5,3))))

What is happening is, if both of the first two characters in the ID are numbers it will evaluate false. If either one of the first two characters in the ID are a number it will evaluate true when I need it to be false. And of course it evaluates true when the first two characters are letters. I think this is an easy one but I can't see it at the moment. Any help would be appreciated.

Thanks, renigar

 

[Andrzejek]

L - Letter
N - Number

So, if you have: [tt]LLNLNLNNN[/tt], that will evalluate to True
Anything else will evaluate to False

If that's the case, why not write your own User Define Function and do all of that in a little VBA code?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

Thanks Andrzejek,

Right before I read this I decided to evaluate the first two characters separately. It works how I want with the following formula and comes in at 227 characters which will work for data validation.

=AND(NOT(ISNUMBER(VALUE(LEFT(B5,1)))),NOT(ISNUMBER(VALUE(Mid(B5,2,1)))),ISNUMBER(VALUE(MID(B5,3,1))),
NOT(ISNUMBER(VALUE(MID(B5,4,1)))),ISNUMBER(VALUE(MID(B5,5,1))),NOT(ISNUMBER(VALUE(MID(B5,6,1)))),
ISNUMBER(VALUE(RIGHT(B5,3))))

renigar

 

[Andrzejek]

>It works how I want
Well, does it...

[tt][red]&/5.4 336[/red][/tt] evaluates to TRUE :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

Thanks for pointing that out to me. There is always something I didn't consider. I also forgot to limit the number of characters to 9. I thought I had an easy answer. How wrong I am. Is there a way to do this in a formula that would fit in Data Validation? I don't know (understand) how to approach this with a VBA function. I didn't understand the formula in the post I referred to. Any suggestions, tips, etc.

 

[Andrzejek]

Dump this code in a Module in Excel:

Option Explicit

Public Function [red]renigar[/red](ByRef str As String) As Boolean[green]
'12 3 4 5 6 789
'LL N L N L NNN[/green]
Const MYLIMIT As Integer = 9
Dim x As Integer

If Len(Trim(str)) <> MYLIMIT Then Exit Function

For x = 1 To MYLIMIT
    Select Case x
        Case 1, 2, 4, 6[green]
            'Letters[/green]
            Select Case Mid(str, x, 1)
                Case "A" To "Z"[green]
                    'All is fine[/green]
                Case Else[green]
                    'We are done checking here[/green]
                    Exit Function
            End Select
        Case 3, 5, 7 To 9[green]
            'Numbers[/green]
            If Not IsNumeric(Mid(str, x, 1)) Then Exit Function
    End Select
Next x
[green]
'If you got to this place, all is fine in the world[/green]
renigar = True

End Function

And use it as any other formula

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

Andrzejek,

Wow, you have gone above and beyond. Didn't expect you to actually write it for me. It works great! And looking at the code it actually looks easy. I may have gotten there on my on after much trial and error but you have saved me some hours and given me a nice lesson at the same time.

Thank you,
renigar

 

[strongm]

Seems an ideal opportunity for a regular expression

 

[combo]

Seems an ideal opportunity for a regular expression

Public Function renigar2(str As String) As Boolean
    renigar2 = str Like "[A-Z][A-Z]#[A-Z]#[A-Z]###"
End Function

combo

 

[strongm]

 

[Andrzejek]

Show off

Like some people say: "There are always more ways to skin the cat"
or
If all you know is how to use a hammer, everything looks like a nail...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

Most impressive Combo. The height of simplicity and conservation of code.
renigar

 

[strongm]

I'm feeling unloved ...

 

[Deniall]

... and I can take a hint.