VALUE function
PRO
BASIC
The VALUE
function in Jspreadsheet Formulas Pro is a handy tool that allows you to turn a text string, that looks like a number, into an actual number that can be used in calculations. For instance, if you have the number '100' stored as text in a cell, the VALUE
function can convert it into the numeric value 100. This is particularly useful when you want to perform calculations or data analysis with numbers that were originally entered as text.
Documentation
Converts a text string that represents a number to a number.
Category
Text
Syntax
VALUE(text)
Parameter | Description |
---|---|
text |
The text string that contains the number you want to convert. The text can be in any of the constant number, date, or time formats recognized by Excel. |
Behavior
The VALUE
function in spreadsheets is used to convert a text string that represents a number into a numeric value. Here's how it handles various types of inputs:
- Empty Cells: If an empty cell is referenced, the function will return a #VALUE! error.
- Text: The function tries to convert the text into a numeric value. If the text is a string that cannot be converted into a number, a #VALUE! error is returned.
- Booleans: Booleans are not accepted by the function and will result in a #VALUE! error.
- Errors: If the referenced cell contains an error, the
VALUE
function will return the same error. - Numbers: If the input is already a number, the function simply returns the number.
Common Errors
Error | Description |
---|---|
#VALUE! | This error is displayed when the function cannot convert the input text string into a numeric value. It also occurs when the referenced cell is empty or contains a boolean. |
#REF! | This error is displayed when the cell reference is not valid. |
Best practices
- Always ensure that the text you're trying to convert into a number is numerically compatible. If it contains non-numeric characters (except for period, comma, E/e for scientific notation, and minus sign for negative numbers), the function will return a #VALUE! error.
- Use the
VALUE
function when you need to perform mathematical operations on a number that has been entered as text.- Avoid using
VALUE
function on cells that contain boolean or error values.- Always check your cell references to prevent #REF! errors.
Usage
A few examples using the VALUE function.
VALUE("123") returns 123
VALUE("$456") returns 456
VALUE("12:34") returns 0.523611111
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('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Text Values",
"Converted Numbers"
],
[
"123",
"=VALUE(A2)"
],
[
"$4,567.89",
"=VALUE(A3)"
],
[
"98.6%",
"=VALUE(A4)"
],
[
"1:30",
"=VALUE(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('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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"
],
[
"123",
"=VALUE(A2)"
],
[
"$4,567.89",
"=VALUE(A3)"
],
[
"98.6%",
"=VALUE(A4)"
],
[
"1:30",
"=VALUE(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('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Text Values",
"Converted Numbers"
],
[
"123",
"=VALUE(A2)"
],
[
"$4,567.89",
"=VALUE(A3)"
],
[
"98.6%",
"=VALUE(A4)"
],
[
"1:30",
"=VALUE(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('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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"
],
[
"123",
"=VALUE(A2)"
],
[
"$4,567.89",
"=VALUE(A3)"
],
[
"98.6%",
"=VALUE(A4)"
],
[
"1:30",
"=VALUE(A5)"
]
]
}]
});
}
}