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.,
but a cell cannot have a value and a formula to manipulate that value.
What you might want to do is set
=IF(AND(LEN(A3)=1, NOT(EXACT(UPPER(A3),LOWER(A3)))), 0, A3)
and then drag/fill that down to
- Check the length (
LEN) of the value that we’re looking at (cell
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
♥, case conversion will have no effect.
If it’s an upper case letter (e.g.,
LOWER(A3) will return its lower case equivalent (
q), and vice-versa.
Therefore, arguably, if
A3 contains a letter.
- Unfortunately for us in this case, Excel is generally case-insensitive; if you compare
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
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