Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting "true" blanks with functions
I'm having a problem inserting a blank in a cell using a function so that
another formula that refers to this blank reads it as a blank. For example, if A1 is a blank cell: Formula Result =ISBLANK(A1) TRUE =IF(ISBLANK(A1)," ",1) =ISBLANK(A3) FALSE In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell in A3, a reference to it in another formula shows that Excel does not think it is blank. How do you write a formula so that there is truly nothing in the cell as a result? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting "true" blanks with functions
" " is a space and not a blank. Try the below formula....
=IF(TRIM(A1)="","Blank","Not Blank") If this post helps click Yes --------------- Jacob Skaria "bearspa" wrote: I'm having a problem inserting a blank in a cell using a function so that another formula that refers to this blank reads it as a blank. For example, if A1 is a blank cell: Formula Result =ISBLANK(A1) TRUE =IF(ISBLANK(A1)," ",1) =ISBLANK(A3) FALSE In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell in A3, a reference to it in another formula shows that Excel does not think it is blank. How do you write a formula so that there is truly nothing in the cell as a result? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting "true" blanks with functions
A formula cannot return a true blank cell. You have written a space with
your " " output, so the cell is certainly neither blank nor empty. The best you can do is to change your =IF(ISBLANK(A1)," ",1) to =IF(ISBLANK(A1),"",1) or to =IF(A1="","",1) and then you will be writing an empty string. Change your =ISBLANK(A1) test to =A1="" -- David Biddulph "bearspa" wrote in message ... I'm having a problem inserting a blank in a cell using a function so that another formula that refers to this blank reads it as a blank. For example, if A1 is a blank cell: Formula Result =ISBLANK(A1) TRUE =IF(ISBLANK(A1)," ",1) =ISBLANK(A3) FALSE In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell in A3, a reference to it in another formula shows that Excel does not think it is blank. How do you write a formula so that there is truly nothing in the cell as a result? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting "true" blanks with functions
Don't use ISBLANK() to test a cell. ISBLANK() will return FALSE unless the
cell is truely empty. Even a nuill character is not empty. For example, in A1 enter: ="" copy A1 and paste/special/value into A2 leave A3 truely empty ISBLANK(A1) will return FALSE ISBLANK(A2) will return FALSE ISBLANK(A3) will return TRUE -- Gary''s Student - gsnu200860 "bearspa" wrote: I'm having a problem inserting a blank in a cell using a function so that another formula that refers to this blank reads it as a blank. For example, if A1 is a blank cell: Formula Result =ISBLANK(A1) TRUE =IF(ISBLANK(A1)," ",1) =ISBLANK(A3) FALSE In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell in A3, a reference to it in another formula shows that Excel does not think it is blank. How do you write a formula so that there is truly nothing in the cell as a result? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting "true" blanks with functions
Why not just test the cell's value?
=IF(A1="","A1 is blank","A1 has something in it") -- Rick (MVP - Excel) "bearspa" wrote in message ... I'm having a problem inserting a blank in a cell using a function so that another formula that refers to this blank reads it as a blank. For example, if A1 is a blank cell: Formula Result =ISBLANK(A1) TRUE =IF(ISBLANK(A1)," ",1) =ISBLANK(A3) FALSE In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell in A3, a reference to it in another formula shows that Excel does not think it is blank. How do you write a formula so that there is truly nothing in the cell as a result? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting "true" blanks with functions
Jacob, Gary, David and Rick:
I should have clarified that the formulae I'm showing in A2 and A3 are very simple ones. In the spreadsheet that I'm working on, they are fairly complex. All of you are right in that if they were as simple as the ones shown, the approaches that you suggested would work. I ended up using something along the lines of =IF(a3=" ",...,...), but wanted to know if there was some way of inserting a blank, but it does not seem feasible. Thanks for all your suggestions. "Rick Rothstein" wrote: Why not just test the cell's value? =IF(A1="","A1 is blank","A1 has something in it") -- Rick (MVP - Excel) "bearspa" wrote in message ... I'm having a problem inserting a blank in a cell using a function so that another formula that refers to this blank reads it as a blank. For example, if A1 is a blank cell: Formula Result =ISBLANK(A1) TRUE =IF(ISBLANK(A1)," ",1) =ISBLANK(A3) FALSE In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell in A3, a reference to it in another formula shows that Excel does not think it is blank. How do you write a formula so that there is truly nothing in the cell as a result? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Typing "true" excel 2007 change it to "TRUE" | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |