Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
1  
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

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.