Excel customer data validation for chars and numbers-CodePudding

I am trying to validate a combination of char and numbers that looks like this XXXX0000000.

I have tried this formula; =OR((LEFT(B2,3)="XXXX",LEN(B2)=11),AND(LEFT(B2,3)="XXXX",LEN(B2)=11).

The error message I receive is as follows:

Formula in B1:

=IF(ISERROR(FILTERXML("<t><s>"&A1&"</s></t>","//s[translate(substring(.,1,4), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''][string-length()=11][substring(.,5)*0=0]")),"Invalid","Valid")

Where:

//s[translate(substring(.,1,4), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''] - Check if when we translate the first 4 characters to nothing this also equals empty string;
[string-length()=11] - Check that node is 11 characters long;
[substring(.,5)*0=0] - Check that substring from 5th position onwards equals zero when multiplied by zero.

Note: FILTERXML() is case sensitive and is currently checking for uppercase alpha-chars.

EDIT:

To use this in data-validation; Ditch the IF() since you don't need that in validation and nest the remainder in NOT():

=NOT(ISERROR(FILTERXML("<t><s>"&A1&"</s></t>","//s[translate(substring(.,1,4), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''][string-length()=11][substring(.,5)*0=0]")))

When you select your range, make sure the validation rule has the topleft cell in the reference.

CodePudding user response：

You have two formulas but no joining of them IE you have an OR formula =OR((LEFT(B2,3)="XXXX",LEN(B2)=11) Which will return a true/false answer

You also have an AND formula =AND(LEFT(B2,3)="XXXX",LEN(B2)=11) Which will return a true/false answer

when you work through it you are being given a return of: =True(or false), true (or false) That isn't a formula and is causing the error output

I think you want to use an IF statement to join them and get an output as desired: E.g. =If(OR(LEFT(B2,3)="XXXX",LEN(B2)=11),AND(LEFT(B2,3)="XXXX",LEN(B2)=11), DO SOEMTHING IF TRUE, DO SOMETHING IF FALSE)

CodePudding user response：

I suggest this:

OR(LEFT(B2,3)="XXX",LEN(B2)=11,AND(LEFT(B2,3)="XXX",LEN(B2)=11))

It corrects the number of characters error and sorts the logic.