I have only text in my calc document (to be exported to CSV).
When I click in a cell in a sheet and reference another cell from a different sheet, I get a 0 if that cell is empty and I don’t want that.
However, in no toolbar or menu can I find a way to format the cell as text. There’s currency, percentage, date, etc etc etc but that doesn’t help me.
How do I prevent empty cells from showing up as 0 when I reference them in a formula?
Villeroy is right about the T() of course, if you are in fact okay with only text.
.
If at some point you do need to do some chain calculations that might have a 0 value in the chain, though, and you cannot convert your “real” zeros to text, you can also format the cells with Format Code #;#;"". That will maintain the “real” numeric zero but suppress showing it. I checked, and a cell formatted that way will export as empty using the CSV export filter.
Use #.########;#.########;"" (or similar) if you have decimal fractions.
.
Also, it is worth mentioning that in the same dialog, Format>Cells>Numbers, the Category Box has Text at the bottom. You might have to scroll to see it. So there is a Text formatting option. However, it will not stop the problem of the 0.
=T(A1) returns the text in A1 or an empty string if A1 is emtpy or number.
Or
=""&B1
You can use the number format code
General;-General;;@
to supress 0 values. Note that after save and reload it becomes the semantically equivalent
[>0]General;[<0]-General;"";@
the former is just easier to input.