Forum Discussion
Solved
Formula for deleting the contents of a cell
Is there a function that I can use to clear the contents of a cell? I'm using a formula in cell "c2" if(a2="","",b2). Simple formula but the problem I have is if this statement returns a blank cell in col C then it just shows empty, but it's not. For example if I use sort function, it treats this cell as having a blank in it instead of being empty which screws up the sort. I need a way that I can write that "if" statement which will leave cell "c2" as if I had selected it and hit the delete key. My excel is in my Microsoft 365 family if that helps
A B C
| Jim | Wisdom | Wisdom |
| Barnum | ||
| Bob | Shaw | Shaw |
| Mary | Franks | Franks |
| Hurst | ||
| Lincoln | ||
| Mathews | ||
| Linda | Murnan | Murnan |
| Ancer |
Thanks
Jim F
As a comment. Excel sorts in such order
(space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Zafter that logical TRUE/FALSE, after them errors and blanks are always on the last place.
Since formulas in Excel can't return blank as a value, as workaround you may generate error as Patrice Brel suggested or text like "zzz". Using conditional formatting you may hide them from showing.
9 Replies
As a comment. Excel sorts in such order
(space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Zafter that logical TRUE/FALSE, after them errors and blanks are always on the last place.
Since formulas in Excel can't return blank as a value, as workaround you may generate error as Patrice Brel suggested or text like "zzz". Using conditional formatting you may hide them from showing.
- I was able to do a work around using your suggestion with the zzz and then after the sort, getting rid of them. works good. I really appreciate the help. All of the answers helped. I had not worked with the trim suggestion or filters before, so now I'm working on learning that.
Thanks for helping me solve my problem
Jimbigjim1216 , you are welcome
Good morning, you probably could use the NA() ( showing #N/A ) to replace ""
=IF(A2="",NA(),B2)
Regards- I couldn't get the NA() to work but along with Sergei Baklan's reply it got me going the right way and I'm pretty sure I found a solution.
Thanks for your help. I really appreciate it. I have another issue I'd like to learn about, but I'll put it into another post.
Thanks again,
Jim
- Hello,
Blank and empty is a different situation for excel cell.
in your case just use this formula: if(a2="",trim(""),b2)- I couldn't get the trim("") to work, but it got me going down another track which I'm pretty sure will work.
Thanks for you help. I really appreciate it.
Jim
- Hello,
You can add a filter formula.
=(FILTER(C2:C10,C2:C10<>""))
And you could include also the sort formula to have the array sorted.
=SORT(FILTER(C2:C10,C2:C10<>""))- I couldn't get the filter idea to work. I haven't used this before so it was probably just my ignorance. But I really appreciate your help. I'm spending time now trying to learn more about filters because of your suggestion so it will really help me expand my knowledge.
Thanks again
Jim
