Some formulas showing up as text

[tharpa]

I have a large spreadsheet, which may or may not be part of the problem. I have a formula in one cell, P33, as follows

=CONCAT(G$6, J33," ", K33, " ", L33, " ",M33)

If I just copy and paste it to another cell, P34, it displays as I would expect. However, if I make any change to it, such as changing it to

=CONCAT(G$7, J33," ", K33, " ", L33, " ",M33)

(Note that all I did there was change the reference to a different cell) it displays as if it is plain text and not a formula.

Any idea what is going on?

[erAck]

Check that you didn’t accidentally set the cell’s number format to Text, which when entering a formula to that cell creates text content instead of a formula.

[tharpa]

Now I remember why I changed it to Text in the first place. I am concatenating a string, and it is showing a 0 at a point within the string, even though there is no zero in the source cells. It seems to be doing this where there is a missing value. How do I prevent it from inserting a 0 within a concatenated string for a missing value when there is no 0 in any of the source cells?

[erAck]

Quite certainly there is a 0 in the source cell, either as value or as formula result, otherwise the “stringified” value wouldn’t be 0. Maybe it is just not displayed because of a number format or a view option. A missing (empty cell) value does not produce a 0 but an empty string instead. However, you can suppress individual 0 by IF(J33;J33;"") for example.