检查文本字符串是否在Excel中包含特殊字符 [英] Check if a text string contains special characters in excel

问题描述

正如已经提到的标题一样，我想检查某个文本字符串是否包含任何特殊字符.

As the title already mentioned, I want to check if a certain text string contains any special characters.

当前，我创建了一个包含允许字符的列(ASCII范围为32-122).我想要一个公式，如果该单元格仅包含允许的字符，则返回"ok"，如果该单元格包含任何不允许的字符，则返回"no ok".

Currently I have created a column with the characters which are allowed (ASCII range 32-122). And I would like a formula which returns 'ok', if the cell only contains characters which are allowed and 'not ok' if the cell contains any character that is not allowed.

但是，即使不使用for循环，这是否有可能?(因此没有vba)

However, Is this even possible without using a for loop? (thus without vba)

推荐答案

假定您的有效字符列表在 A1:A91 中，并且您希望对其进行测试的第一个条目是在 B1 中，在 C1 中输入以下公式:

Assuming that your list of valid characters is in A1:A91 and that the first entry for which you wish to perform the test is in B1, enter this formula in C1:

= IF(LEN(B1)= 0，"，REPT("not"，SUMPRODUCT(0+(ISNUMBER(MATCH(MID(B1，ROW(INDEX(B:B，1):INDEX(B:B，LEN(B1)))，1)，$ A $ 1:$ A $ 91,0)))))<> LEN(B1))&"ok")

向下复制以得到 B2 ， B3 等中的字符串类似的结果.

Copy down to give similar results for strings in B2, B3, etc.

实际上，我们可以在工作表中不使用显式列表的情况下进行操作，即:

We can in fact do without the use of an explicit list within the worksheet, viz:

= IF(LEN(B1)= 0，"，REPT("not"，SUMPRODUCT(0+(ABS(77-CODE(MID(B1，ROW(INDEX(B:B，1):INDEX(B:B，LEN(B1)))，1)))> 45)))&确定")

致谢

这篇关于检查文本字符串是否在Excel中包含特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！