|
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. 
Also note that I have formatted the highlighted range (C2:E6 – as shown in grey below) as Number:  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.  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.   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. 
This simple trick helped me to solve a problem, hope it will help you too.
0 Comments
Leave a Reply. |
Carine HoughHi, 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. 
Categories
All
|
