Ask Your Question

Isnumber always returns false

asked 2016-02-01 19:06:44 +0100

Michael Pragit gravatar image

=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?

edit retag flag offensive close merge delete


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 Textbefore 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.

Opaque gravatar imageOpaque ( 2020-06-09 15:29:07 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-06-09 14:25:14 +0100

Lupp gravatar image

updated 2020-06-09 15:05:08 +0100

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.)

edit flag offensive delete link more


And see the FAQ.

erAck gravatar imageerAck ( 2020-06-09 16:35:02 +0100 )edit

answered 2020-06-09 11:58:24 +0100

mschrama gravatar image

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

edit flag offensive delete link more



Thank you - this answer definitely solves the question.

Mike Kaganski gravatar imageMike Kaganski ( 2020-06-09 12:52:57 +0100 )edit


What @Mike Kaganski are saying is that your answer is not an answer, so (prior to delete it) paste it as a comment to the question. Thanks.

LeroyG gravatar imageLeroyG ( 2020-06-09 14:34:49 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-02-01 19:06:44 +0100

Seen: 354 times

Last updated: Jun 09 '20