Check if cell contains Non-Alpha characters in Excel
excel-2013
Solution
There is a "weird" but simple and generic answer.
=SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz"))
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.
You can enclose this formula in an `ISNUMBER` or `ISERR` to convert this to a TRUE/FALSE value.
Replace the `SEARCH` with a `FIND` to make it case sensitive. Using `FIND` will ensure wildcard characters "?~*" are identified as non-letter characters.
You can put any character in the `"abc...xyz"` string. This makes it easy to test of alphanumeric, or common punctuations, etc.
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.
Problem
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 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.