Forum Discussion
COUNTIF not working
Thank you Haytham Amairah. However, on my file, I do not have the "green triangles" and the cells are formatted under Format --> Cells --> as "Number" as you can see in the image attached. It is still not working...
To convert the numbers entered as Text into real numbers, select column A --> Data Tab --> Text to Columns --> Finish.
To check if a number in a cell is a real number (not entered as Text), in any blank cell type the formula =ISNUMBER(A4)
If the formula returns TRUE, that means 4 in A4 has been converted into the real number.
Thank you, this worked! MOST APPRECIATED! Do you know how to make a the default setting such that I can convert the format of a group of cells to "real" "Number" format without going through the Data Tab? It is very misleading, since my Format --> Cells method has worked in the past...
Thank you,
Sarah
sme527 Note that formatting does not change the contents of a cell. It just changes how it looks. If you have 00345 in a cell that was formatted as text before you keyed it in, it will be text. If you change the cell format to number, it will NOT change it to a number. It leaves it as text. You can press F2/Enter and you'll see it then change to 345, as numbers don't retain leading zeros.
This is always an issue when you have numbers you want as text or text as numbers. You have to actually change the values in the cell. F2/Enter will do it one by one, the Data Tab will convert numbers to text, as will functions in another cell =TEXT() or VALUES() to convert to text/values respectively, then copy|Paste Values back to your original range.
If you weren't seeing the green triangle warning you of this issue, make sure this box is checked in your Excel options.
Glad to hear that the problem has been solved through Text to Columns.
Please note that the data formats in the Home tab not always represent the real format in the cell, especially when you try to change the format from Text to another format.
Data stored in text format sometimes contains some hidden characters that require some special transforms to be removed, so even when you try to change the format of the numbers to Number, the numbers stick with the Text format.
I recommend you enable the error indicators in Excel as described in the link below to alert you when the numbers are stored as texts, so you can convert them easily as described in my first reply.
https://support.office.com/en-us/article/hide-error-values-and-error-indicators-761401bc-25de-4673-862a-b6f33a7f563f
Regards
You're welcome Sarah!
By default the format of the cells is General and it happens if you manually change the format of the cells to Text and then input the numbers in them and in that case they look like numbers but actually they are numbers as Text. If you input a number in a cell with General format, it will automatically aligned to the right which is a kind of visual clue that a number entered in the cell is not a real number if you haven't changed the alignment of the cell.
