可配置的Excel字符与长度限制数据验证公式需求

问题分析 #

你提供的原公式存在两个核心问题：

1. 使用SEARCH函数会识别通配符（如*），导致非法字符被误判为有效；
2. ROW(1:30)是固定行范围，复制到其他单元格时无法自动适配目标单元格的输入长度，且超过30字符的内容会漏判。

改进后的可配置公式 #

=AND(LEN(A1)<=10, SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_")))=LEN(A1))

参数配置说明 #

直接修改公式里的两个关键部分即可适配不同规则：

• 最大长度限制：把LEN(A1)<=10中的10替换为你需要的最大允许字符数；
• 许可字符集：把"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_"替换为你的合法字符集合（比如需要允许小写字母就追加abcdefghijklmnopqrstuvwxyz）。

公式原理拆解 #

1. LEN(A1)<=10：直接校验输入内容的长度不超过设定值；
2. ROW(INDIRECT("1:"&LEN(A1)))：动态生成从1到当前单元格字符长度的序列，复制到其他单元格时会自动适配目标单元格的引用（比如复制到B5，公式会自动调整为LEN(B5)和MID(B5,...)）；
3. FIND(MID(A1,ROW(...),1), "许可字符集")：MID逐个提取输入的每个字符，FIND（不识别通配符）检查字符是否在许可集内，避免*这类特殊字符被误判；
4. --ISNUMBER(...)：将布尔判断结果转为1（有效）或0（无效），SUMPRODUCT求和后与总字符长度对比，确保所有字符都合法。

使用步骤 #

1. 选中需要设置验证的单元格/行列；
2. 点击「数据」选项卡 → 「数据验证」→ 选择「允许」为「自定义」；
3. 粘贴上述公式并修改参数；
4. 可在「出错警告」中设置提示文本，明确录入规则。

测试验证 #

比如设置最大长度为5，许可字符集为"ABC123"：

• 输入AB123：验证通过；
• 输入A1*：触发验证错误（*不在许可集内）；
• 输入ABC123：触发验证错误（长度超过5）。

内容的提问来源于stack exchange，提问作者neo-ned