Excel单元格自定义数据验证：如何添加子串长度限制

现有规则说明 #

你当前使用的公式已经覆盖了前两项验证规则：

• 文本总长度限制：LEN(A1)<=100 确保单元格内容总长度不超过100字符
• 输入项数限制：LEN(A1)-LEN(SUBSTITUTE(A1, ",", ""))<=10 通过统计逗号数量，限制最多输入11个项（10个逗号对应11项）

添加子串长度限制的完整公式 #

要实现每个逗号分隔的子串长度不超过N（比如示例中的N=6），可以将以下条件整合到原公式中，最终完整的验证公式为：

=AND(
    LEN(A1)<=100,
    LEN(A1)-LEN(SUBSTITUTE(A1, ",", ""))<=10,
    MAX(LEN(TRIM(MID(SUBSTITUTE(A1, ",", REPT(" ", 100)), (ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1, ",", ""))+1)))-1)*100+1, 100))))<=6
)

子串长度验证部分的工作原理 #

• SUBSTITUTE(A1, ",", REPT(" ", 100))：把单元格内的每个逗号替换为100个空格，确保每个子串之间有足够的间隔，方便后续提取
• MID(..., (ROW(...)-1)*100+1, 100)：根据项数生成序列，逐个提取每个子串（每个子串被限定在100字符的区间内）
• TRIM(...)：去除提取出的子串前后的空格，还原原始输入的子串内容
• LEN(...)：计算每个子串的实际长度
• MAX(...)：找出所有子串中的最长长度，判断是否小于等于设定的N值（示例中为6）

自定义调整说明 #

• 将公式中的6替换为你需要的子串最大长度N
• 如果需要限制单元格不能为空，可在AND函数中额外添加A1<>""条件

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