LinkBack URL
About LinkBacks
Hi,
This might be a simple question. How do I leave a cell blank (really blank so that ISBLANK() returns true) not with empty space "".
For ex: formula in B1, =if(isblank(A1),blank B1, "notblank")
Thanks,
Bhuvana
formula to leave the cell blank
Hi,
This might be a simple question. How do I leave a cell blank (really blank so that ISBLANK() returns true) not with empty space "".
For ex: formula in B1, =if(isblank(A1),blank B1, "notblank")
Thanks,
Bhuvana
If a cell contains a formula blank "" then =ISBLANK(cell_ref) = FALSE.
It's better to use:
=IF(cell_ref="",...
Or:
=IF(cell_ref<>"",...
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Sorry,Tony. I quite did not get your answer. I want to blank a cell if source cell is blank. Like if A1 is blank then I want to make B1 blank. I used the following formula in B1,
=IF(ISBLANK(A1),A1,"1") where A1 is blank. I except the value in B1 to be blank (so that =isblank(B1) = true) but the result of the formula is 0 not blank. And if I change my formula to be
=IF(ISBLANK(I26)," ","1") then =isblank(B1) = false.
thanks,
Bhuvana
Not sure what you're wanting.
Maybe this...
=IF(A1="","",A1)
Tony, setting the value to "" is not actually making the cell blank. after this formula, if you use =isblank(B1) then the result will be FALSE which means the cell is not blank. (where cell B1=IF(A1="","",A1)
I noted in my other reply that if a cell contains a formula blank that =ISBLANK(cell_ref) = FALSE.Originally Posted by bhuvanashriram
Not sure what you're trying to do.
If a cell contains a formula that returns a formula blank "" then that cell is not EMPTY. It contains a formula that returns an empty TEXT string.
That's the closest thing we can get to a "blank" cell using a formula.
If you want an EMPTY cell then you'll have to use an event macro that deletes the cell formula.
There's a difference between an EMPTY cell and a blank cell!
Not helped by the fact that Excel's ISBLANK function tests for empty cell, not cells with null strings.
There's a difference between an EMPTY cell and a blank cell!
Entia non sunt multiplicanda sine necessitate
Exactly.Not helped by the fact that Excel's ISBLANK function tests for empty cell, not cells with null strings.There's a difference between an EMPTY cell and a blank cell!
I've commented on this before. Based on its characteristics, ISBLANK is misnamed. It should have been named ISEMPTY.
Yes, Buvan, we know that.
We are getting to the stage where we need to see a concrete example of what you have. I have attached a sheet.
A1 contains a value
B1 contains =IF(A1=2,4,"")
C1 contains =IF(A1=2,4,"")
is this the sort of thing you want. If not, please attach an Excel sheet that DOES show what you want.
Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Without using VBA, you cannot have a formula in a cell return an empty cell. Either there is a formula in a cell, a value in a cell, or nothing.
Last edited by 63falcondude; 09-19-2016 at 01:26 PM.
I have attached the sample file to explain what I want. Hope that helps.
Tony, yes you are getting my point now that there is a difference between blank and empty cell.
In my workbook, cell A1 is blank so the formula in cell D1(=isblank(A1)) returns true. I want to bring the content of cell A1 to B1. (if A1 is blank B1 should also be blank not empty). So if isblank(A1) is true then isblank(B1) should also be true.The formula I used in cell B1 is =IF(ISBLANK(A1),"","not blank"). Now if you see the formula in C1 which is to check if B1 is blank then it returns false. That means, A1 is not the same as B1. A1 is blank but B1 is not.
So I need a formula to copy contents of A1 to B1. If A1 is blank then B1 should also be blank so that isblank(A1) or isblank(B1) are true.
Hope I have made clear!!
Thanks,
Bhuvana
Last edited by bhuvanashriram; 09-20-2016 at 03:46 AM. Reason: added attachment
Hi,
At the risk of merely repeating what everyone else here has already said, that is not possible if B1 contains a formula.
Don
Please remember to mark your thread 'Solved' when appropriate.
ohhh!! now I get it!! Sorry Tony, I misunderstood your reply! and thanks xlnitwit! Although I am disappointed I cannot achieve this using formula now
Cheers,
Bhuvana
Exactly. So what we need to know is WHY you want ISBLANK() to show TRUE. What is the NEXT STEP? What is the information to be USED FOR. there will be a workaround.
I can explain why I want, sorry that I do not have work around at this moment other than making it empty ("").
I have two columns for cost of products, cost1 and cost2 which are the data pulled from SQL server.
And I created third column for cost3 which has to be set to cost 2 if cost1 is blank/0.
when we pull data from SQL database, if cost1 is NULL in database then excel shows that as blank.
The formula on cost3 is =if(isblank(cost1),cost2,cost1). This formula returns 0 when cost2 is also blank. I wanted to eliminate that 0 and make the cost3 same as cost2.
Example continued. Please check the formulas on C6 and C7,
Thanks,
Bhuvana
In C6, copied down:
=IFERROR(1/(1/IF(A6="",B6,A6)),"")
Glenn, I changed my formula to be =IF(ISBLANK(A7),IF(ISBLANK(B7),"",B7),A7), as the closest option is to make the cell "". In your formula, you are assigning value "" when there is error and the formula 1/0 will error out in my example.
Bhuvana
Did you actually enter the formula that I suggested on your sheet. It returns EXACTLY what you aked for.
Glenn, your formula returns "" and I understood what your formula does. But my objective for this thread is not to return "" but blank(actual blank cell). But I got to know that it is not possible where is formula on the cell even though it returns "". So the closest option is to return "" that can be done by many ways. One of the ways is your formula. I made it other way. Thanks for the suggestion! I did enter the formula you suggested and it makes the cell empty ("").
Bhuvana
I still don't understand why you are insisting that it should be BLANK rather than EMPTY. What practical difference does it make? But, so long as you are happy....
Glenn, practically I can work by comparing the cell value to "" and set the cell to "" in my formulas. But I do have other columns which again checks the value of cost3 if empty/blank. cost1,cost2 have the data pulled from sql server which is BLANK. But I cannot carry over the blank to other cells which has formulas on cost1/cost2. so ideally I can use isblank() on cost1/cost2 only. I am curious to use the same function on other cells (cos3) but I cannot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks