×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Excel custom format data validation
4

Excel custom format data validation

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?
https://www.tek-tips.com/viewthread.cfm?qid=1757756

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... ponder

&/5.4 336 evaluates to TRUE sad

---- 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 wiggle

---- 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

smile

RE: Excel custom format data validation

Show off upsidedown

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.

Reply To This Thread

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close