VALUETOTEXT
The VALUETOTEXT function returns text from any specified value.
Syntax
Arguments
value
The value to return as text.
[format]
The format of the returned data. It can be one of two values:
0 - Default. Concise format that is easy to read.
1 - Strict format that includes escape characters and row delimiters.
ARRAYTOTEXT
The ARRAYTOTEXT function returns an array of text values from any specified range.
Syntax
Arguments
array
The array to return as text.
[format]
The format of the returned data. It can be one of two values:
0 - Default. Concise format that is easy to read.
1 - Strict format that includes escape characters and row delimiters.
Note
When the input value is an error, such as #VALUE! or #DIV/0!, both VALUETOTEXT and ARRAYTOTEXT return the error text itself instead of returning an error. This differs from the TEXT function, which returns an error for error values.
Sample
Data:
A
B
1
TRUE
#VALUE!
2
1234.01234
Seattle
3
Hello
1123
VALUETOTEXT Examples:
Formula(format=0)
Result
Formula(format=1)
Result
=VALUETOTEXT(A1,0)
TRUE
=VALUETOTEXT(A1,1)
TRUE
=VALUETOTEXT(B1,0)
#VALUE!
=VALUETOTEXT(B1,1)
#VALUE!
=VALUETOTEXT(A2,0)
1234.01234
=VALUETOTEXT(A2,1)
1234.01234
=VALUETOTEXT(B2,0)
Seattle
=VALUETOTEXT(B2,1)
"Seattle"
=VALUETOTEXT(A3,0)
Hello
=VALUETOTEXT(A3,1)
"Hello"
=VALUETOTEXT(B3,0)
1123
=VALUETOTEXT(B3,1)
1123
ARRAYTOTEXT Examples:
Formula
Result
ARRAYTOTEXT(A1:B3,0)
TRUE, #VALUE!, 1234.01234, Seattle, Hello, 1123
ARRAYTOTEXT(A1:B3,1)
{TRUE,#VALUE!;1234.01234,"Seattle";"Hello",1123}
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
spread.options.allowDynamicArray = true;
initStyles(spread);
initSpread(spread);
};
var data = [
{
sheetName: 'VALUETOTEXT',
cells: [
[{v:"Data",s:"title"}],
[true],
[1234.01234],
["Hello"],
[`="Hello" + 1`],
["Seattle"],
[1234],
[],
[{v:"Concise Formula",s:"title"}, {v:"Concise Result",s:"title"}, {v:"Strict Formula",s:"title"}, {v:"Strict Result",s:"title"}],
["=FORMULATEXT(B10)", "=VALUETOTEXT(A2,0)", "=FORMULATEXT(D10)", "=VALUETOTEXT(A2,1)"],
["=FORMULATEXT(B11)", "=VALUETOTEXT(A3,0)", "=FORMULATEXT(D11)", "=VALUETOTEXT(A3,1)"],
["=FORMULATEXT(B12)", "=VALUETOTEXT(A4,0)", "=FORMULATEXT(D12)", "=VALUETOTEXT(A4,1)"],
["=FORMULATEXT(B13)", "=VALUETOTEXT(A5,0)", "=FORMULATEXT(D13)", "=VALUETOTEXT(A5,1)"],
["=FORMULATEXT(B14)", "=VALUETOTEXT(A6,0)", "=FORMULATEXT(D14)", "=VALUETOTEXT(A6,1)"],
["=FORMULATEXT(B15)", "=VALUETOTEXT(A7,0)", "=FORMULATEXT(D15)", "=VALUETOTEXT(A7,1)"],
],
columnsWidth: [200, 200, 200, 200]
},
{
sheetName: 'ARRAYTOTEXT',
cells: [
[{v:"Data",s:"title"}, ""],
[true, `="Hello" + 1`],
[1234.01234, "Seattle"],
["Hello", 1123],
[],
[{v:"Formula",s:"title"}, {v:"Result",s:"title"}, {v:"Formula",s:"title"}, {v:"Result",s:"title"}],
["=FORMULATEXT(B7)", "=ARRAYTOTEXT(A2:B4,0)", "=FORMULATEXT(D7)", "=ARRAYTOTEXT(A2:B4,1)"],
],
columnsWidth: [180, 280, 180, 280]
}
];
function initSpread(workbook) {
workbook.suspendPaint();
workbook.suspendCalcService();
workbook.setSheetCount(data.length);
for (var i = 0; i < data.length; i++) {
var sheetData = data[i];
var sheet = workbook.sheets[i];
sheet.name(sheetData.sheetName);
setCells(sheet, sheetData.cells, 0, 0);
setColumnsWidth(sheet, sheetData.columnsWidth);
}
workbook.resumeCalcService();
workbook.resumePaint();
}
function initStyles(workbook) {
var style = new GC.Spread.Sheets.Style();
style.name = 'title';
style.font = 'normal bold 16px Segoe UI';
style.foreColor = "#172b4d";
workbook.addNamedStyle(style);
}
function setCells(sheet, cells, rowIndex, colIndex) {
for(var i = 0; i < cells.length; i++) {
var row = cells[i];
var r = rowIndex + i;
for (var j = 0; j < row.length; j++) {
var cell = row[j];
var c = colIndex + j;
if (typeof cell === "object") {
if (cell.v !== undefined) {
sheet.setValue(r, c, cell.v);
}
if (cell.s !== undefined) {
sheet.setStyle(r, c, cell.s)
}
} else if (typeof cell === 'string' && cell[0] === '=') {
sheet.setFormula(r, c, cell);
} else {
sheet.setValue(r, c, cell);
}
}
}
}
function setColumnsWidth(sheet, columnsWidth) {
if (!columnsWidth) {
return;
}
for (var i = 0; i < columnsWidth.length; i++) {
sheet.setColumnWidth(i, columnsWidth[i]);
}
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
margin: 0;
}
