How to copy blanks from one sheet to another? [closed]

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
edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-22 17:47:15.339899

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.

( 2014-05-15 00:07:41 +0200 )edit

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.

( 2014-05-15 11:01:15 +0200 )edit

Sort by » oldest newest most voted

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(...))


:-/

more

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.

more