Exact Format Validation in either Data Validation or VBA

[RegularExcelUser]

Hi there,
I've read a quite a few different solutions for this on the internet but can't really get any to work, so figured I'd come here. I'm agnostic as to whether the solution is a formula in the Custom field of Data Validation, or in VBA, although I have a slight preference for the former as I've a big chunk of VBA code already on the sheet so don't want to add more if I can avoid it.

The problem is as follows. I have a data entry sheet, and in cell H8, I only want users to be able to enter data in the exact format of a-aaa-aaaa-aa-00-0000, where a represents any letter, and 0 represents any number. In terms of entry, I'm not bothered if the letters are upper or lower case, or if the user types in the dashes, but I would like the final format to have lower case letters and the dashes in place, so will accept a solution that requires entry in the specific format (lower case with dashes) if it's easier.

 

[RegularExcelUser]

bump

 

[RegularExcelUser]

bump, really could do with some help on this.

 

[JvdV]

You mention you don't mind to validate the exact pattern. If you are using Excel 2013 or higher for Windows, you can try a formula using FILTERXML() as a custom formula validation rule. For example:

=NOT(ISERROR(FILTERXML("<t><s>"&LOWER(A1)&"</s></t>","//s[translate(substring(.,1,13),'abcdefghijklmnopqrstuvwxyz','')='---'][concat(substring(.,2,1),substring(.,6,1),substring(.,11,1),substring(.,14,1),substring(.,17,1))='-----'][translate(substring(.,15),'-','')*0=0][string-length()=21]")))

• [translate(substring(.,1,13),'abcdefghijklmnopqrstuvwxyz','')='---'] - Assert that when all alpha-chars are substituted with empty string the remainder equals three hyphens;
• [concat(substring(.,2,1),substring(.,6,1),substring(.,11,1),substring(.,14,1),substring(.,17,1))='-----'] - When all characters at given indexes are concatenated assert that result equals 5 hyphens;
• [translate(substring(.,15),'-','')*0=0] - Assert that when hyphens in last part of string are removed the remainder equals 0 when multiplied by 0 (thus numeric);
• [string-length()=21] - Assert that input is 21 characters long in total.

 

[excel_learnerz]

You can achieve this using a combination of data validation and a formula. Here's a step-by-step guide to set this up:

1. Select cell H8.
2. Go to the "Data" tab on the ribbon, and click "Data Validation" > "Data Validation."
3. In the "Data Validation" dialog box, choose "Custom" in the "Allow" dropdown list.
4. In the "Formula" field, enter the following formula:
   =AND(LEN(H8)=19, ISNUMBER(--MID(H8,11,2)), ISNUMBER(--MID(H8,14,4)), CODE(MID(LOWER(H8),1,1))>=97, CODE(MID(LOWER(H8),1,1))<=122, CODE(MID(LOWER(H8),3,1))>=97, CODE(MID(LOWER(H8),3,1))<=122, CODE(MID(LOWER(H8),4,1))>=97, CODE(MID(LOWER(H8),4,1))<=122, CODE(MID(LOWER(H8),8,1))>=97, CODE(MID(LOWER(H8),8,1))<=122, CODE(MID(LOWER(H8),9,1))>=97, CODE(MID(LOWER(H8),9,1))<=122, MID(H8,2,1)="-", MID(H8,5,1)="-", MID(H8,10,1)="-", MID(H8,13,1)="-")
   This formula checks if the input in H8 is in the required format a-aaa-aaaa-aa-00-0000. It ensures that the dashes are in the correct positions, letters are in the specified positions, and numbers are in the specified positions.
5. Click "OK" to apply the data validation to cell H8.

Now, users will only be able to enter data in the required format in cell H8. If they enter data in an incorrect format, Excel will show a data validation error message.

To automatically convert the entered text to lowercase and add dashes if they are not entered by the user, you can use a Worksheet Change event in VBA:

1. Right-click the sheet tab where cell H8 is located, and choose "View Code."
2. In the Visual Basic for Applications (VBA) editor, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$8" Then Application.EnableEvents = False Target.Value = LCase(Target.Value) Target.Value = Application.WorksheetFunction.Substitute(Target.Value, "-", "") Target.Value = Mid(Target.Value, 1, 1) & "-" & Mid(Target.Value, 2, 3) & "-" & Mid(Target.Value, 5, 4) & "-" & Mid(Target.Value, 9, 2) & "-" & Mid(Target.Value, 11, 2) & "-" & Mid(Target.Value, 13, 4) Application.EnableEvents = True End If End Sub

