1

I want to replace one value with another. For instance, if the result of an expression is 0, I want to replace it with an empty string. I don't want to repeat the expression, or use an intermediate cell.

For example, instead of this:

=IF(VLOOKUP(A1,B:C,2,FALSE)=0,"",VLOOKUP(A1,B:C,2,FALSE))

I would like something like this, if there were a "CASE" function that did what I want:

=CASE(VLOOKUP(A1,B:C,2,FALSE),0,"")

Is there a function or syntax that does this? Basically just like switch but without the last "default" parameter. I want the default to be the value being checked, without having to repeat the expression.

I suppose I could do this:

=regexreplace(vlookup(A1,B:C,2,FALSE),"^0$","")

...but that's a bit ugly and probably very inefficient.

Improve this question

1 Answer 1

Reset to default
1

Use this pattern to replace zeros with blanks:

=iferror( 1 / ( 1 / vlookup(A1, B:C, 2, false) ) )

Improve this answer
2
  • That replaces non-numerics with blanks as well, which is not what I want.
    – PhilHibbs
    Commented May 13, 2021 at 15:40
  • Please show what your data looks like. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there.
    – doubleunary
    Commented May 15, 2021 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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