Yesterday, in the 30XL30D challenge, we got cell addresses with the ADDRESS function, and combined it with INDIRECT to get a cell’s value.
TYPE Function
For day 21 in the challenge, we’ll examine the TYPE function. It identifies the type of value in a cell, by returning a number.
So, let’s take a look at the TYPE information and examples, and if you have other tips or examples, please share them in the comments.
Function 21: TYPE
The TYPE function returns a number, that identifies a value’s type:
Here’s the list of results, and the data types:
How Could You Use TYPE?
The TYPE function can tell you what kind of value is in a cell, but the logical functions, like ISERROR, ISTEXT, etc., will also check for a specific data type.
However, if you just want to know what’s in a cell, the TYPE function can:
- Identify cell value type by number
- Test for Number type before multiplying
TYPE Syntax
The TYPE function has the following syntax:
- TYPE(value)
- value can be text, number, error, or any other value
TYPE Traps
Unfortunately, the TYPE function cannot identify cells that contain a formula. It only shows the type for a cell’s contents, or a formula’s result.
In a few versions of Excel, the Help files incorrectly reported that a formula would return 8 with the TYPE function. This MSKB article corrects that error.
https://support.microsoft.com/kb/119148
Example 1: Identify cell value type by number
The TYPE function returns number, based on a value’s type, so you can use it to see what’s in a cell. For example, if you type 123 in cell B3, the result of this formula is 1 — Number.
=TYPE(B3)
However, if there is an apostrophe in front of the number, the TYPE function result would be 2 — Text.
Example 2: Test for Number type before multiplying
You could use the TYPE function with CHOOSE, to multiply valid numbers, or show a message, if something else is entered.
=CHOOSE(MIN(TYPE(B3),3),B3*C3,”No text”,”Enter Qty”)
- If a number is entered in B3, the TYPE function returns 1, so the CHOOSE function returns the result of B3*C3.
- If text is entered in B3, the TYPE function returns 2, so the CHOOSE function returns the message “No text”.
- If anything else is entered in B3, the TYPE function returns a 4 or higher. The MIN function result will be 3, so the CHOOSE function returns the message “Enter Qty”.
Download the TYPE Function File
To see the formulas used in today’s examples, you can download the TYPE function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the TYPE Video
To see a demonstration of the examples in the TYPE function sample workbook, you can watch this short Excel video tutorial.
_____________
Very good and effective post. Thanks for posting such and interesting and effective post. All the 30 functions are very useful for better spreedsheet development. Keep on doing this service more and more. Post many useful articles in excel so that it will be helpful for excel learners.
Debra
Wow really great article I hadn’t ever thought to use CHOOSE in this way.
John