-2

1- I'd like to use a validation rule for an input cell where the entry must be 7 or 8 alphanumeric characters long

2- at the start of the string Alphas used must be 1 or 2 characters and uppercase.

3- at the end of the string Numerics will always be 6 characters long.

4- The following type of entries are required to be validated FD456789 X256325 Z899666 DQ985421 FD000052

5-I have created a validation formula. it works fine except it cannot validate 2nd character as alphabate in the string. i used AP656569 and A5656569 for testing. it should allow only AP656569, but on the contrary it is allowing both strings.

Formula: =AND(OR(LEN(A3)=7,LEN(A3)=8),ISNUMBER(VALUE(RIGHT(A3,6))),IF(LEN(A3)=7,NOT(ISNUMBER(VALUE(LEFT(A3,1)))),ISTEXT(MID(A3,2,1))))

Muhammad Rehan
  • 13
  • 3
  • 1
    Conditional formatting and text functions such as left() and len() come to mind. But show some effort this is not a free coding service. However I have given you hints. – Solar Mike Dec 20 '20 at 06:21

2 Answers2

1

You may try:

=AND(AND(LEN(A1)>6,LEN(A1)<9,ISNUMBER(RIGHT(A1,6)*1),CODE(A1)>64,CODE(A1)<91),IF(LEN(A1)=8,AND(CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<91),1))
  • =AND( - Let's check two things:
    • AND( - Check if multiple conditions are TRUE:
      • LEN(A1)>6 - Check if string is over 6 char.
      • LEN(A1)<9 - Check if string in under 9 chars.
      • ISNUMBER(RIGHT(A1,6)*1 - Check if 6 rightmost characters make up a numeric value.
      • CODE(A1)>64,CODE(A1)<91 - Check if leftmost characters is in class [A-Z].
    • IF( - Check the following:
      • LEN(A1)=8 - Check if the lengths is actually 8.
        • AND( - If TRUE then check the following:
          • CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<91 - Check if 2nd char is in class [A-Z].
        • 1 - If the length is not false, it will still be 7, therefor we return a 1 (equal to TRUE), to not mess with our parent AND().

You can apply this to your custom validation rule as a formula if you want to avoid false data, or as mentioned in the comments to conditional formatting if you want to be able to show false data after it being entered.

Alternatively, if you have Excel 2019 or higher, and you like code-golf you could use:

=AND(ISNUMBER(RIGHT(A1,6)*1),CODE(A1)>64,CODE(A1)<91,SWITCH(LEN(A1),7,1,8,AND(CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<91),0))
JvdV
  • 70,606
  • 8
  • 39
  • 70
0

Your conditions do not exclude a string like A1234567 (1 capital letter, 7 digits). According to your conditions and assuming your string is in cell A1, this formula should work:

=AND(OR(LEN(A1)=7,LEN(A1)=8),OR(IFERROR(LEFT(A1,1)*1,0)=0,AND(IFERROR(LEFT(A1,1)*1,0)=0,IFERROR(LEFT(A1,2)*1,0)=0)),UNICODE(A1)=UNICODE(UPPER(A1)),UNICODE(MID(A1,2,1))=UNICODE(UPPER(MID(A1,2,1))),IFERROR(MID(RIGHT(A1,6),1,1)*1,0),IFERROR(MID(RIGHT(A1,6),2,1)*1,0),IFERROR(MID(RIGHT(A1,6),3,1)*1,0),IFERROR(MID(RIGHT(A1,6),4,1)*1,0),IFERROR(MID(RIGHT(A1,6),5,1)*1,0),IFERROR(MID(RIGHT(A1,6),6,1)*1,0))

It's basically an AND function that contains:

  • a condition to check for the lenght of the string: OR(LEN(A1)=7,LEN(A1)=8)
  • a condition to check if first 2 characters of the string are letters (only the first or both): OR(IFERROR(LEFT(A1,1)*1,0)=0,AND(IFERROR(LEFT(A1,1)*1,0)=0,IFERROR(LEFT(A1,2)*1,0)=0))
  • a condition to check if the first character is capital: UNICODE(A1)=UNICODE(UPPER(A1))
  • a condition to check if the second character is capital: UNICODE(MID(A1,2,1))=UNICODE(UPPER(MID(A1,2,1)))
  • a condition for each last 6 characters to check if they are numeric (example refers to the first one): IFERROR(MID(RIGHT(A1,6),1,1)*1,0)

EDIT: Improvements

The formula can be improved like this:

=AND(OR(LEN(A1)=7,LEN(A1)=8),OR(IFERROR(LEFT(A1,1)*1,0)=0,AND(IFERROR(LEFT(A1,1)*1,0)=0,IFERROR(LEFT(A1,2)*1,0)=0)),EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),ISNUMBER(RIGHT(A1,6)*1))

It's still an AND function. This the changes:

  • it contains a single condition to check if the first 2 characters are capital (previously there were 1 for each character that used the UNICODE function): EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))) [CREDIT: JvdV]
  • it contains a single condition for the last 6 characters to check if they are numeric (previously there were 1 for each character that used the IFERROR function): ISNUMBER(RIGHT(A1,6)*1)

EDIT: correction

In order to exclude special character, i've edited the formula:

=AND(OR(LEN(A1)=7,LEN(A1)=8),OR(AND(UNICODE(A1)>64,UNICODE(A1)<91,ISNUMBER(MID(A1,2,1)*1)),AND(UNICODE(A1)>64,UNICODE(A1)<91,UNICODE(MID(A1,2,1))>64,UNICODE(MID(A1,2,1))<91)),EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),ISNUMBER(RIGHT(A1,6)*1))
Evil Blue Monkey
  • 2,276
  • 1
  • 7
  • 11
  • I've checked again and indead this does *not* validate properly. It validates `:B123456` as `TRUE` and `FD000052` as `FALSE`. Both incorrect. Your improvent fixes that last case but still validates the incorrect leading characters outside of `[A-Z]`. – JvdV Dec 20 '20 at 12:27
  • Hm, i didn't consider the "special characters" as ":". My bad. I don't see why the FD000052 would not work. Did you placed a space at the end of the string? In that case, it's again a "special character" bug. Check if the JvdV solutions works while i change the formula. Guess JvdV UNICODE approach is the way. – Evil Blue Monkey Dec 20 '20 at 12:40
  • Just also note that both your versions validate `A5656569` which I guess is meant to fail, being over 6 digits. – JvdV Dec 20 '20 at 12:43
  • @JvdV ye, but that doesn't go against the questioner instructions and i've underlined it at the beginning of my answer. – Evil Blue Monkey Dec 20 '20 at 14:34
  • Op specifically mentioned in his question that A5656569 is **not** allowed. – JvdV Dec 20 '20 at 15:07
  • Ah, he added point 5 while i was already working apparetly. Knowing it i would have gone for an IF function too. I guess i'll just let him pick your solution. More elegant and actually working without further edit. Also i didn't notice the first comment (12:27:21Z) was yours. ^^ – Evil Blue Monkey Dec 20 '20 at 15:09
  • Validation rules through formulae are just tedious. Other apps may support regex which would be a breeze. Easy to make mistakes with all that nesting. – JvdV Dec 20 '20 at 15:28