REGEXREPLACE function
PRO
The REGEXREPLACE
function in Jspreadsheet Formulas Pro is a powerful tool that allows you to replace certain parts of a text based on a pattern you define, known as a regular expression. This function takes three arguments: the original text, the regular expression which defines the pattern you want to replace, and the replacement text. The function searches the original text for any sequences that match the regular expression, and then substitutes those sequences with the replacement text. This can be especially useful for tasks like data cleaning or formatting.
Documentation
Replaces parts of a text using a regular expression.
Category
Text
Syntax
REGEXREPLACE(text, regex, replacement)
Parameter | Description |
---|---|
text |
Text in which replacements occur. |
regex |
Regular expression that dictates which parts should be replaced. |
replacement |
Text that is inserted into the text of the first argument. |
Behavior
REGEXREPLACE
is a function used to replace part of a text string with a different text string using regular expressions. The function behaves in the following ways:
- If the cell referenced is empty or does not contain the pattern specified in the regular expression,
REGEXREPLACE
will return the original text string without any changes. - If the cell contains text,
REGEXREPLACE
will replace all instances of the specified pattern in the text string with the replacement string. - If the cell contains a boolean value,
REGEXREPLACE
will treat it as a text string and proceed with the replace operation. - If the regular expression pattern or the replacement string is not a text string,
REGEXREPLACE
will return an error. - If the cell contains an error,
REGEXREPLACE
will propagate that error.
Common Errors
Error Name | Description |
---|---|
#VALUE! | This error occurs when the regular expression is invalid, i.e., it does not follow the correct syntax for regular expressions. |
#ERROR! | This error is returned when the arguments passed in the function are not in the correct format or sequence. |
Best practices
- Always ensure that the regular expression pattern is valid and follows the correct syntax to avoid #VALUE! errors.
- Specify the pattern and replacement string as text strings to avoid #ERROR! errors.
- When using
REGEXREPLACE
on a range of cells, ensure that all cells in the range contain text strings to ensure consistent results.- Be aware that
REGEXREPLACE
will replace all instances of the specified pattern in the text string, not just the first instance. If you only want to replace the first instance, consider using a different function.
Usage
A few examples using the REGEXREPLACE function.
REGEXREPLACE("The price today is (price)", "\(price\)", "$10") returns "The price today is $10"
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Email",
"Clean Email",
"Formula"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A2,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A3,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A4,\"\\d+\",\"\")"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Email",
"Clean Email",
"Formula"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A2,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A3,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A4,\"\\d+\",\"\")"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Email",
"Clean Email",
"Formula"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A2,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A3,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A4,\"\\d+\",\"\")"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MGIzZmVhYTE4NzgwMWMyYTM4YzdmMDdmNzdiZTRiNzI4ZGUwOTcxY2FhNWQ0OWFjOTMxZjRiYzM1YWZkYjc4MmUzNGJmNzUzNzc2NjJlZTAyNTRkZTg5ZjdhZDIzMGQxMjM3ZmRmOTM3MjI3NGZkYzI3NjQ4MDcwNzMzMGFhMzgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lPVEEyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Email",
"Clean Email",
"Formula"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A2,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A3,\"\\d+\",\"\")"
],
[
"[email protected]",
"[email protected]",
"=REGEXREPLACE(A4,\"\\d+\",\"\")"
]
]
}]
});
}
}