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

office 365

SergeiBaklan
Jan 17, 2022
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.

9 Replies

SergeiBaklan
Diamond Contributor
Jan 17, 2022
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.
- bigjim1216
  Copper Contributor
  Jan 17, 2022
  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
  - SergeiBaklan
    Diamond Contributor
    Jan 17, 2022
    bigjim1216 , you are welcome
Patrice Brel
Copper Contributor
Jan 17, 2022
Good morning, you probably could use the NA() ( showing #N/A ) to replace ""
=IF(A2="",NA(),B2)
Regards
- bigjim1216
  Copper Contributor
  Jan 17, 2022
  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
Jihad Al-Jarady
MCT
Jan 17, 2022
Hello,

Blank and empty is a different situation for excel cell.
in your case just use this formula: if(a2="",trim(""),b2)
- bigjim1216
  Copper Contributor
  Jan 17, 2022
  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
alannavarro
Iron Contributor
Jan 17, 2022
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<>""))
- bigjim1216
  Copper Contributor
  Jan 17, 2022
  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

Forum Discussion

Formula for deleting the contents of a cell

9 Replies

Resources