The SJS.REGEXREPLACE function can replace part of a text string with a different text string using regular expressions.

Syntax Argument Description text (Required) The text, a part of which will be replaced.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 regular expression. All matching instances in text will be replaced.You can input a string like “[0-9]+“,or a cell reference like “C4“. replacement (Required) The text which will be inserted into the original text according to the regular_expression.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. Samples
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); var sheet = spread.sheets[0]; var defaultStyle = new GC.Spread.Sheets.Style(); defaultStyle.hAlign =; defaultStyle.vAlign =; sheet.setDefaultStyle(defaultStyle); sheet.setColumnWidth(0, 200); sheet.setColumnWidth(1, 230); sheet.setColumnWidth(2, 160); sheet.setColumnWidth(3, 140); sheet.setColumnWidth(4, 250); sheet.setColumnWidth(5, 150); 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, 1); 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("Replace Text").font("21px bold normal normal"); sheet.getCell(0, 3).value("Modifiers").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 = [ ["SpreadJS Worksheet", "s.*t", "book", ""], ["12/23/2022", "(\\d{2})/(\\d{2})/(\\d{4})", "$3-$1-$2", ""], ["John-Smith", "(\\w{4})-(\\w{5})", "$2 $1", ""], ["Red red Rose", "red", "blue", "gi"]]; sheet.setArray(2, 0, dataArr); for (var i = 0, len = dataArr.length; i < len; i++) { var row = 3 + i; sheet.setFormula(row - 1, 5, `=SJS.REGEXREPLACE(A${row}, B${row}, C${row}, D${row})`); sheet.setFormula(row - 1 , 4, `=FORMULATEXT(F${row})`); } sheet.tables.add("table1", 0, 0, 6, 6, GC.Spread.Sheets.Tables.TableThemes.medium28); 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> </body> </html>
input[type="text"] { width: 200px; margin-right: 20px; } label { display: inline-block; width: 110px; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } label { display: block; margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; width:216px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } code { border: 1px solid #000; }