Буквенно-цифровая проверка в Microsoft Excel

Вы можете попробовать:

=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( - Давайте проверим две вещи:
  • AND( - Проверьте, выполняются ли несколько условий TRUE:
    • LEN(A1)>6 - Проверить, превышает ли строка 6 символов.
    • LEN(A1)<9 - Проверьте, содержит ли строка менее 9 символов.
    • ISNUMBER(RIGHT(A1,6)*1 - Проверьте, составляют ли 6 крайних правых символов числовое значение.
    • CODE(A1)>64,CODE(A1)<91 - Проверьте, относятся ли самые левые символы к классу [A-Z].
  • IF( - Проверьте следующее:
    • LEN(A1)=8 - Проверьте, действительно ли длина равна 8.
      • AND( - Если TRUE, то проверьте следующее:
        • CODE(MID(A1,2,1))>64,CODE(MID(A1,2,1))<91 - Проверить, учится ли второй персонаж в классе [A-Z].
      • 1 - Если длина не ложна, она все равно будет 7, поэтому мы возвращаем 1 (равное TRUE), чтобы не связываться с нашим родителем AND().

Вы можете применить это к своему пользовательскому правилу проверки в виде формулы, если хотите избежать ложных данных, или, как указано в комментариях к условному форматированию, если вы хотите иметь возможность отображать ложные данные после их ввода.

В качестве альтернативы, если у вас Excel 2019 или более поздней версии и вам нравится код-гольф, вы можете использовать:

=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))

Ваши условия не исключают строку типа A1234567 (1 заглавная буква, 7 цифр). В соответствии с вашими условиями и при условии, что ваша строка находится в ячейке A1, эта формула должна работать:

=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))

Это в основном функция AND, которая содержит:

• условие для проверки длины строки: OR(LEN(A1)=7,LEN(A1)=8)
• условие для проверки, являются ли первые 2 символа строки буквами (только первые или оба): 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)))
• условие для каждых последних 6 символов, чтобы проверить, являются ли они числовыми (пример относится к первому): IFERROR(MID(RIGHT(A1,6),1,1)*1,0)

Обновлено: Улучшения

Формулу можно улучшить следующим образом:

=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))

Это все еще функция AND. Это изменения:

• он содержит одно условие для проверки того, являются ли первые 2 символа заглавными (ранее было по 1 для каждого символа, который использовал функцию UNICODE):
• он содержит одно условие для последних 6 символов, чтобы проверить, являются ли они числовыми (ранее было 1 для каждого символа, который использовал функцию ЕСЛИОШИБКА): EXACT(LEFT(A1,2),UPPER(LEFT(A1,2)))

Обновлено: исправление

Чтобы исключить специальный символ, я отредактировал формулу:

=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))