Jan 16 2022 06:28 PM
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
Jan 16 2022 07:57 PM
Jan 16 2022 11:35 PM
Jan 17 2022 12:26 AM - edited Jan 17 2022 12:35 AM
Good morning, you probably could use the NA() ( showing #N/A ) to replace ""
=IF(A2="",NA(),B2)
Regards
Jan 17 2022 12:42 AM
SolutionAs 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.
Jan 17 2022 01:21 PM
Jan 17 2022 01:24 PM
Jan 17 2022 01:30 PM
Jan 17 2022 01:33 PM
Jan 17 2022 12:42 AM
SolutionAs 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.