Discussion Re: Formula for deleting the contents of a cell in Excel
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063953#M130117
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.<BR /><BR />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.<BR /><BR />Thanks again,<BR />JimMon, 17 Jan 2022 21:24:22 GMTbigjim12162022-01-17T21:24:22ZFormula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063222#M130015
<P>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</P><P> A B C</P><TABLE border="0" width="192" cellspacing="0" cellpadding="0"><TBODY><TR><TD width="64" height="19"> </TD><TD width="64"> </TD><TD width="64"> </TD></TR><TR><TD height="19">Jim</TD><TD>Wisdom</TD><TD>Wisdom</TD></TR><TR><TD height="19"> </TD><TD>Barnum</TD><TD> </TD></TR><TR><TD height="19">Bob</TD><TD>Shaw</TD><TD>Shaw</TD></TR><TR><TD height="19">Mary</TD><TD>Franks</TD><TD>Franks</TD></TR><TR><TD height="19"> </TD><TD>Hurst</TD><TD> </TD></TR><TR><TD height="19"> </TD><TD>Lincoln</TD><TD> </TD></TR><TR><TD height="19"> </TD><TD>Mathews</TD><TD> </TD></TR><TR><TD height="19">Linda</TD><TD>Murnan</TD><TD>Murnan</TD></TR><TR><TD height="19"> </TD><TD>Ancer</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P> </P><P>Thanks</P><P> </P><P>Jim F</P>Mon, 17 Jan 2022 02:28:07 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063222#M130015bigjim12162022-01-17T02:28:07ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063240#M130016
Hello,<BR /><BR />You can add a filter formula.<BR /><BR />=(FILTER(C2:C10,C2:C10<>""))<BR /><BR />And you could include also the sort formula to have the array sorted.<BR />=SORT(FILTER(C2:C10,C2:C10<>""))<BR /><BR /><BR />Mon, 17 Jan 2022 03:57:28 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063240#M130016alannavarro2022-01-17T03:57:28ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063331#M130023
Hello,<BR /><BR />Blank and empty is a different situation for excel cell.<BR />in your case just use this formula: if(a2="",trim(""),b2)Mon, 17 Jan 2022 07:35:30 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063331#M130023Jihad Al-Jarady2022-01-17T07:35:30ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063379#M130028
<P>Good morning, you probably could use the NA() ( showing #N/A ) to replace ""<BR />=IF(A2="",NA(),B2)<BR />Regards</P>Mon, 17 Jan 2022 08:35:18 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063379#M130028Patrice Brel2022-01-17T08:35:18ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063389#M130030
<P><LI-USER uid="960979"></LI-USER> </P>
<P>As a comment. Excel sorts in such order</P>
<LI-CODE lang="basic">(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</LI-CODE>
<P>after that logical TRUE/FALSE, after them errors and blanks are always on the last place.</P>
<P>Since formulas in Excel can't return blank as a value, as workaround you may generate error as <LI-USER uid="59097"></LI-USER> suggested or text like "zzz". Using conditional formatting you may hide them from showing.</P>Mon, 17 Jan 2022 08:42:03 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063389#M130030Sergei Baklan2022-01-17T08:42:03ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063952#M130116
I couldn't get the trim("") to work, but it got me going down another track which I'm pretty sure will work.<BR />Thanks for you help. I really appreciate it.<BR /><BR />JimMon, 17 Jan 2022 21:21:44 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063952#M130116bigjim12162022-01-17T21:21:44ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063953#M130117
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.<BR /><BR />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.<BR /><BR />Thanks again,<BR />JimMon, 17 Jan 2022 21:24:22 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063953#M130117bigjim12162022-01-17T21:24:22ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063955#M130119
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.<BR /><BR />Thanks for helping me solve my problem<BR /><BR />JimMon, 17 Jan 2022 21:30:19 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063955#M130119bigjim12162022-01-17T21:30:19ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063957#M130121
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.<BR /><BR />Thanks again<BR /><BR />JimMon, 17 Jan 2022 21:33:09 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063957#M130121bigjim12162022-01-17T21:33:09ZRe: Formula for deleting the contents of a cell
https://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063979#M130125
<P><LI-USER uid="960979"></LI-USER> , you are welcome</P>Mon, 17 Jan 2022 22:00:10 GMThttps://techcommunity.microsoft.com/t5/excel/formula-for-deleting-the-contents-of-a-cell/m-p/3063979#M130125Sergei Baklan2022-01-17T22:00:10Z