Check if cell contains Non-Alpha characters in Excel

excel-2013
Chad Portman picture Chad Portman · Apr 24, 2015 · Viewed 66.3k times · Source

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.

Answer

Roobie Nuby picture Roobie Nuby · Sep 15, 2015

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.

  • 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.

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel. =IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","") This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1. All I'd like …

Read More
Clear contents and formatting of an Excel cell with a single command

If you want to clear the contents of a cell or range in Microsoft Excel, you can use .ClearContents. If you also want to clear the formatting, you can use .ClearFormats. Sheets("Test").Range("A1:C3").ClearContents Sheets("Test").Range("…

Read More