1

I sometimes want a function that returns some value if the result is blank, and the result itself function otherwise.

Obviously =IF(FUNCTION()="","WAS BLANK",FUNCTION()) works, but I hate the duplication.

With functions returning numbers I came up with a hacky solution using iferror

=IFERROR(1/(1/FUNCTION()),"WAS ZERO")

This is really useful to me, but I'm wondering if there's something similar I can do for functions which return text.

I do this to avoid UDFs/helper columns/duplication

  • I already have an IFBLANK udf but it's nice to have an option without haveing to resort to that – Some_Guy May 10 '17 at 17:18
  • With your edit the only answer is No it is not possible with native functions, without the duplication and/or a helper column – Scott Craner May 10 '17 at 17:37
  • @ScottCraner Re-reading the question, I agree. Sounds like you've got an answer. – T.J.L. May 10 '17 at 18:06
  • What possible entries can FUNCTION() return? Without knowing, it's difficult to give a definite answer, though the fact that CODE returns an error when passed a null string ("") might lead you to try =IFERROR(CODE(FUNCTION()),"WAS BLANK"). – XOR LX May 10 '17 at 20:19
  • @XORLX That is a great tip, thanks. The specific application I was thinking of hear had a text lookup involved (so function would return a variable length string), but I have absolutely had 1 character returning functions where I wanted to do something unless it was blank before, so this is really useful. – Some_Guy May 12 '17 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.