excelif-statementvstacknonblank

how to make a blank cell after copy-paste

After a conversion i get an excel file with multiple sheets.

i create a new sheet(1) and with vstack i stack the content from all sheets into the new sheet(1). all blank cells from the original multiple sheets become "0"s for a workaround, i use the formula

=if(vstack(..)=0,"",vstack(..))

i copy the columns from new sheet(1), where my data is into a new sheet(2) and paste only the values. (ctrl+c, ctrl+v, ctrl, v) the blank cells in new sheet(2) are false in isblank(cell) formula.

Any ideas why?

what i want to do next is to delete all blank cells

i tried this too: =FILTER(VSTACK(Sheet1:Sheet8!A1:O87),VSTACK(Sheet1:Sheet8!A1:O87)<>"") - #value error

Solution

  • When you enter a formula in excel there is not an option to have it return null or blank. This is a constant work around when you use excel. For your situation I recommend using an arbitrary set of characters then replacing those. Example:

    =if(vstack(..)=0,"zzz",vstack(..))

    Then when you paste, use the paste special of "Values". Next do a "Find and Replace" to find the "zzz" and replace with nothing.