When working with text, a common task is to “break apart” text strings using a delimiter.
To make it easier to extract the text from the start or end of a cell’s contents, we've added two functions that simply return everything before or after your selected delimiter: TEXTBEFORE and TEXTAFTER.
We also added TEXTSPLIT to easily "split" text into multiple segments. Each text segment is then automatically spilled into its own cell through the magic of dynamic arrays.
TEXTBEFORE - Returns text that is before delimiting characters
TEXTAFTER - Returns text that is after delimiting characters
TEXTSPLIT - Splits text into rows or columns using delimiters
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue';
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
const spreadRef = ref(null);
let initSpread = function (spread) {
spreadRef.value = spread;
let data = [
{
sheetName: 'TEXTBEFORE',
cells: [
[{v:"Data",s:"title"}],
["Little Red Riding Hood's red hood"],
["Little red Riding Hood's red hood"],
[{v:"Formulas",s:"title"}, {v:"Results",s:"title"}],
["=FORMULATEXT(B5)", '=TEXTBEFORE(A2,"Red")'],
["=FORMULATEXT(B6)", '=TEXTBEFORE(A3,"red",2)'],
["=FORMULATEXT(B7)", '=TEXTBEFORE(A3,"red",-2)'],
["=FORMULATEXT(B8)", '=TEXTBEFORE(A3,"Red")'],
["=FORMULATEXT(B9)", '=TEXTBEFORE(A3,"Red",,1)'],
["=FORMULATEXT(B10)", '=TEXTBEFORE(A3,"Riding")'],
],
columnsWidth: [226, 156]
},
{
sheetName: 'TEXTAFTER',
cells: [
[{v:"Data",s:"title"}],
["Little Red Riding Hood's red hood"],
["Little red Riding Hood's red hood"],
[{v:"Formulas",s:"title"}, {v:"Results",s:"title"}],
["=FORMULATEXT(B5)", '=TEXTAFTER(A2,"Red")'],
["=FORMULATEXT(B6)", '=TEXTAFTER(A3,"red",2)'],
["=FORMULATEXT(B7)", '=TEXTAFTER(A3,"red",-2)'],
["=FORMULATEXT(B8)", '=TEXTAFTER(A3,"Red")'],
["=FORMULATEXT(B9)", '=TEXTAFTER(A3,"Red",,1)'],
["=FORMULATEXT(B10)", '=TEXTAFTER(A3,"Riding")'],
],
columnsWidth: [226, 156]
},
{
sheetName: 'TEXTSPLIT',
cells: [
[{v:"Data",s:"title"}],
["Dakota Lennon Sanchez"],
["To be or not to be"],
["1,2,3;4,5,6"],
[],
[{v:"Formulas",s:"title"}, "=FORMULATEXT(B7)"],
[{v:"Results",s:"title"}, '=TEXTSPLIT(A2, " ")'],
[],
[{v:"Formulas",s:"title"}, "=FORMULATEXT(B10)"],
[{v:"Results",s:"title"}, '=TEXTSPLIT(A3, " ")'],
[],
[{v:"Formulas",s:"title"}, "=FORMULATEXT(B13)"],
[{v:"Results",s:"title"}, '=TEXTSPLIT(A4,",",";")'],
],
columnsWidth: [168]
}
];
spread.options.allowDynamicArray = true;
spread.suspendPaint();
spread.suspendCalcService();
initStyles(spread);
spread.setSheetCount(data.length);
for (let i = 0; i < data.length; i++) {
let sheetData = data[i];
let sheet = spread.sheets[i];
sheet.name(sheetData.sheetName);
setCells(sheet, sheetData.cells, 0, 0);
setColumnsWidth(sheet, sheetData.columnsWidth);
}
spread.resumeCalcService();
spread.resumePaint();
}
let initStyles = function (workbook) {
let style = new GC.Spread.Sheets.Style();
style.name = 'title';
style.font = 'normal bold 16px Segoe UI';
style.foreColor = "#172b4d";
workbook.addNamedStyle(style);
}
let setCells = function (sheet, cells, rowIndex, colIndex) {
for(let i = 0; i < cells.length; i++) {
let row = cells[i];
let r = rowIndex + i;
for (let j = 0; j < row.length; j++) {
let cell = row[j];
let 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 (cell[0] === '=') {
sheet.setFormula(r, c, cell);
} else {
sheet.setValue(r, c, cell);
}
}
}
}
let setColumnsWidth = function (sheet, columnsWidth) {
if (!columnsWidth) {
return;
}
for (let i = 0; i < columnsWidth.length; i++) {
sheet.setColumnWidth(i, columnsWidth[i]);
}
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
