Contents
Yesterday in the marathon 30 Excel functions in 30 days we got the cell address using the function ADDRESS (ADDRESS) and combined it together with INDIRECT (INDIRECT) to get the value of the cell.
On the 21th day of the marathon, we will study the function TYPE (TYPE OF). It determines the type of data in the cell by returning the corresponding number.
So, let’s study the theory and practice regarding the function TYPE (TYPE OF). If you have additional information or examples, please share them in the comments.
Function 21: TYPE
Function TYPE (TYPE) returns a number that specifies the data type.
Here is a list of values and corresponding data types:
How can the TYPE function be used?
Function TYPE (TYPE) can tell you what kind of data a cell contains. In turn, such logical functions as ISERROR (EOSHIBKA), ISTEXT (ETEXT) and the like also check the cell for a specific data type. If you just need to find out what is contained in the cell, then using the function TYPE (TYPE) you can:
- Get a number identifying the value in the cell.
- Make sure the cell contains a numeric value before doing the multiplication.
Syntax TYPE
Function TYPE (TYPE) has the following syntax:
TYPE(value)
ТИП(значение)
- value (value) – can be text, number, error, or any other value.
Traps TYPE
Unfortunately the function TYPE (TYPE) cannot determine if a cell contains a formula. It shows the type of cell content or result returned by the formula.
For some versions of Excel, Microsoft Help erroneously reported that the function TYPE (TYPE) returns a number 8if the cell contains a formula. This is not true! The error was later corrected.
Example 1: Get a number identifying the value in a cell
Function TYPE (TYPE) returns a number based on the data type, so you can use it to find out what is in the cell. For example, if you enter 123 in cell B3, the result of the formula is 1, i.e. number.
=TYPE(B3)
=ТИП(B3)
However, if you put an apostrophe in front of the number, the result of the function TYPE (TYPE) will 2, i.e. text.
Example 2: Checking if a cell contains a number before doing the multiplication
You can use the function TYPE (TYPE) together with CHOOSE (SELECT) to multiply valid numeric values, or display a specific message if another data type is entered.
=CHOOSE(MIN(TYPE(B3),3),B3*C3,"No text","Enter Qty")
=ВЫБОР(МИН(ТИП(B3);3);B3*C3;"No text";"Enter Qty")
- If a number is entered in cell B3, then the function TYPE (TYPE) will return a value 1and the function CHOOSE (CHOICE) product B3*C3.
- If text is entered in cell B3, then the function TYPE (TYPE) will return a value 2and the function CHOOSE (SELECT) “No text” message.
- If something else is entered in cell B3, then the value of the function TYPE (TYPE) will 4 or more. Function MIN (MIN) will return 3, CHOOSE (SELECT) message “Enter Qty”.
