SOLVED

Formula for deleting the contents of a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-3063222%22%20slang%3D%22en-US%22%3EFormula%20for%20deleting%20the%20contents%20of%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3063222%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20function%20that%20I%20can%20use%20to%20clear%20the%20contents%20of%20a%20cell%3F%26nbsp%3B%20I'm%20using%20a%20formula%20in%20cell%20%22c2%22%26nbsp%3B%20if(a2%3D%22%22%2C%22%22%2Cb2).%26nbsp%3B%20Simple%20formula%20but%20the%20problem%20I%20have%20is%20if%20this%20statement%20returns%20a%20blank%20cell%20in%20col%20C%20then%20it%20just%20shows%20empty%2C%20but%20it's%20not.%26nbsp%3B%20For%20example%20if%20I%20use%20sort%20function%2C%20it%20treats%20this%20cell%20as%20having%20a%20blank%20in%20it%20instead%20of%20being%20empty%20which%20screws%20up%20the%20sort.%26nbsp%3B%20%26nbsp%3BI%20need%20a%20way%20that%20I%20can%20write%20that%20%22if%22%20statement%20which%20will%20leave%20cell%20%22c2%22%20as%20if%20I%20had%20selected%20it%20and%20hit%20the%20delete%20key.%26nbsp%3B%20My%20excel%20is%20in%20my%20Microsoft%20365%20family%20if%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22192%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EJim%3C%2FTD%3E%3CTD%3EWisdom%3C%2FTD%3E%3CTD%3EWisdom%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EBarnum%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EBob%3C%2FTD%3E%3CTD%3EShaw%3C%2FTD%3E%3CTD%3EShaw%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EMary%3C%2FTD%3E%3CTD%3EFranks%3C%2FTD%3E%3CTD%3EFranks%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EHurst%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ELincoln%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMathews%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ELinda%3C%2FTD%3E%3CTD%3EMurnan%3C%2FTD%3E%3CTD%3EMurnan%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAncer%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%20F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3063222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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

   
JimWisdomWisdom
 Barnum 
BobShawShaw
MaryFranksFranks
 Hurst 
 Lincoln 
 Mathews 
LindaMurnanMurnan
 Ancer 

 

 

Thanks

 

Jim F

9 Replies
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<>""))


Hello,

Blank and empty is a different situation for excel cell.
in your case just use this formula: if(a2="",trim(""),b2)

Good morning, you probably could use the NA() ( showing #N/A ) to replace ""
=IF(A2="",NA(),B2)
Regards

best response confirmed by bigjim1216 (Occasional Contributor)
Solution

@bigjim1216 

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 Z

after 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 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
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
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

Jim
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

@bigjim1216 , you are welcome