How to replace cells containing individual letters with another value?

In Excel, how do I write a formula that looks at a range of cells (say, A3 to A16), checks whether any of them are each individually letters, and if they are, replaces those values with "0"?

Replace and substitute seem to work only if it is looking at other cells (if that makes sense).

-
That's right, a formula can only change the value of the cell in which it resides - you'd need VBA to do what you suggest if I understand correctly – barry houdini Jan 16 '13 at 22:36

Replace and substitute seem to work only if it is looking at other cells (if that makes sense).

Right.  Depending on semantics (i.e., how you look at it), formulas (and functions) don’t change anything.  They compute a value for the cell they are in.  This may depend on the value(s) in some other cell(s) (e.g., `A3:A16`), but a cell cannot have a value and a formula to manipulate that value.

What you might want to do is set `B3` to

``````=IF(AND(LEN(A3)=1, NOT(EXACT(UPPER(A3),LOWER(A3)))), 0, A3)
``````

and then drag/fill that down to `B16`.

Explanation:

• Check the length (`LEN`) of the value that we’re looking at (cell `A3`).  If it’s one (1), proceed with this test, otherwise, “fail” (more on that later).
• Convert the value in `A3` to upper case and to lower case.  If `A3` contains some non-alphabetic character such as `7`, `&`, or `♥`, case conversion will have no effect.  If it’s an upper case letter (e.g., `Q`), `LOWER(A3)` will return its lower case equivalent (`q`), and vice-versa.  Therefore, arguably, if `UPPER(A3)``LOWER(A3)`, `A3` contains a letter.
• Unfortunately for us in this case, Excel is generally case-insensitive; if you compare `Q` to `q`, Excel will say that they are equal.  So we use the `EXACT()` function to see whether they are exactly identical.
• Combining the above, if `A3` is one character and that character is a letter, it is what (I believe) you are looking for.  So then we evaluate `B3` to the next argument, namely `0`.  If it’s blank or more than one character or not a letter (i.e., the test failed), we display its value (i.e., the value of `A3`).

There’s a glitch in the above: if `A3` is blank, the length is zero and we get the last part of the formula –– the `A3` part –– but it will display as a `0`.  To fix that, do

``````=IF(ISBLANK(A3), "", IF(AND(LEN(A3)=1, NOT(EXACT(UPPER(A3),LOWER(A3)))), 0, A3))
``````

which says, if `A3` is blank, set `B3` to blank; otherwise, proceed with the formula as described above.

If you want to change the values in `A3:A16` permanently, just copy `B3:B16` and paste values into `A3:A16`.

-