SJS.REGEXEXTRACT

The SJS.REGEXEXTRACT function extracts the matching substrings according to a specified regular expression.

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) regular_expression (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“. modifiers The modifiers to define the pattern.You can input a string like “gi“, or a cell reference like “C4“. Modifiers Modifier Name Description g global Retain the index of the last match, allowing subsequent searches to start from the end of the previous match.Without the global flag, subsequent searches will return the same match. i ignore case Makes the whole expression case-insensitive.For example, /aBc/i would match AbC. m multiline When the multiline flag is enabled, beginning and end anchors (^ and \$) will match the start and end of a line, instead of the start and end of the whole string.Note that patterns such as /^[\s\S]+$/m may return matches that span multiple lines because the anchors will match the start/end of any line. u unicode When the unicode flag is enabled, you can use extended unicode escapes in the form \x{FFFFF}.It also makes other escapes stricter, causing unrecognized escapes (ex. \j) to throw an error. y sticky The expression will only match from its lastIndex position and ignores the global (g) flag if set. Because each search in RegExr is discrete, this flag has no further impact on the displayed results. s dotAll Dot (.) will match any character, including newline. Please make sure your browser supports the modifiers you use. The modifier “y“ is not supported in SJS because sticky will lead to inconsistent calculation results.If modifier “y“ is set, it will be ignored in the calculation. Dynamic Array If allowDynamicArray is set to false, SJS.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, 250); sheet.setColumnWidth(1, 250); sheet.setColumnWidth(2, 120); sheet.setColumnWidth(3, 250); sheet.setColumnWidth(4, 80); sheet.setColumnWidth(5, 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, 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("Modifiers").font("21px bold normal normal"); sheet.getCell(0, 3).value("Formula Text").font("21px bold normal normal"); sheet.getCell(0, 4).value("Result").font("21px bold normal normal"); var dataArr = [ ["I think SpreadJS is Good", ".*(good).*", "i"], ["there is 300 rabbits grazing", "\\d+", ""], ["O my Luve is like a red, red rose", "O my (\\w+) is like a red, red (\\w+)", ""]]; sheet.setArray(2, 0, dataArr); for (var i = 0, len = dataArr.length; i < len; i++) { var row = 3 + i; sheet.setFormula(row - 1, 4, `=SJS.REGEXEXTRACT(A${row}, B${row}, C${row})`); sheet.setFormula(row - 1 , 3, `=FORMULATEXT(E${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; }