Google spreadsheet - show an empty cell in a cell reference after exporting as excel

0
votes

Consider a spreadsheet with a "source" spreadsheet and a "formatted" sheet The formatted sheet references data from a source sheet.

The ideal way for this to work is to use array formula

# formatted-sheet (formulas)
| title | description |
------------------------
| = ArrayFormula(source!A2:AA) | |
| | |
| | |

# source (raw data)
| title | description |
------------------------
| SomeTitle | long description |
| OtherTitle | Other description |
| emptyDesc | |

In google spreadsheet, the resulting formatted sheet displays like this :

# formatted-sheet (rendering formulas)
| title | description |
------------------------
| SomeTitle | long description |
| OtherTitle | Other description |
| emptyDesc | |

However after exporting to Excel format, the empty values are displayed as "0"

# formatted-sheet (rendering formulas)
| title | description |
------------------------
| SomeTitle | long description |
| OtherTitle | Other description |
| emptyDesc | 0 |
| 0 | 0 | # for every additional line captures by the array formula, zeroes everywhere

The fix I found for this, is to use an IF to check for empty string values

# formatted-sheet (formulas)
| title | description |
------------------------
| = ArrayFormula(IF(source!A2:AA = ""; ""; source!A2:AA)) | |
| | |

However the IF() formula is broken for cells that are longer than 255 characters, Is there a different workaround possible ? Foe example a way to rpevent the empty strings to appear as "0" after exporting to xlsx and opening with Microsoft Excel ? or to improve the formula ?

Here is the reference for a sample sheet.

2
excelgoogle-sheets

2 Answers

0
votes

Try using:

=ARRAYFORMULA(IF(NOT(ISBLANK(source!B2:B)),source!A2:AA,""))

it also hides the last "emptyDesc". If this is not your intention please let me know.

0
votes

I found out this trick from this question that is quite concise to convert everything to a text like format.

=ArrayFormula(source!A2:AA & "")

However it will most likely kill the formatting of numbers and interoperability, I have not made extensive tests regarding this, as it is fine for me in the current state