Simply Excel
  • Home
  • About
  • Blog
  • Challenges
  • Contact

ISNUMBER and ISTEXT functions

18/9/2016

0 Comments

 
Skills level: Intermediate

​I happened to come across the ISNUMBER and ISTEXT formula’s while looking for something else, and thought to myself, this is quite nifty… don’t know if I am ever going to use it, but cool formula. What the formula does is simply confirm whether the data contained in a cell is formatted as text (ISTEXT formula) or as a number (you guessed it, the ISNUMBER formula). Pretty cool, but why would I ever need to use it?
​Until the next day…
​
A colleague asked for my help with a simple formula that just would not calculate. It kept on giving a #VALUE! error. I immediately thought of checking the formatting of the cell which contains the formula - it was formatted as a number, so no problems there, and then I continued to check the formatting of all the cells used in the calculation… Also not finding any issues. All the cells seemed to be appropriately formatted as a number, but the #VALUE! error remained.
Then I remembered the ISNUMBER and the ISTEXT formula that I just learned about the day before, and decided to test it. And low and behold, one of the cells sneakily kept its formatting as text even though we changed it to number. 
So how does that happen?
​
Sometimes, when you receive an excel file from someone else via email, the settings on their computer might vary slightly to yours. They might use a comma in decimals, while your computer use full stops. It is also possible that when a report is exported, the figures download as text. The number may be preceded by an apostrophe which is not displayed in the cell itself, making it difficult to detect that it is actually formatted as text. When someone manually puts a space to separate 1000’s, the same thing happens. Or if you manually insert a currency symbol that is not the usual currency setting on your system, it keeps the value as text too. 
​In all of the above scenarios, simply changing the formatting to number, will not do the trick. And most of the time, it would appear like you are dealing with a set of numbers, but you will not be able to do anything with it before making some changes to it.
So what about the formula?

ISNUMBER
The formula checks the value of a cell and returns “True” or “False” to answer your question. If the value is a number, it will display “TRUE”, if it is not a number, it returns “FALSE”. Easy enough.
​
ISTEXT
This one checks for text contained in cells. If the cell contains text, it returns “TRUE”, if not it displays “FALSE”.
This simple test helped me to easily spot the culprit that was causing havoc in my colleague’s spreadsheet.
​I have created a few examples below to illustrate. 
Picture
  • Column C contains numbers that appear to be fine, but the #VALUE! errors in column E show that something must be wrong.
  • Column E contains a simple formula that multiplies the value in Cell C with the value in Cell D. (For Example: =C2*D2).
  • I have given a short description of how I have entered the numbers in Column B; in some cases deliberately causing the value not to be stored as a number.
  • Column D is simply needed for the calculation, to be able to illustrate how incorrectly formatted numbers can cause problems. 
  • Column F contains the ISNUMBER formula
  • Column G contains the ISTEXT formula
Also note that I have formatted the highlighted range (C2:E6 – as shown in grey below) as Number:
Picture
Example 1
This is a “normal” value; acceptable to Excel for calculations. My system uses a full stop in decimal numbers. The formula in Cell E2 therefore calculates correctly. The ISNUMBER formula returns “True” for Cell C2, confirming that it is a number and Excel recognises it as such. The ISTEXT formula returns “False”, confirming the same.
​
Formula to enter in Cell F2 & G2                                              Value returned
=ISNUMBER(C2)                                                                               TRUE
=ISTEXT(C2)                                                                                       FALSE
​Example 2
I have used a comma instead of a full stop. Excel does not recognize this figure as a number on my system. Calculation in Cell E2 returns a #VALUE! error because of this. You will have to change the comma to a full stop for the calculation to be performed and for the error to clear.
 
Formula to enter in Cell F3 & G3                                              Value returned
=ISNUMBER(C3)                                                                               FALSE
=ISTEXT(C3)                                                                                       TRUE
Example 3
I have used a space to separate 1000’s (instead of using the correct formatting to do so). The space causes the value not to be recognised as a number, but as text. Once again, it cannot be used in calculations. The space needs to be removed to be able to clear the #VALUE! error.
 
Formula to enter in Cell F4 & G4                                              Value returned
=ISNUMBER(C4)                                                                               FALSE
=ISTEXT(C4)                                                                                       TRUE
Example 4
The dollar sign that I have entered manually does not correspond to my default currency setting (set on South African Rand) and my Excel is not programmed to deal with a currency symbol other than “R” (in USA, or if your Excel is set to use the American Dollar symbol, this would not have created a problem). Excel therefore does not recognise this addition, and cannot use the figure in a calculation. The Dollar sign ($) needs to be removed, or changed to South African Rand (R) for Excel to be able to perform the calculation.
 
Formula to enter in Cell F5 & G5                                              Value returned
=ISNUMBER(C5)                                                                               FALSE
=ISTEXT(C5)                                                                                       TRUE
​Example 5
Lastly, I have entered an apostrophe (‘) before the number. The apostrophe does not appear in the cell itself, although when you select the cell, you will see the apostrophe in the formula bar. This makes it easy for you to miss a value that looks like a number, but is actually stored as text.
Picture
​In this example, you will notice that for some reason, the calculation works in this case (no error in Cell E6), even though the value in Cell C 6 is stored as text.
 
The ISNUMBER and ISTEXT formula’s also confirm that the value is stored as text.
 
Formula to enter in Cell F6 & G6                                              Value returned
=ISNUMBER(C6)                                                                               FALSE
=ISTEXT(C6)                                                                                       TRUE
​If you hover over the little exclamation mark in the yellow square next to this cell, it will actually tell you that the value is not stored as a number, and give you the option to convert it to a number as soon as you click on it.
Picture
Picture
​As soon as you have converted the value to number, the apostrophe disappears from the formula bar, and the ISNUMBER formula returns a “TRUE” value, and ISTEXT returns a “FALSE” value.
Picture
​
​This simple trick helped me to solve a problem, hope it will help you too.
0 Comments



Leave a Reply.

    Carine Hough

    Hi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog.

    Picture
    Picture

    Categories

    All
    1. Beginner
    2. Intermediate
    3. Advanced
    Charts
    Functions & Formulas
    Pivot Tables
    Power Query
    Tips & Techniques

Powered by Create your own unique website with customizable templates.
  • Home
  • About
  • Blog
  • Challenges
  • Contact