TYPE function

PRO BASIC

The TYPE function in Jspreadsheet Formulas Pro is a useful tool for identifying the type of data you're working with. It gives back a number that corresponds to the type of value you input. For instance, if you input text, it will return 2, if you input a logical value (true or false), it will return 4, and so on. This function is especially handy if you're working with a large dataset and need to quickly categorize or sort the data types.

Documentation

Returns a number representing the data type of a value.

Syntax

TYPE(value)

Parameter	Description
`value`	Value - the value for which you want to return the data type. Can be a reference to a cell containing a value.

Behavior

The TYPE function is designed to return an integer which corresponds to the type of data in a specified cell. Here are some of the expected behaviors:

The function treats empty cells as zero (0).
Numbers in a cell will return 1.
It considers text in a cell as a string, and returns 2.
For boolean values, the function will return 4.
In case of error values, it will return 16.
For array or range of cells, it will return 64.

Common Errors

Error	Description
#VALUE!	The function returns this error when the cell reference is invalid.
#REF!	This error appears if the function refers to a cell that isn't valid, such as a cell that has been deleted.

Best practices

Always ensure that the reference cell is valid to avoid errors.

Use the TYPE function to quickly check the type of data in a cell especially when dealing with large datasets.

Remember that TYPE function treats empty cells as zero (0). If this is not your intended result, consider using data validation or other checks to ensure cells are not left empty.

The TYPE function can be used with IF statements to create more complex formulas based on the type of data in a cell.

Usage

A few examples using the TYPE function.

TYPE("hello") → 2 (text string)  
TYPE(42) → 1 (number)  
TYPE(TRUE) → 4 (Boolean value)  
TYPE(#REF!) → 16 (error value)  
TYPE({1,2,3}) → 64 (array)  
TYPE( ) → 1 (empty cell, treated as zero → number)

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NjMwM2Q0YWI2MmMwOGQ4YTVjN2UzZTkwZWM4ZTcwMmFlN2QzZjNiMDg5ZDMyNjBlMDc1OTJmZWFhZTkwN2M0MGNjM2U2N2E0YWVhZWQyNTRkMThmOWMxNzE4MGM5ZmVhM2NiZWI5ODQzZGJiZDE0ZGMyNjJiOTU5MGJjNjBhOTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Value",
        "Data Type",
        "Type Number"
    ],
    [
        "Hello World",
        "=TYPE(A2)",
        2
    ],
    [
        42.5,
        "=TYPE(A3)",
        1
    ],
    [
        true,
        "=TYPE(A4)",
        4
    ],
    [
        "2024-01-15",
        "=TYPE(A5)",
        2
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('NjMwM2Q0YWI2MmMwOGQ4YTVjN2UzZTkwZWM4ZTcwMmFlN2QzZjNiMDg5ZDMyNjBlMDc1OTJmZWFhZTkwN2M0MGNjM2U2N2E0YWVhZWQyNTRkMThmOWMxNzE4MGM5ZmVhM2NiZWI5ODQzZGJiZDE0ZGMyNjJiOTU5MGJjNjBhOTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Value",
        "Data Type",
        "Type Number"
    ],
    [
        "Hello World",
        "=TYPE(A2)",
        2
    ],
    [
        42.5,
        "=TYPE(A3)",
        1
    ],
    [
        true,
        "=TYPE(A4)",
        4
    ],
    [
        "2024-01-15",
        "=TYPE(A5)",
        2
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('NjMwM2Q0YWI2MmMwOGQ4YTVjN2UzZTkwZWM4ZTcwMmFlN2QzZjNiMDg5ZDMyNjBlMDc1OTJmZWFhZTkwN2M0MGNjM2U2N2E0YWVhZWQyNTRkMThmOWMxNzE4MGM5ZmVhM2NiZWI5ODQzZGJiZDE0ZGMyNjJiOTU5MGJjNjBhOTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Value",
        "Data Type",
        "Type Number"
    ],
    [
        "Hello World",
        "=TYPE(A2)",
        2
    ],
    [
        42.5,
        "=TYPE(A3)",
        1
    ],
    [
        true,
        "=TYPE(A4)",
        4
    ],
    [
        "2024-01-15",
        "=TYPE(A5)",
        2
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NjMwM2Q0YWI2MmMwOGQ4YTVjN2UzZTkwZWM4ZTcwMmFlN2QzZjNiMDg5ZDMyNjBlMDc1OTJmZWFhZTkwN2M0MGNjM2U2N2E0YWVhZWQyNTRkMThmOWMxNzE4MGM5ZmVhM2NiZWI5ODQzZGJiZDE0ZGMyNjJiOTU5MGJjNjBhOTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFEzTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Value",
        "Data Type",
        "Type Number"
    ],
    [
        "Hello World",
        "=TYPE(A2)",
        2
    ],
    [
        42.5,
        "=TYPE(A3)",
        1
    ],
    [
        true,
        "=TYPE(A4)",
        4
    ],
    [
        "2024-01-15",
        "=TYPE(A5)",
        2
    ]
]
            }]
        });
    }
}

Edit this page on GitHub