Menu
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.
735
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.
Press Alt + F11 and create a new module. The AlphaNumeric function will return TRUE if all of the values in the string are alphanumeric. Otherwise, it will return FALSE.
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number. The Excel FIND function returns the position (as a number) of one text string inside another.
Select the range that you need to remove non-alphanumeric characters from, and click Kutools > Text > Remove Characters. 2. Then a Delete Characters dialog box will appear, only check Non-alphanumeric option, and click the Ok button. Now all of the non-alphanumeric characters have been deleted from the text strings.
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.
177
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
