30 Excel Functions in 30 Days: 21 – TYPE
Yesterday, in the 30XL30D challenge, we got cell addresses with the ADDRESS function, and combined it with INDIRECT to get a cell's value.
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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
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
The TYPE function has the following syntax:
- value can be text, number, error, or any other value
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.
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.
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.
YouTube link: Identify Cell Contents with Excel TYPE Function