I am trying to create a formula that will reference a cell. If that cell is blank, I want it to bring back a value of "LTL Direct". If it is not blank (anything at all showing), I want it to bring back a value of "Cartage".

I've used if and if/or statements before, but for some reason I cannot make this bring back the values correctly.

Any help would be greatly appreciated. Thanks!


You can do this way:-

=IF(ISBLANK(A1),"LTL Direct","Cartage")

Still if you want to be more specific, do this way

=IF(OR(ISBLANK(A1),ISERROR(A1)),"LTL Direct","Cartage")
  • 1
    Just a heads up that there are some instances where a cell is "blank" but also not empty. A single apostrophe in a cell will not show anything in the cell (it's a "format as text" flag) but it will come out as FALSE with the ISBLANK() function. The same holds true for a formula in that cell which results in an empty string (""). In both cases, the cell looks empty but isn't. Since LEN() checks visible data in the cell, it would return properly according to the original question. – techturtle Apr 24 '12 at 17:50

Try this:

=if(len(A1)>0,"Cartage","LTL Direct")

This just checks if there are more than 0 characters in the referenced cell. If so, it would be "not blank" and follows your pattern accordingly.

  • I've tried all of the suggestions (some of which I've already tried), and I'm getting the same value back each time. It's not changing to "LTL Direct" when the referenced field is blank. – sarah vac Apr 24 '12 at 18:40
  • If they've all failed, then there is something else at work here. I would guess that the data is weird somehow, but I honestly couldn't tell you without looking at it. If it was data you pasted in, can you paste it in a different way? (As values, as text or unicode, with or without keeping formatting, etc.) If it's linked, I would check the source data too. You should probably click on some of the cells that are "blank" but not responding properly and see what's in the formula bar, in case it's a formula or text that might be hidden (white on white). – techturtle Apr 25 '12 at 14:31
  • thanks. It seems that if I go into referenced cell and hit the back space key, the formula works as expected. I had to use a formula to "trim" and then use that "trimmed" value instead. Thanks so much! – sarah vac Apr 25 '12 at 20:49
  • 1
    Cool beans! Sounds like you may have had a space or two in your blank fields, causing all our formulas to tell you it wasn't blank. – techturtle Apr 25 '12 at 21:42

In addition to techturtle's answer you can use this:

=IF(A1="", "LTL Direct", "Cartage")

This is more in line with the way you specified: If blank show LTL Direct. Otherwise show Cartage.

  • True, that does fit more what was actually asked. I guess I check the length in case there is some way that it could return no characters but not equal exactly "". – techturtle Apr 24 '12 at 17:55
  • I was able to get this to work (but only after changing the reference cell to text.) Any ideas on why this would be the case? – sarah vac Apr 24 '12 at 18:48
  • As techturle mentioned in another comment, maybe there were spaces. If you didn't want to go through the trouble of "trimming" each time, you could probably use a double if: =if(A1="","LTL Direct",if(A1=" ", "LTL Direct","Cartage")). This will check for both blanks and single spaces, returning LTL Direct if that's the case, otherwise Cartage. – rishimaharaj Apr 25 '12 at 23:30

Your Answer

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

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