1. Close the VBA editor.

Now, when users enter data in cell H8, the text will automatically be converted to lowercase, and the dashes will be added in the correct positions if they were not entered by the user. Note that this VBA code assumes that the user has entered the correct number of characters (14 characters in total: 10 letters and 4 numbers). The data validation will ensure that the input is in the correct format.

 

[Joe4]

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$8" Then Application.EnableEvents = False Target.Value = LCase(Target.Value) Target.Value = Application.WorksheetFunction.Substitute(Target.Value, "-", "") Target.Value = Mid(Target.Value, 1, 1) & "-" & Mid(Target.Value, 2, 3) & "-" & Mid(Target.Value, 5, 4) & "-" & Mid(Target.Value, 9, 2) & "-" & Mid(Target.Value, 11, 2) & "-" & Mid(Target.Value, 13, 4) Application.EnableEvents = True End If End Sub

Excel_learnerz,

I am not sure how you expect anyone to be able to read or use the VBA code in that format.
Please use Code Tags when posting your code, as described here: How to Post Your VBA Code

Thank you.

 

[RegularExcelUser]

You mention you don't mind to validate the exact pattern. If you are using Excel 2013 or higher for Windows, you can try a formula using FILTERXML() as a custom formula validation rule. For example:

=NOT(ISERROR(FILTERXML("<t><s>"&LOWER(A1)&"</s></t>","//s[translate(substring(.,1,13),'abcdefghijklmnopqrstuvwxyz','')='---'][concat(substring(.,2,1),substring(.,6,1),substring(.,11,1),substring(.,14,1),substring(.,17,1))='-----'][translate(substring(.,15),'-','')*0=0][string-length()=21]")))

• [translate(substring(.,1,13),'abcdefghijklmnopqrstuvwxyz','')='---'] - Assert that when all alpha-chars are substituted with empty string the remainder equals three hyphens;
• [concat(substring(.,2,1),substring(.,6,1),substring(.,11,1),substring(.,14,1),substring(.,17,1))='-----'] - When all characters at given indexes are concatenated assert that result equals 5 hyphens;
• [translate(substring(.,15),'-','')*0=0] - Assert that when hyphens in last part of string are removed the remainder equals 0 when multiplied by 0 (thus numeric);
• [string-length()=21] - Assert that input is 21 characters long in total.

