EP04. “Product Codes 限制产品代码格式”
🔒 登录后可标记已读- 用 Data Validation 搭配 AND、LEFT、LEN、RIGHT、ISNUMBER、VALUE 几个函数组合，限制某一栏只能输入符合特定格式的代码
- 示范格式要求：长度必须是 4 个字符、必须以字母 C 开头、后面 3 位必须是数字
- 前置知识：这几个文字处理函数各自的基本用法
- 学完能限制任何"固定格式"的编号栏位，不只限于本例的产品代码
重点内容
适用版本
桌面版通用（Excel 365 / 2021 / 2019 等）。
设置格式限制规则
- 选取范围 A2:A7
- Data 选项卡 → Data Tools 组 → Data Validation
- Allow 下拉菜单选择 Custom
- Formula 输入框填入：
=AND(LEFT(A2,1)="C",LEN(A2)=4,ISNUMBER(VALUE(RIGHT(A2,3))))
这个 AND 函数同时检查三个条件，全部满足才允许输入：
LEFT(A2,1)="C"：强制第一个字符必须是字母 CLEN(A2)=4：强制总长度必须是 4 个字符ISNUMBER(VALUE(RIGHT(A2,3)))：取最后 3 个字符转成数字，再判断是不是数字，强制以 3 位数字结尾
- 点击 OK 确认
- 选中 A3 单元格重新打开 Data Validation，确认公式已正确复制（引用会自动从 A2 变成 A3）
[截图：Data Validation 对话框，Allow 选 Custom、Formula 栏填入 AND/LEFT/LEN/RIGHT 组合公式的界面]
测试效果
输入一个不符合格式的产品代码（例如开头不是 C，或长度不是 4 位），Excel 弹出错误提示，拒绝这次输入。
函数速查
| 函数 | 语法 | 用途 | 例子 |
|---|---|---|---|
| LEFT | =LEFT(text, [num_chars]) | 取文字左边指定字符数 | =LEFT(A2,1) |
| RIGHT | =RIGHT(text, [num_chars]) | 取文字右边指定字符数 | =RIGHT(A2,3) |
| LEN | =LEN(text) | 计算文字长度 | =LEN(A2) |
| ISNUMBER | =ISNUMBER(value) | 判断是否为数字 | =ISNUMBER(123) → TRUE |
| VALUE | =VALUE(text) | 把文字形式的数字转成真正的数字 | =VALUE("123") → 123 |
| AND | =AND(logical1, [logical2], ...) | 多个条件同时成立才算真 | =AND(条件1,条件2,条件3) |
学完你会
- ✅ 会用 AND 把多个格式条件组合成一条 Data Validation 规则
- ✅ 会用 LEFT / RIGHT / LEN 检查字符串的开头、结尾、长度
- ✅ 会用 ISNUMBER + VALUE 判断一段文字是不是数字
常见错误
- 只判断了开头字母和长度，漏了 ISNUMBER(VALUE(...)) 这段，导致后 3 位输入字母也能通过验证
- LEFT/RIGHT 函数漏写第二个参数（字符数），导致只取了 1 个字符，跟预期的格式要求对不上
- 这条规则只限制"格式"，不会限制"重复"——如果还需要防止代码重复，要另外搭配 EP03 的 COUNTIF 写法，两者是不同的验证需求
Sources
Blog / Website：