Validate the format of a number

W

Warrior

I need to determine that an entered serial number is valid. It must check
that it is 11 characters and follows the format as follows:
a letter, followed by a number, followed by 2 letters, followed by 6
numbers, and ending with a letter.

For example, the user enters D7PM234567B and the cell next to it would
indicate 'valid' or something similar.
If 87PM2345674 was entered, it was indicate 'invalid' next to it or
something similar to alert the user it is not in the correct format.

Thank you very much in advance.

Steve
 
B

Bernard Liengme

This formula
=AND(LEN(A1)=11,CODE(LEFT(A1))>64,CODE(MID(A1,3,1))>64,CODE(MID(A1,4,1))>64,CODE(RIGHT(A1))>64,
ISNUMBER(--MID(A1,2,1)), ISNUMBER(--MID(A1,5,6)))
will check for digits ('numbers') as required, the test for 'text' actually
checks that the character's code is greater than 64 (capital A has code 65).
You may want to add CODE(position)< 91 to restrict characters to A ...Z.

This formula
=AND(LEN(A1)=11,NOT(ISNUMBER(--LEFT(A1))),NOT(ISNUMBER(--MID(A1,3,2))),NOT(ISNUMBER(--RIGHT(A1))),ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,5,6)))
is less 'fussy'. It checks that the first, third, fourth and last are not
digits

The formulas will return TRUE or FALSE. You could wrap either in an IF to
get "Valid" or "Invalid".
=IF(formula, "Valid" , "Invalid")

best wishes
 
R

Ron Rosenfeld

I need to determine that an entered serial number is valid. It must check
that it is 11 characters and follows the format as follows:
a letter, followed by a number, followed by 2 letters, followed by 6
numbers, and ending with a letter.

For example, the user enters D7PM234567B and the cell next to it would
indicate 'valid' or something similar.
If 87PM2345674 was entered, it was indicate 'invalid' next to it or
something similar to alert the user it is not in the correct format.

Thank you very much in advance.

Steve
Click to expand...

One method is with a UDF to return True or False.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like
=ValidData(A1)
in some cell.

=========================================
Option Explicit
Function ValidData(s As String) As Boolean
ValidData = s Like "[A-Z]#[A-Z][A-Z]######[A-Z]"
End Function
=========================

The above UDF is Case SENSITIVE and requires that the letters be capitalized.
To make this case INsensitive, merely add Option Compare Text

===============================
Option Explicit
Option Compare Text
Function ValidData(s As String) As Boolean
ValidData = s Like "[A-Z]#[A-Z][A-Z]######[A-Z]"
End Function
==========================
--ron
 
B

Bernard Liengme

This better for second formula
=AND(LEN(A1)=11,NOT(ISNUMBER(--LEFT(A1))),NOT(ISNUMBER(--MID(A1,3,1))),NOT(ISNUMBER(--MID(A1,3,1))),NOT(ISNUMBER(--RIGHT(A1))),ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,5,6)))

while can be reduced to
=AND(LEN(A1)=11,
NOT(ISNUMBER(--LEFT(A1))+ISNUMBER(--MID(A1,3,1))*ISNUMBER(--MID(A1,3,1))+ISNUMBER(--RIGHT(A1))),ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,5,6)))

Bernard
 
W

Warrior

Guys,

Each of your formulas worked nicely and thank you so much.
However, there was one limit I forgot to mention: the 1st character (text)
can't be higher than an M since it coincides with the month of December. (A
is Jan, B is Feb etc and the letter I is not used). Any additional help on
this validation?

Steve
 
B

Bernard Liengme

Since M has code 77 , my first formula can be changed to
=AND(LEN(A1)=11,CODE(LEFT(A1))>64, code(left(a1))< 78,
CODE(MID(A1,3,1))>64,CODE(MID(A1,4,1))>64,CODE(RIGHT(A1))>64,
Bernard
 
W

Warrior

Thanks guys, the codes worked nicely. I ended up using (LEFT(A9,1)<"N")
within the formula. I tried it on several serial numbers and it checks out
ok. I also nested the formula with the IF statement to have Valid and
Invalid as the result. Thanks again to each of you who responded.

Steve
 
R

Rick Rothstein

The above UDF is Case SENSITIVE and requires that the letters be
capitalized.
To make this case INsensitive, merely add Option Compare Text

===============================
Option Explicit
Option Compare Text
Function ValidData(s As String) As Boolean
ValidData = s Like "[A-Z]#[A-Z][A-Z]######[A-Z]"
End Function
==========================
Click to expand...

Or, to avoid imposing Compare Text on the rest of the procedures in the code
module (in case it matters), you can do either this (my preference)...

===============================
Function ValidData(s As String) As Boolean
ValidData = s Like "[A-Za-z]#[A-Za-z][A-Za-z]######[A-Za-z]"
End Function
===============================

or this...

===============================
Function ValidData(s As String) As Boolean
ValidData = UCase(s) Like "[A-Z]#[A-Z][A-Z]######[A-Z]"
End Function
===============================
 
W

Warrior

One last item, how would I have the formula check if the 4th character was M
to indicate ""full" and if it was an L to indicate "starter"?

Thanks
 
R

Ron Rosenfeld

One last item, how would I have the formula check if the 4th character was M
to indicate ""full" and if it was an L to indicate "starter"?

Thanks
Click to expand...

Here's a modification of my UDF:

===============================
Option Explicit
Function ValidData(s As String) As String
Dim bValid As Boolean
bValid = s Like "[A-HJ-M]#[A-Z][A-Z]######[A-Z]"
If bValid = True Then
Select Case Mid(s, 4, 1)
Case Is = "M"
ValidData = "Full"
Case Is = "L"
ValidData = "Starter"
Case Else
ValidData = "Valid"
End Select
Else
ValidData = "Invalid"
End If
End Function
==================================
--ron
 
R

Ron Rosenfeld

Thanks guys, the codes worked nicely. I ended up using (LEFT(A9,1)<"N")
within the formula. I tried it on several serial numbers and it checks out
ok. I also nested the formula with the IF statement to have Valid and
Invalid as the result. Thanks again to each of you who responded.

Steve
Click to expand...

Note that your modification does not include a test for "I" in the first
position, which you wrote was an illegal character.
--ron
 
W

Warrior

Ron Rosenfeld said:
Note that your modification does not include a test for "I" in the first
position, which you wrote was an illegal character.
--ron
Click to expand...

Ron,

Yes, good catch. How can I work that into my formula (LEFT(A9,1)<"N") ?

Steve
 
R

Ron Rosenfeld

Ron,

Yes, good catch. How can I work that into my formula (LEFT(A9,1)<"N") ?

Steve
Click to expand...

Since I supplied the UDF solution, which seems simpler to me to both implement
and maintain, you'd probably best address that question to Bernard. But
possibly something like

AND(left(a9,1)<"N",left(a9,1)<>"I")

--ron
 
W

Warrior

Ron Rosenfeld said:
Since I supplied the UDF solution, which seems simpler to me to both
implement
and maintain, you'd probably best address that question to Bernard. But
possibly something like

AND(left(a9,1)<"N",left(a9,1)<>"I")

--ron
Click to expand...

Thanks Ron, that will work fine when I fit it into my existing formula.

Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Number Format 5
More validadtion problems! 50
Validate user inputted name. 1
Conditional Formatting question 3
validate field by entering number twice 2
Formula to validate characters within a string of data in aparticular cell 1
Conditional formatting in Excel 2010 2
format cell type 6

Top