30 Excel Functions in 30 Days: 21 – TYPE

Icon30DayYesterday, 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:

Type00

Here’s the list of results, and the data types:

Type01b

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.

http://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)

Type01a

However, if there is an apostrophe in front of the number, the TYPE function result would be 2 — Text.

Type01c

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.

Type02a

  • If text is entered in B3, the TYPE function returns 2, so the CHOOSE function returns the message “No text”.

Type02b

  • 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”.

Type02c

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

_____________

You may also like...

3 Responses

  1. Rahul says:

    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.

  2. John Hackwood says:

    Debra
    Wow really great article I hadn’t ever thought to use CHOOSE in this way.
    John

  1. February 5, 2013

    […] TYPE function was originally designed to show what a cell contained, such as text or a formula. It returns a […]

Leave a Reply

Your email address will not be published. Required fields are marked *