检查单元格是否在Excel中包含非字母字符 [英] Check if cell contains Non-Alpha characters in Excel

问题描述

是否有非VBA方式检查Col B和Col C以查看它们是否包含非Alpha字符?只是要通过非Alpha进行澄清，我的意思是不属于字母的任何内容(不区分大小写).

Is there a Non-VBA way to check Col B and Col C to see if they contains any characters that are Non-Alpha? Just to clarify by Non-Alpha I mean anything not part of the alphabet(case insensitive).

Col B和Col C是名字和姓氏的列表.其中一些名称会因输入错误的数据而在其中包含符号或数字.我正在尝试查找所有需要修复的问题.因此，我需要找到包含 任何 而不是字母的内容.

Col B and Col C is a list of First and Last Names. Some of these names have symbols or numbers in them through bad data entry. I am trying to find all the ones that need to be fixed. So I need to find the ones that contain ANYTHING that is not a letter.

推荐答案

有一个怪异"但简单而通用的答案.

There is a "weird" but simple and generic answer.

=SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz"))

• 如果A1包含任何非字母字符，则此公式返回#VALUE!错误；如果A1仅包含字母，则该公式返回数字；如果A1为空白，则返回#REF!错误.

  • This formula returns #VALUE! error if A1 contains any non-letter characters, number if A1 contains only letters, or #REF! error if A1 is blank.

    您可以将此公式包含在ISNUMBER或ISERR中，以将其转换为TRUE/FALSE值.

    You can enclose this formula in an ISNUMBER or ISERR to convert this to a TRUE/FALSE value.

    将SEARCH替换为FIND以使其区分大小写.

    Replace the SEARCH with a FIND to make it case sensitive.

    您可以在"abc...xyz"字符串中放置任何字符.这样可以轻松测试字母数字或常见标点符号等.

    You can put any character in the "abc...xyz" string. This makes it easy to test of alphanumeric, or common punctuations, etc.

    "1:"&LEN(A1)表示将从第一个字母开始一直检查到最后一个字母.将其更改为"2:"&(LEN(A1)-1)不会检查首字母和尾字母.

    The "1:"&LEN(A1) means that starting from the first letter, all the way to the last letter will be checked. Changing that to "2:"&(LEN(A1)-1) will not check the first and last letters.

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