TYPE()

Definition. This function returns a number indicating the data type of the value argument.

value (required). The expression (a number, text, a formula without an equal sign, a logical value, an error value, a reference, or a name) that you want to check

Background. This function is related to the IS() functions, which return a logical value depending on the argument.

This function is often used together with the IF() function to pre-test the result of a calculation. The result returned by an IS() function can be used as the basis for conditional formats and validation rules.

To use this function, you should be familiar with the mappings listed in Table 11-7.

Table 11-7. Types Mapped to Numbers

Argument	Return Value
Number	`1`
Text	`2`
Logical value	`4`
Error value	`16`
Array	`64`

With the exception of the last type, the results can be produced with the ISNUMBER(), ISTEXT(), ISNONTEXT(), ISLOGICAL(), and ISERROR() functions. For example:

ISNUMBER(B12)

and

TYPE(B12) = 1

have the same logical value.

The ISBLANK(), ISNA(), and ISREF() functions don’t work with the TYPE() function, along with ISERR(), which does not recognize #N/A as an error.

Tip: Use TYPE() or IF() to test for a condition

There is no general rule for choosing between TYPE() and an IS() function to test for a condition (IF() or conditional formatting). If you use an IS() function, you have to nest after the IS() function when using it in an IF() function. If you use the TYPE() function, you nest after the type function. The same information is returned from

=IF(ISERROR(F26),"error",IF(ISNUMBER(F26),"number",IF(ISTEXT(F26),"text")))

and

=IF(TYPE(F26)=16,"error",IF(TYPE(F26)=1,"number",IF(TYPE(F26)=2,"text")))

You cannot use the TYPE() function to find out whether a cell contains a formula. If the cell contains a formula, the TYPE() function returns the type of the formula result.

You will not get the value 64 if the cell contains an array formula. To get 64, you have to specify a range as the TYPE() argument and press Ctrl+Shift+Enter.

Examples. The following examples illustrate how to use the TYPE() function.

Producing IS() Functions. You can use the TYPE() function in the examples for the IS() functions. The second condition, =ISERROR(B3), in the example shown earlier in Figure 11-2, is now

=(TYPE(B3)=16)

For the example in Figure 11-6 (also shown earlier), you can use

=IF(TYPE(M42)=2,,L42*M42)

instead of

=IF(ISTEXT(M42),,L42*M42)

And in the second example for the ISLOGICAL() function, the identification of the cells containing logical values with colors can be done with

=AND(TYPE(G63)=4,G63)

for the red cells and with

=AND(TYPE(G63)=4,NOT(G63))

for the green cells.

Creating a Custom Function. If you often use information from a value type for text, you can create a custom function. The following code checks whether the cell contains an error. If the cell doesn’t contain an error, it checks whether the cell value is TRUE or FALSE. If the cell contains a different value, it checks whether the value is a numeric value.

Function TypeInWords(myRange As Range) As String
    Dim var As Variant
    var = myRange.Value
    If Not IsEmpty(var) Then
        If IsError(var) Then
            TypeInWords = "Error"
        Else
            If var = True Or var = False And Not IsEmpty(var) Then
                TypeInWords = "Logical value"
            Else
                If IsNumeric(var) Then
                    TypeInWords = "Number"
                Else
                    TypeInWords = "Text"
                End If
            End If
        End If
    Else
        TypeInWords = ""
    End If
End Function

Tip

For VBA pros: Unfortunately, not all worksheet functions have a counterpart in the object model. Therefore, you sometimes have to enter formulas manually.

TYPE()

Tip: Use TYPE() or IF() to test for a condition

Tip

See Also