Find non alphanumeric characters in an Excel cell
I thought I’d post more things here, since one of my old posts has gained some attention. I had started stashing away tips in my Evernote account, so here’s one from my notebook in 2013:
=IF(OR(AND(CODE(A1)>=48,CODE(A1)<=57),AND(CODE(A1)>=65,CODE(A1)<=90),AND(CODE(A1)>=97,CODE(A1)<=122)),"","Not Alphanumeric")
This basically works by checking if the cell (A1) is strictly alphanumeric or not. If it's 100% alphanumeric, the cell will display nothing. If it is not 100% alphanumeric, the words "Not Alphanumeric" will display.
I want something like this but instead of displaying “Non Alphanumeric” I want the cell to change it’s background color like in Conditional Formatting. I can’t get this to work using the last option in the New Rule which is > use a formula to determine…
Hi Cleif,
After I got your comment I started playing around with this in Excel. It has been seven years since I’ve worked on something like this, and an important caveat I forgot to note is that this formula only checks the first character in a cell. So if you’re only checking values one character long, you’re good.
Anyway, this is how you do it with conditional formatting in Excel 2007, which is the version I have. In the top nav, click Home, then Conditional Formatting, then New Rule. Choose the option “Use a formula to determine which cells to format.”
In this example, you’ll have cell A1 selected and you would paste the following formula into the box where it says “Format values where this formula is true”: =IF(OR(AND(CODE(A1)>=48,CODE(A1)<=57),AND(CODE(A1)>=65,CODE(A1)<=90),AND(CODE(A1)>=97,CODE(A1)<=122)),FALSE,TRUE) Then click "Format", then the "Fill" tab, and then select the background color you want. Click OK. That should give you what you're looking for. If you're trying to check a whole string of characters, sorry this isn't more helpful for that kind of scenario.