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
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
export function AppFunc() {
const initSpread = (workbook) => {
var 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]
}
];
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
workbook.suspendCalcService();
initStyles(workbook);
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();
}
const 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);
}
const 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 (cell[0] === '=') {
sheet.setFormula(r, c, cell);
} else {
sheet.setValue(r, c, cell);
}
}
}
}
const setColumnsWidth = (sheet, columnsWidth) => {
if (!columnsWidth) {
return;
}
for (var i = 0; i < columnsWidth.length; i++) {
sheet.setColumnWidth(i, columnsWidth[i]);
}
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
initSpread(workbook) {
var 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]
}
];
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
workbook.suspendCalcService();
this.initStyles(workbook);
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);
this.setCells(sheet, sheetData.cells, 0, 0);
this.setColumnsWidth(sheet, sheetData.columnsWidth);
}
workbook.resumeCalcService();
workbook.resumePaint();
}
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);
}
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 (cell[0] === '=') {
sheet.setFormula(r, c, cell);
} else {
sheet.setValue(r, c, cell);
}
}
}
}
setColumnsWidth(sheet, columnsWidth) {
if (!columnsWidth) {
return;
}
for (var i = 0; i < columnsWidth.length; i++) {
sheet.setColumnWidth(i, columnsWidth[i]);
}
}
}
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
input {
padding: 4px 6px;
}
.options-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