JDV, thanks for taking the time to answer. That formula at 293 characters is too long for entry into a custom formula validation rule, which has a max length of 256 characters. As an aside, I have tried this formula in just an ordinary Excel cell, and it is still returning a FALSE value. I'm thinking it has to do with 2 things:
(a) In the concat(substring... element of the formula, I think the substring(.17,1) should actually be substring(.,16,1) if I understand this correctly e.g. it's calling out the point in the entry that the "- " should be placed.
(b) In the initial part [translate(substring.... element of the formula, I think that it is looking for an alpha character in every part of the entry e.g. even where the hyphens should appear, and thus is returning it as false.
Unfortunately, I don't have the time to dig into it further as it wouldn't be fit for purpose even if I did fix it, but appreciate the time you took to put this together in any case.

 

[RegularExcelUser]

You can achieve this using a combination of data validation and a formula. Here's a step-by-step guide to set this up:

1. Select cell H8.
2. Go to the "Data" tab on the ribbon, and click "Data Validation" > "Data Validation."
3. In the "Data Validation" dialog box, choose "Custom" in the "Allow" dropdown list.
4. In the "Formula" field, enter the following formula:
   =AND(LEN(H8)=19, ISNUMBER(--MID(H8,11,2)), ISNUMBER(--MID(H8,14,4)), CODE(MID(LOWER(H8),1,1))>=97, CODE(MID(LOWER(H8),1,1))<=122, CODE(MID(LOWER(H8),3,1))>=97, CODE(MID(LOWER(H8),3,1))<=122, CODE(MID(LOWER(H8),4,1))>=97, CODE(MID(LOWER(H8),4,1))<=122, CODE(MID(LOWER(H8),8,1))>=97, CODE(MID(LOWER(H8),8,1))<=122, CODE(MID(LOWER(H8),9,1))>=97, CODE(MID(LOWER(H8),9,1))<=122, MID(H8,2,1)="-", MID(H8,5,1)="-", MID(H8,10,1)="-", MID(H8,13,1)="-")
   This formula checks if the input in H8 is in the required format a-aaa-aaaa-aa-00-0000. It ensures that the dashes are in the correct positions, letters are in the specified positions, and numbers are in the specified positions.
5. Click "OK" to apply the data validation to cell H8.

Now, users will only be able to enter data in the required format in cell H8. If they enter data in an incorrect format, Excel will show a data validation error message.

To automatically convert the entered text to lowercase and add dashes if they are not entered by the user, you can use a Worksheet Change event in VBA:

1. Right-click the sheet tab where cell H8 is located, and choose "View Code."
2. In the Visual Basic for Applications (VBA) editor, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$8" Then Application.EnableEvents = False Target.Value = LCase(Target.Value) Target.Value = Application.WorksheetFunction.Substitute(Target.Value, "-", "") Target.Value = Mid(Target.Value, 1, 1) & "-" & Mid(Target.Value, 2, 3) & "-" & Mid(Target.Value, 5, 4) & "-" & Mid(Target.Value, 9, 2) & "-" & Mid(Target.Value, 11, 2) & "-" & Mid(Target.Value, 13, 4) Application.EnableEvents = True End If End Sub

1. Close the VBA editor.

Now, when users enter data in cell H8, the text will automatically be converted to lowercase, and the dashes will be added in the correct positions if they were not entered by the user. Note that this VBA code assumes that the user has entered the correct number of characters (14 characters in total: 10 letters and 4 numbers). The data validation will ensure that the input is in the correct format.

Excel_learnez, thanks for taking the time to reply. Aside from having the same issue as JDV, which is that the formula is too long for a custom format validation, I think your formula was also off in a couple of places. However, it did give me some food for thought, and I have fixed it up so that it now validates the format properly. At least now I have a functional formula, even if I can't enter it into the custom format validation. I'll probably enter the formula into a hidden cell on the worksheet, and then use a value=TRUE formula in the custom validation. For those who are interested, the corrected formula I finally got to work was:
=AND(LEN(H8)=21,ISNUMBER(--MID(H8,15,2)),ISNUMBER(--MID(H8,18,4)),CODE(MID(LOWER(H8),1,1))>=97,CODE(MID(LOWER(H8),1,1))<=122,CODE(MID(LOWER(H8),3,1))>=97,CODE(MID(LOWER(H8),3,1))<=122,CODE(MID(LOWER(H8),4,1))>=97,CODE(MID(LOWER(H8),4,1))<=122,CODE(MID(LOWER(H8),5,1))>=97,CODE(MID(LOWER(H8),5,1))<=122,CODE(MID(LOWER(H8),7,1))>=97,CODE(MID(LOWER(H8),7,1))<=122,CODE(MID(LOWER(H8),8,1))>=97,CODE(MID(LOWER(H8),8,1))<=122,CODE(MID(LOWER(H8),9,1))>=97,CODE(MID(LOWER(H8),9,1))<=122,CODE(MID(LOWER(H8),10,1))>=97,CODE(MID(LOWER(H8),10,1))<=122,CODE(MID(LOWER(H8),12,1))>=97,CODE(MID(LOWER(H8),12,1))<=122,CODE(MID(LOWER(H8),13,1))>=97,CODE(MID(LOWER(H8),13,1))<=122,MID(H8,2,1)="-",MID(H8,6,1)="-",MID(H8,11,1)="-",MID(H8,14,1)="-",MID(H8,17,1)="-")
With regard to your VBA code for inserting the "-"s, that worked fine. The only thing I didn't really like about it is that it does mean the cell is pre-populated with "-----" as the default. If there is a way to hide this, that would be good. However, it's not a biggie.

 

[Peter_SSs]

the formula is too long for a custom format validation

What about this in custom DV?

23 04 21.xlsm
	H
8	a-aaa-aaaa-aa-00-0000
9	f-lkj-wrty-xs-65-6985
DV

Cells with Data Validation
Cell	Allow	Criteria
H8:H9	Custom	=LET(s,SUBSTITUTE(H8,"-",""),c,CODE(MID(s,SEQUENCE(10),1)),AND(LEN(H8)=21,MIN(c)>96,MAX(c)<123,CONCAT(MID(H8,MID("0206111417",SEQUENCE(5,,,2),2),1))="-----",ISNUMBER(-MID(s,SEQUENCE(6,,11),1))))