×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Excel custom format data validation4

 Forum Search FAQs Links MVPs

## Excel custom format data validation

(OP)
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?

I wrote this formula but the first section (highlighted) does not work correctly. The rest works great.
=AND(NOT(ISNUMBER(VALUE(LEFT(B5,2)))),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

### RE: Excel custom format data validation

L - Letter
N - Number

So, if you have: LLNLNLNNN, 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

### RE: Excel custom format data validation

(OP)
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

### RE: Excel custom format data validation

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

&/5.4 336 evaluates to TRUE

---- Andy

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

### RE: Excel custom format data validation

(OP)
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.

### RE: Excel custom format data validation

Dump this code in a Module in Excel:

#### CODE

Option Explicit

Public Function renigar(ByRef str As String) As Boolean
'12 3 4 5 6 789
'LL N L N L NNN
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
'Letters
Select Case Mid(str, x, 1)
Case "A" To "Z"
'All is fine
Case Else
'We are done checking here
Exit Function
End Select
Case 3, 5, 7 To 9
'Numbers
If Not IsNumeric(Mid(str, x, 1)) Then Exit Function
End Select
Next x

'If you got to this place, all is fine in the world
renigar = True

End Function 

And use it as any other formula

---- Andy

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

### RE: Excel custom format data validation

(OP)
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

### RE: Excel custom format data validation

Seems an ideal opportunity for a regular expression

### RE: Excel custom format data validation

2

#### Quote (strongm)

Seems an ideal opportunity for a regular expression

#### CODE -->

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

combo

### RE: Excel custom format data validation

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

### RE: Excel custom format data validation

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

### RE: Excel custom format data validation

I'm feeling unloved ...

### RE: Excel custom format data validation

... and I can take a hint.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!