NUMBERVALUE function

PRO

The NUMBERVALUE function in Jspreadsheet Formulas Pro is a useful tool that allows you to change text into a numerical value, no matter the regional settings. This means it can understand different formats, like the comma being used as a decimal point in some countries. It's particularly beneficial when dealing with data from various international sources, as it ensures consistency in numerical data. Overall, it's a simple way to efficiently convert and standardize text into numbers.

Documentation

Converts text to a number in a locale-independent way.

Syntax

NUMBERVALUE(text, [decimal_separator], [group_separator])

Parameter	Description
`text`	The text to be converted to a number.
`decimal_separator`	An optional argument that specifies the character used as the decimal separator.
`group_separator`	An optional argument that specifies the character used as the group separator, such as a comma or period.

Behavior

The NUMBERVALUE function is used to convert text to numbers. Here's how it handles different types of inputs:

Text: NUMBERVALUE will convert text that represents numbers into actual numbers. For instance, NUMBERVALUE("123.45") would return 123.45. If the text cannot be converted into a number, it will return an error.
Empty cells: If the function refers to an empty cell, it will return an error.
Booleans: Booleans are treated as numbers (TRUE = 1, FALSE = 0).
Errors: If the function refers to a cell containing an error, it will propagate that error.
Non-numeric text: If the function refers to a cell containing text that cannot be converted into numbers, it will return an error.

Common Errors

Error	Description
#VALUE!	This error occurs when the text or the format provided cannot be converted into a number. For example, `NUMBERVALUE("ABC")` would return a `#VALUE!` error because "ABC" is non-numeric and cannot be converted into a number.
#REF!	This error occurs when the function refers to a cell that is not valid. This can happen if a cell is deleted after the function has been set up to refer to it.

Best practices

Always ensure that the text you're trying to convert can be accurately represented as a number. If it can't, NUMBERVALUE will return an error.

You can use NUMBERVALUE to convert text numbers with decimal and group separators. For example, NUMBERVALUE("1,234.56") would return 1234.56.

Be mindful of the decimal separator and group separator that NUMBERVALUE uses. By default, it uses a period (.) as the decimal separator and a comma (,) as the group separator. If your text uses different separators, you need to specify them in the function.

Use NUMBERVALUE to convert text numbers in scientific notation. For example, NUMBERVALUE("1.23E+3") would return 1230.

Usage

A few examples using the NUMBERVALUE function.

NUMBERVALUE("1234.56")  
// Returns 1234.56  

NUMBERVALUE("1,234.56", ".", ",")  
// Returns 1234.56 (using dot as decimal, comma as grouping separator)  

NUMBERVALUE("1.234,56", ",", ".")  
// Returns 1234.56 (using comma as decimal, dot as grouping separator)  

NUMBERVALUE("1.23E+3")  
// Returns 1230 (supports scientific notation)

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('OTQyZDUzZGQ5ZTRkOTIxMjlhMjMyMGMzZjI0OTQzYjQ2NDkwODVjNGQ5ZjExYTM0YmU3MTY0ODVjZjg0ZjM3MjA5ODc4MDc1M2IxMzZhOWNkMDM1Njg4NDFjY2RmNWEzMDNhYmI2YzkwNzVkOTgyYzc2NTVlMzE5M2E4ZjlmMzIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Text Values",
        "Converted Numbers"
    ],
    [
        "1,234.56",
        "=NUMBERVALUE(A2)"
    ],
    [
        "\u20ac2.345,67",
        "=NUMBERVALUE(A3,\",\",\".\")"
    ],
    [
        "$1,500.00",
        "=NUMBERVALUE(A4)"
    ],
    [
        "3 456,78",
        "=NUMBERVALUE(A5,\",\",\" \")"
    ]
]
  }]
});
</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('OTQyZDUzZGQ5ZTRkOTIxMjlhMjMyMGMzZjI0OTQzYjQ2NDkwODVjNGQ5ZjExYTM0YmU3MTY0ODVjZjg0ZjM3MjA5ODc4MDc1M2IxMzZhOWNkMDM1Njg4NDFjY2RmNWEzMDNhYmI2YzkwNzVkOTgyYzc2NTVlMzE5M2E4ZjlmMzIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Text Values",
        "Converted Numbers"
    ],
    [
        "1,234.56",
        "=NUMBERVALUE(A2)"
    ],
    [
        "\u20ac2.345,67",
        "=NUMBERVALUE(A3,\",\",\".\")"
    ],
    [
        "$1,500.00",
        "=NUMBERVALUE(A4)"
    ],
    [
        "3 456,78",
        "=NUMBERVALUE(A5,\",\",\" \")"
    ]
];

    // 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('OTQyZDUzZGQ5ZTRkOTIxMjlhMjMyMGMzZjI0OTQzYjQ2NDkwODVjNGQ5ZjExYTM0YmU3MTY0ODVjZjg0ZjM3MjA5ODc4MDc1M2IxMzZhOWNkMDM1Njg4NDFjY2RmNWEzMDNhYmI2YzkwNzVkOTgyYzc2NTVlMzE5M2E4ZjlmMzIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Text Values",
        "Converted Numbers"
    ],
    [
        "1,234.56",
        "=NUMBERVALUE(A2)"
    ],
    [
        "\u20ac2.345,67",
        "=NUMBERVALUE(A3,\",\",\".\")"
    ],
    [
        "$1,500.00",
        "=NUMBERVALUE(A4)"
    ],
    [
        "3 456,78",
        "=NUMBERVALUE(A5,\",\",\" \")"
    ]
]

        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('OTQyZDUzZGQ5ZTRkOTIxMjlhMjMyMGMzZjI0OTQzYjQ2NDkwODVjNGQ5ZjExYTM0YmU3MTY0ODVjZjg0ZjM3MjA5ODc4MDc1M2IxMzZhOWNkMDM1Njg4NDFjY2RmNWEzMDNhYmI2YzkwNzVkOTgyYzc2NTVlMzE5M2E4ZjlmMzIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNVEE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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: [
    [
        "Text Values",
        "Converted Numbers"
    ],
    [
        "1,234.56",
        "=NUMBERVALUE(A2)"
    ],
    [
        "\u20ac2.345,67",
        "=NUMBERVALUE(A3,\",\",\".\")"
    ],
    [
        "$1,500.00",
        "=NUMBERVALUE(A4)"
    ],
    [
        "3 456,78",
        "=NUMBERVALUE(A5,\",\",\" \")"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub