TYPE Function Google Sheets

What Is TYPE Function In Google Sheets?

The TYPE function Google Sheets is a built-in Info function. It accepts a value and determines the data type of the cited value as a numerical code. Users can make use of the TYPE function Google Sheets in formulas whose logic depends on a specified value’s data type. Also, the function helps affirm the data types of the values a function accepts as the inputs and returns as the output.

For example, the source dataset shows a set of values.

We must find the specified values’ data types as numeric codes and display them in column B cells B2:B4.

Then, adhering to the meaning of the TYPE function Google Sheets explained earlier, we can apply the TYPE() in the target cells to secure the anticipated output.

The TYPE(), which works like the Excel TYPE function, in each of the column B cells takes the reference to the data in the corresponding column A cells as its input. It then determines the data type of the specified value as a number.

Let us check the cell B1 formula to understand its logic. The TYPE() accepts the cell reference to the currency value of $50 and returns the value’s type as 1, which is the code for the Number data type.

TYPE() Google Sheets Formula

The TYPE Google Sheets functionsyntax is as follows:

Where,

• value: The value for which we want the function to find the data type. The function input can be a value or a reference to the specific value. However, if the supplied argument value is an expression or a reference to the specific formula, the TYPE function Google Sheets returns the data type of the formula output.

Furthermore, the table below lists the TYPE() output, which the function will return based on the specified input argument value.

Also, the argument value is mandatory when using TYPE function Google Sheets.

How To Use TYPE Google Sheets Function?

We can implement the TYPE function in Google Sheetsin the following two ways:

1. Access the function from the ribbon.
2. Enter the function into the sheet manually.

#Method 1 – Access The Function From The Ribbon →

Select a cell for showing the function output → The Insert tab → The Function optionright arrow → The Info function group right arrow → The TYPE function.

The chosen function will get inserted in the selected cell, with the cursor inside the function brackets. We can now update the TYPE function Google Sheets argument within the brackets.

Further, clicking the ‘?’ symbol on the left of the function name will show the function syntax.

Next, clicking the down arrow in the syntax pane will display the meaning of the TYPE function Google Sheets explained with an example.

Finally, once we enter the mandatory TYPE function Google Sheets argument, press Enter to view the function return value.

#Method 2 – Enter The Function Into The Sheet Manually →

