Syntax
Argument
Description
text
(Required) The text you are searching within. Wildcard characters are not allowed. If the text is an empty string, returns empty text.you can input a string like “text“, or a cell reference like “C4”, or a cell range reference like “C4: D4“ (work in dynamic array)
pattern
(Required) The first part of text that matches this expression will be returned.You can input a string like “[0-9]+“,or a cell reference like “C4“.
return_mode
A number that specifies what strings to extract.0: (default) Return the first string that matches the pattern.1: Return all strings that match the pattern as an array.2: Return capturing groups from the first match as an array.
case_sensitivity
Determines whether the match is case-sensitive.0: (default) Case sensitive.1: Case insensitive.
Dynamic Array
If allowDynamicArray is set to false, REGEXEXTRACT can still work. But for some cases, you may need to set allowDynamicArray to true to get the best result.
In the below example, you will get "Luve" as the result when the allowDynamicArray flag is false, and ["Luve", "rose"] as the result when the flag is true.
Samples
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
spread.options.allowDynamicArray = true;
var sheet = spread.sheets[0];
var defaultStyle = new GC.Spread.Sheets.Style();
defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
defaultStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
sheet.setDefaultStyle(defaultStyle);
sheet.setColumnWidth(0, 220);
sheet.setColumnWidth(1, 220);
sheet.setColumnWidth(2, 120);
sheet.setColumnWidth(3, 130);
sheet.setColumnWidth(4, 170);
sheet.setColumnWidth(5, 80);
sheet.setColumnWidth(6, 80);
sheet.addSpan(0, 0, 2, 1);
sheet.addSpan(0, 1, 2, 1);
sheet.addSpan(0, 2, 2, 1);
sheet.addSpan(0, 3, 2, 1);
sheet.addSpan(0, 4, 2, 1);
sheet.addSpan(0, 5, 2, 2);
sheet.getCell(0, 0).value("Text").font("21px bold normal normal");
sheet.getCell(0, 1).value("Regular Expression").font("21px bold normal normal");
sheet.getCell(0, 2).value("Return Mode").font("21px bold normal normal");
sheet.getCell(0, 3).value("Case Sensitivity").font("21px bold normal normal");
sheet.getCell(0, 4).value("Formula Text").font("21px bold normal normal");
sheet.getCell(0, 5).value("Result").font("21px bold normal normal");
var dataArr = [ ["I think SpreadJS is Good", "(good)", "", 1],
["there is 300 rabbits grazing", "\\d+", ""],
["O my Luve is like a red, red rose", "O my (\\w+) is like a red, red (\\w+)", 2]];
sheet.setArray(2, 0, dataArr);
for (var i = 0, len = dataArr.length; i < len; i++) {
var row = 3 + i;
sheet.setFormula(row - 1, 5, `=REGEXEXTRACT(A${row}, B${row}, C${row}, D${row})`);
sheet.setFormula(row - 1, 4, `=FORMULATEXT(F${row})`);
}
spread.resumePaint();
document.getElementById("allowDynamicArray").addEventListener('change', function (e) {
var checked = e.target.checked;
spread.options.allowDynamicArray = !!checked;
spread.resumeCalcService();
spread.resumePaint();
});
}
<!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 class="options-container">
<div class="option-row">
<label class="colorLabel">Switch the allowDynamicArray flag.</label>
</div>
<div class="option-row">
<input type="checkbox" id="allowDynamicArray" checked />
<label for="allowDynamicArray">Allow Dynamic Array</label>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.option-row {
margin-bottom: 12px;
}
label {
user-select: none;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
