Excel replace na and 0 with empty: Difference between revisions

Excel replace na and 0 with empty (edit)

454 bytes added , 17 April 2015

+color

13,468

edits

@@ Line 1: / Line 1: @@
-replace #N/A and 0 with empty when vlookup
+replace #N/A and 0 with <span style="color: #98bf21">empty</span> when vlookup
-. replace 0 with empty
+. replace 0 with <span style="color: #98bf21">empty</span>
-<pre>
+* IF( logical_test,      <span style="color: #98bf21">[value_if_true]</span>,      [value_if_false] )
-=IF( logical_test,      [value_if_true],      [value_if_false] )
+* <span style="border-bottom-style: dotted;border-color: #999;">IF( <nowiki>VLOOKUP(A1,SHEET1!A:E,3,0) = 0</nowiki>,     <span style="color: #98bf21"><nowiki>""</nowiki></span>,      <nowiki>VLOOKUP(A1, SHEET1!A:E, 3, 0)</nowiki> )</span>
-=IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0,      "",      VLOOKUP(A1, SHEET1!A:E, 3, 0) )
-</pre>
-. replace #N/A with empty
+. replace #N/A with <span style="color: #98bf21">empty</span>
-<pre>
+* =IFNA (value,      <span style="color: #98bf21">value_if_na</span> )
-=IFNA (value,      value_if_na )
+* =IFNA( <span style="border-bottom-style: dotted;border-color: #999;">IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, <nowiki>""</nowiki>, VLOOKUP(A1, SHEET1!A:E, 3, 0))</span>,      <span style="color: #98bf21"><nowiki>""</nowiki></span>)
-=IFNA( IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, "", VLOOKUP(A1, SHEET1!A:E, 3, 0)),      "")
-</pre>