Custom Alphanumeric Formatting using Data Validation

E

Ebreezy757

New Member
Joined
Jul 21, 2017
Messages
3
Hi guys,

I have tried to reverse engineer some formulas that I've found on the forum for my specific case, but I could never get them to work correctly. I was hoping someone might be able to help my lock the input of a cell down to this specific set of characters.

1. First character will always be a letter (text)
2. The next seven characters will always be numbers
3. The ninth character will always be a letter (text)
4. The last four digits will always be numbers.
5. I would like the letters to be remain capital letters

An example would be: N0018712A2345

Thanks in advance for any help you can provide!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
In your VBA editor, from Tools >> References menu find Microsoft VBScript Editor 5.5 and select it and close the window.

Assuming the range of cells that you are trying to check the format is A1:A20, in the related sheet module, paste the following code;

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim regEx As New RegExp
    MyPattern = "([a-zA-Z])([0-9]{7})([a-zA-Z])([0-9]{4})$"
    regEx.Pattern = MyPattern
        If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
            If regEx.test(Target.Text) Then
                Exit Sub
            Else
                MsgBox "Oppsss...!"
                Target.Select
            End If
    End If
End Sub

If a user enters a text different then the specified format, he/she will be prompted and the related cell will be selected again.
 
Upvote 0
Try the following formula for your custom DV:

=AND(LEN(A1)=13,CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,ISNUMBER(--MID(A1,2,7)),CODE(MID(A1,9,1))>64,CODE(MID(A1,9,1))<91,ISNUMBER(--RIGHT(A1,4)))
 
Upvote 0
Tetra201 said:
Try the following formula for your custom DV:

=AND(LEN(A1)=13,CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,ISNUMBER(--MID(A1,2,7)),CODE(MID(A1,9,1))>64,CODE(MID(A1,9,1))<91,ISNUMBER(--RIGHT(A1,4)))
Click to expand...
Your formula is not robust enough; it reports TRUE for things like this...

N2,187E2ANOV5
 
Upvote 0
Haluk said:
In your VBA editor, from Tools >> References menu find Microsoft VBScript Editor 5.5 and select it and close the window.

Assuming the range of cells that you are trying to check the format is A1:A20, in the related sheet module, paste the following code;

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim regEx As New RegExp
    MyPattern = "([a-zA-Z])([0-9]{7})([a-zA-Z])([0-9]{4})$"
    regEx.Pattern = MyPattern
        If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
            If regEx.test(Target.Text) Then
                Exit Sub
            Else
                MsgBox "Oppsss...!"
                Target.Select
            End If
    End If
End Sub

If a user enters a text different then the specified format, he/she will be prompted and the related cell will be selected again.
Click to expand...
This can be done without using Regular Expressions just as easily...
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
    For Each Cell In Intersect(Target, Range("A1:A20"))
      If Not Cell.Value Like "[A-Za-z]#######[A-Za-z]####" Then
        MsgBox "Oppsss...!"
        Target.Select
      End If
    Next
  End If
End Sub



Ebreezy757 said:
1. First character will always be a letter (text)
2. The next seven characters will always be numbers
3. The ninth character will always be a letter (text)
4. The last four digits will always be numbers.
5. I would like the letters to be remain capital letters

An example would be: N0018712A2345
Click to expand...
Give this formula a try...

=AND(IF(MID("A0000000A0000",{1,2,3,4,5,6,7,8,9,10,11,12,13},1)="A",(MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13},1)>="A")*(MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13},1))))
 
Last edited:
Upvote 0
Rick Rothstein said:
Your formula is not robust enough; it reports TRUE for things like this...

N2,187E2ANOV5
Click to expand...
Thank you for pointing that out.

Here is an updated formula for custom DV:

=AND(CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,CODE(MID(A1,9,1))>64,CODE(MID(A1,9,1))<91,ISNUMBER(--(MID(A1,2,7)&RIGHT(A1,4))),(MID(A1,2,7)&RIGHT(A1,4))=TEXT(--(MID(A1,2,7)&RIGHT(A1,4)),"00000000000"))
 
Upvote 0
Rick Rothstein said:
Give this formula a try...

=AND(IF(MID("A0000000A0000",{1,2,3,4,5,6,7,8,9,10,11,12,13},1)="A",(MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13},1)>="A")*(MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13},1))))
Click to expand...
I need to point out that the above formula is only for use in a cell on the worksheet... it cannot be used as the Custom Formula in a Data Validation rule. If you want to use a formula with Data Validation, then use the one Tetra201 posted is modified by me below.



Tetra201 said:
Thank you for pointing that out.

Here is an updated formula for custom DV:

=AND(CODE(LEFT(A1))>64,CODE(LEFT(A1))<91,CODE(MID(A1,9,1))>64,CODE(MID(A1,9,1))<91,ISNUMBER(--(MID(A1,2,7)&RIGHT(A1,4))),(MID(A1,2,7)&RIGHT(A1,4))=TEXT(--(MID(A1,2,7)&RIGHT(A1,4)),"00000000000"),LEN(A1)=13)
Click to expand...
That seems to be pretty robust now with one minor exception (see addition in red above).
 
Upvote 0
Tetra201 said:
@Rick Rothstein:

In my (limited) testing the DV formula from Post #6 worked without the LEN(A1)=13 condition -- that's why i've removed it.
Click to expand...
You need the length test otherwise your formula will return TRUE for values like this...

N1234567A1234567890
 
Upvote 0
Haluk,

Thank you very much. The code worked great after following your instructions. However, I had one issue. It worked initially, but not after I saved the file and went back to it for some strange reason. At Excel's direction (msgbox that appeared), I saved the file as a macro-enabled workbook but it still wouldn't work after I closed the file and reopened it. Nevertheless, I really appreciate you taking the time to reply.

-EB757
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Bold specific alphanumeric text...
Replies
3
Views
241
Dan10
D
S
  • Question
IF statement if not in a specific format
Replies
1
Views
262
Peter_SSs
Peter_SSs
S
  • Question
Extracting text within varying length phrases
Replies
8
Views
866
Peter_SSs
Peter_SSs
M
  • Solved
How to sum a row of data with some cells containing an extra character
2
Replies
10
Views
668
Fluff
Fluff
D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
591
diversification
D

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top