=ISNUMBER(cell) keeps keeps returning FALSE even though there is a number in the cell. This only happens when the number in the cell is the result of a formula. The formatting is set to number for all cells involved and there are no errors in the formula or formulas in the cells it references . If I delete the formula in the cell and retype the same number, =ISNUMBER returns TRUE. This happens with any cell that has a formula and and number result in it. No setting or format changes I have tried seem to help. Any ideas?
No setting or format changes I have tried seem to help
This statement seems to contain a common misunderstanding. Formatting does not change data type stored in a cell. If it is stored as text at some point in time, you need to perform an explicit type conversion - formatting cannot cure that. Example: If you format a cell as Text before you enter data, you’ll get Text-type values into your cell, even if you enter 12345.
Another problem might be that there a functions, which work with text strings and perform an implicit type conversion (e.g. REGEX(), CONCAT() …). What I try to say: You did not provide any of the formulas in use, thus nobody will be able to correctly analyze your specific issue.
If ISNUMBER() is returning FALSE for a cell reference against the user’s expectation, the most comon cause is that there isn’t a number, but a text looking like a number, you may call it a “numeric text”.
If =ISNUMBER(CellRef) returns FALSE, but =VALUE(CellRef) returns a value for the same cell, this was the case.
To avoid issues of that kind:
-1- Don’t use explicit horizontal alignment for cells. (The content/result type then gets obvious.)
-2- Look what code is set under >Format>Cells...>>Numbers. If there is a @ the content is text independent of how it looks.
-3- You may also enable >View>Value Highlighting (Crl+F8) which will color the displayed string depending on the type.
(I prefer -1- insead of using this setting, because it sometimes isn’t equally clear.)
Same issue, if I copy an integer into the cell LO sees it a text quite frustrating, changing the cell to number will loose the number.
would be nice if it was more intelligent and sees a number as a number
Thank you - this answer definitely solves the question.
What @mikekaganski are saying is that your answer is not an answer, so (prior to delete it) paste it as a comment to the question. Thanks.