如何用Excel公式检查序列值是否在ABCD0001-ZZZZ9999范围内？

核心思路 #

序列是4位大写字母+4位数字的组合，本质可拆解为「字母段的26进制数值」与「数字段的10进制数值」的组合。通过将字母段转换为十进制数，结合数字段生成唯一的整体数值，再与范围边界的对应数值比较，即可完成验证，无需VBA。

步骤拆解与公式实现 #

1. 格式合法性校验 #

先确保输入符合「4大写字母+4数字」的结构：

• 长度必须为8位
• 前4位是大写字母（区分大小写用EXACT，不区分可简化校验）
• 后4位是数字

2. 字母段转十进制数值 #

把4位字母看作26进制数（A=1，B=2，…，Z=26），计算其十进制值：

(CODE(MID(A1,1,1))-64)*26^3 + (CODE(MID(A1,2,1))-64)*26^2 + (CODE(MID(A1,3,1))-64)*26 + (CODE(MID(A1,4,1))-64)

• CODE(MID(A1,n,1))-64：将字母转换为1-26的数值（A的ASCII码是65，减64得1）
• 位权从左到右为26^3、26^2、26^1、26^0，对应4位字母的权重

3. 数字段转十进制数值 #

直接提取后4位转换为数字：

VALUE(RIGHT(A1,4))

4. 生成整体比较值并判断范围 #

将字母段数值乘以10000（匹配数字段的4位长度），加上数字段数值，得到可直接比较的整体值：

• 范围下限ABCD0001对应整体值：189900001
• 范围上限ZZZZ9999对应整体值：4752549999

完整公式 #

基础版本（兼容所有Excel版本）

=AND(
    LEN(A1)=8,
    ISNUMBER(VALUE(RIGHT(A1,4))),
    EXACT(LEFT(A1,4),UPPER(LEFT(A1,4))),
    ((CODE(MID(A1,1,1))-64)*26^3 + (CODE(MID(A1,2,1))-64)*26^2 + (CODE(MID(A1,3,1))-64)*26 + (CODE(MID(A1,4,1))-64))*10000 + VALUE(RIGHT(A1,4)) >= 189900001,
    ((CODE(MID(A1,1,1))-64)*26^3 + (CODE(MID(A1,2,1))-64)*26^2 + (CODE(MID(A1,3,1))-64)*26 + (CODE(MID(A1,4,1))-64))*10000 + VALUE(RIGHT(A1,4)) <= 4752549999
)

返回TRUE则输入在范围内，FALSE则不在。

简化版本（Excel 365/2021及以上，用LET减少重复计算）

=LET(
    input,A1,
    letterPart,LEFT(input,4),
    numPart,RIGHT(input,4),
    letterVal,(CODE(MID(letterPart,1,1))-64)*26^3 + (CODE(MID(letterPart,2,1))-64)*26^2 + (CODE(MID(letterPart,3,1))-64)*26 + (CODE(MID(letterPart,4,1))-64),
    numVal,VALUE(numPart),
    totalVal,letterVal*10000+numVal,
    lowerBound,189900001,
    upperBound,4752549999,
    AND(
        LEN(input)=8,
        ISNUMBER(numVal),
        EXACT(letterPart,UPPER(letterPart)),
        totalVal>=lowerBound,
        totalVal<=upperBound
    )
)

注意事项 #

• 若允许小写字母，可去掉EXACT校验，直接用UPPER(letterPart)参与计算
• 若输入包含非字母/数字字符，公式会直接返回FALSE，无需额外处理

内容的提问来源于stack exchange，提问作者Dingo