25

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.

Chad Portman
  • 1,134
  • 4
  • 12
  • 38

2 Answers2

66

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.

Community
  • 1
  • 1
Roobie Nuby
  • 1,379
  • 12
  • 19
  • 1
    This works really quite well. And your explanation of how it works is very much appreciated. I do have one further question. How would I allow it to accept spaces? The idea being that the cell value of "Chad" would be good, "Chad1" would not be good, and "O Neal" would be good. – Chad Portman Sep 17 '15 at 21:02
  • 4
    @ChadPortman Put a space in the `"abc...xyz"` string. as in `" abc...xyz"`. – Roobie Nuby Sep 19 '15 at 21:21
  • 1
    That was simple should have tried that. Thanks for the help. – Chad Portman Sep 21 '15 at 16:32
3

You can use 26 nested SUBSTITUTEs to remove all alphabetic characters from the text.

If anything is left over, the cell contains non-alpha characters.

And thanks to @RaGe for pointing out that you need to check for empty cells as well:

=AND(ISTEXT(A2),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A2),"a",),"b",),"c",),"d",),"e",),"f",),"g",),"h",),"i",),"j",),"k",),"l",),"m",),"n",),"o",),"p",),"q",),"r",),"s",),"t",),"u",),"v",),"w",),"x",),"y",),"z",) = "")

enter image description here

Rick Hitchcock
  • 35,202
  • 5
  • 48
  • 79
  • Just be careful that this returns TRUE for a empty cell as well – RaGe Apr 24 '15 at 20:16
  • Wow that is so strange that works. I feel like there has to be a more efficient way to do than 26 nested formulas but it does work. Thanks for that. I guess I will use this for now until I can find something more streamline. – Chad Portman Apr 24 '15 at 20:19
  • 1
    @nights, it wasn't until Roobie posted a much more elegant solution : ) – Rick Hitchcock Feb 20 '19 at 18:43