发票的自定义数据校验公式

Question

我已经创建了一个公式，它限制了对单元格的输入，创建的公式如下：

=AND(FIND("-",F5,1)=3,1*LEFT(F5,FIND("-",F5,1)-1)<13,1*LEFT(F5,FIND("-",F5,1)-1)>=1,ISNUMBER(1*RIGHT(F5,LEN(F5)-FIND("-",F5,1))))

我的发票编号格式是："00-000“为基础，公式中不给出任何字母顺序的输入。我想要做的是使字母顺序输入到右边的部分(000)。

Answer 1

如果您的输入仅包含字母、数字和连字符，则此公式将为TRUE：

=(SUMPRODUCT(LEN(SUBSTITUTE(UPPER(F5),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","-","0","1","2","3","4","5","6","7","8","9"},"")))=36*LEN(F5))

它使用SUMPRODUCT查找输入的长度(UPPER(F5))，其中包含37个字符("A"-"Z":26 + "0"-"9"：10 + "-":1)，并将它们相加。如果字符串中的每个字符都被替换了一次(即，它们都在37个字符的列表中)，那么这个总长度将是原始字符串长度的 36 倍(每个字符在36次未替换的情况下每次都出现一次)

要添加/删除有效字符，请在{数组}中添加/删除它们，并向上或向下调整36，使其比您接受的字符数少1。