Ask Your Question

Some formulas showing up as text [closed]

asked 2019-06-14 21:31:07 +0200

tharpa gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-06-17 13:25:56.187763

1 Answer

Sort by » oldest newest most voted

answered 2019-06-14 22:45:14 +0200

erAck gravatar image

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.

edit flag offensive delete link more


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?

tharpa gravatar imagetharpa ( 2019-06-15 22:32:14 +0200 )edit

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.

erAck gravatar imageerAck ( 2019-06-17 13:25:45 +0200 )edit

Question Tools

1 follower


Asked: 2019-06-14 21:31:07 +0200

Seen: 19 times

Last updated: Jun 14