1. Select the cell where we want to show the result.
2. Type =TYPE( in the cell. [ Alternatively, type =T or =TY and click the function name TYPE from the listed options to choose the function.]
3. Update the argument value and close the brackets.
4. Press Enter to fetch the value the TYPE function Google Sheets returns.

Examples

Check out the following illustrations to learn the practical ways of using TYPE function Google Sheets.

Example #1

We have a list of input values in column A, and column B shows the actions we must perform using the column A values.

We should carry out the calculations using the appropriate formulas in column C and finally display the data types of the values the formulas return in the target cells as numeric codes.

Step 1: We need to find the data type code of the cell A2 value. So, select cell C2, enter the TYPE(), and press Enter.

=TYPE(A2)

[ Alternatively, choose cell C2 and Insert à Function à Info à TYPE.

The chosen cell will show the ‘=’ sign followed by the function name, TYPE, and brackets.

Since the cursor is within the brackets, we can update the value argument in the function.

Finally, pressing Enter will execute the function in the selected cell.]

The TYPE() takes the reference to the text value in cell A2 as the input. Since the input value is a text, the function output is the corresponding data type numeric code 2.

Step 2: We need to check if the cell A3 value is below 5 and find the outcome’s data type code. For that, we must select cell C3, enter the TYPE() containing the AND function, and press Enter.

=TYPE(AND(A3<5))

First, the AND(), which works like the Excel AND function, verifies whether the cell A3 value of 100 is less than 5. Since the condition is false, the function output is the Boolean value of FALSE. Next, the TYPE() accepts the AND() return value of FALSE as the input. Since the function input value is a Boolean logical value, its output is 4, which is the numeric code for the Logical Value data type.

Step 3: We must add a space and the text “Excel” to the cell A4 value and determine the output’s data type code. Thus, we must choose cell C4, enter the following TYPE() expression, and press Enter.

=TYPE(A4&” Excel”)

Firstly, the ‘&’ concatenate symbol combines the cell A4 text, Space character, and the specified text value of “Excel” to return the text value of “MS Excel”. Next, the TYPE() takes the formula return value of “MS Excel” as the input. Since the input is a text value, the function returns 2 as the input value’s data type numeric code.

Step 4: We must add the value of 50 to the cell A5 value and obtain the result’s data type code. So, choose cell C5, enter the following TYPE(), and press Enter.

=TYPE(A5+50)

The expression inside the TYPE function tries adding the value of 50 to the cell A5 value. Though the cell A5 value contains numbers, the ‘+’ in the term 5+10 makes it non-numerical. So, adding 50 to a non-numerical data returns the #VALUE! error value.

Next, the TYPE() accepts the expression’s return value, #VALUE! error value as the input. Since the function input value is an error value, it returns the corresponding data type code of 16 as the output.

Step 5: The requirement is to show the cell A6 array data type. For that, choose cell C6, enter the TYPE(), and press Enter.

=TYPE({0;1;1;2;3;5;8;13;21})

When we directly supply an array as input to the TYPE(), it returns the corresponding data type code of 64. However, assume cell A6 contained only the array value and we supplied the cell reference A6 as input to the TYPE(). Then, the function would consider the input value as text and return the corresponding data type code of 2.

Example #2

The following source dataset shows the fields of a form to be filled by an employee at a firm.

While the fields are in column A, the employee must update their data in column B.

Now, the requirement is to determine the data types of the values the employee has updated in column B for the respective fields and show the output in column C.

Step 1: Select cell C2, enter the following IF function containing the TYPE(), and press Enter.

=IF(TYPE(B2)=1,”Number Value”,IF(TYPE(B2)=2,”Text Value”,IF(TYPE(B2)=4,”Logical Value”,IF(TYPE(B2)=16,”Error Value”,”NA”))))

Step 2: Implement the formula in the remaining target cells using the fill handle.

Let us check the cell C6 formula to understand the working.

The IF() used in this example works with the same logic as the Excel IF function.

Firstly, the TYPE() in all the IF()s accepts the same input value, which is the reference to the value #VALUE! in cell B6. It then returns the value’s data type code of 16.

So, the outer-most IF() condition checks if the TYPE() output value of 16 is equal to the value of 1. Since the condition is false, the IF() returns the FALSE value, which is again an IF().

Next, this IF() condition checks if the TYPE() output value of 16 equals the value of 2. Here again, the condition is false. So, the IF(), provided as the FALSE value, executes, which checks if the TYPE() output value of 16 equals the value of 4. Since the condition is false, the last IF(), specified as the FALSE value, executes.

Since the condition in the last IF() holds, the function returns its TRUE value, which is the text “Error Value” as the output.

Example #3

The dataset below displays the monthly inventory levels in cartons and the prices per carton data.

We should find the total inventory cost per month in column D. However if there is an issue with the inventory level data, the target cell must show an appropriate comment.

Step 1: Select cell D2, enter the following CHOOSE function containing the MIN function and TYPE(), and press Enter.

=CHOOSE(MIN(TYPE(B2),3),B2*C2,”Inventory level (Cartons) is a text data.”,”Ensure the Inventory level (Cartons) is a number.”)

Step 2: Utilize the fill handle to update the formula in the ensuing target cells.

We shall check the cell D13 formula to learn the logic.

First, the TYPE() accepts the reference to the value “No Data” in cell B13 as input. Since the input is a text value, it returns the data type code of 2. Next, the MIN(), which is the equivalent of the Excel MIN function, determines the least values among the TYPE() output value of 2 and the cited value of 3. Since the TYPE() return value of 2 is smaller among the two numbers, the MIN() output is the value of 2.

Finally, the CHOOSE(), that works like the Excel CHOOSE function, accepts the MIN() return value of 2 as its first argument value, index. So, it returns the second choice, “Inventory level (Cartons) is a text data.”, from the list of choices supplied as argument values to the CHOOSE(), following the index argument value.

Important Things To Note

1. When we supply more than one argument value to the TYPE function Google Sheets, the function returns the #N/A! error value.
2. When the supplied argument value to the TYPE function in Google Sheets is a reference to an empty cell, the function returns the value of 1.
3. The TYPE function in Google Sheets cannot find if a cell or cell range contains a formula or not. It only determines the data type of value the chosen cell displays.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the TYPE Function Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is TYPE Function Google Sheets. We explain how to use the TYPE function in Google Sheets with examples and points to remember. You can learn more from the following articles. –

• IFERROR In Google Sheets
• XMATCH In Google Sheets
• Data Validation In Google Sheets