Formula for deleting the contents of a cell

[bigjim1216]

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

[#2]

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

[#3]

Hello,

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

[#4]

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

[#5 · Accepted 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.

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

@bigjim1216 , you are welcome