How to copy blanks from one sheet to another?

[artfulrobot]

A cell in one workbook has a VLOOKUP formula that yields empty.

When I copy that cell and paste special into a second workbook (different file) I can’t get the blank to come through - it’s translated into a zero.

A zero and a blank mean completely different things in this dataset. I need a way to do this. For my purposes, a blank can be represented as a ZLS “”, because it’s going to get exported as CSV anyway.

Example sheet:

A1: =VLOOKUP("foo",B1:C2,2,0)
B1: foo

1. A1 is blank (because the vlookup
   returns blank cell C1).
2. Copy A1
3. Paste-special somewhere else,
   without formula. You get a zero

[mariosv]

Please try: with =ISBLANK(VLOOKUP("foo",B1:C2,2,0)) in both files, to verify if the cell obtained with VLOOKUP is empty on both.

[artfulrobot]

The ISBLANK(VLOOKUP()) returns TRUE, however ISBLANK(A1) (put this in another cell on the demo sheet) returns FALSE. TYPE(A1) return 8 which means formula. I think that this is a bug.

[mariosv]

Are you sure, that the difference is not in the cell format, or the option to hide zeroes?.
Menu/Tools/Options/LIbreOffice calc/View - Display - Zero values.

Please can you show a sample formula with the issue.

[artfulrobot]

I’ve edited the question, thanks for your interest.

[artfulrobot]

You can’t. According to that post, the architecture of LO is such that a cell that is empty is deleted and therefore cannot contain a formula - so a formula that retuns blank (like VLOOKUP does - you can test by wrapping it in ISBLANK) cannot store this value in the cell. Also see the LibreOffice wiki page on handling of blanks.

The only way around this is to either return a ZLS with "", or to return NA() which uses the special #N/A non-value. These things copy and paste OK.

This is going to make my spreadsheet twice as slow: I have literally thousands of VLOOKUPs and I’m now going to duplicate them all like this:

=IF(ISBLANK(VLOOKUP(...)), "", VLOOKUP(...))

:-/