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.

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('NjE5OWNkOTZiZGM2MTc3MTdjNTA1OWU5MGM5ODFhZWUwZjNlZmFjZjQwZDM1ZjFkYTVmZmY0YmY5MGJkYzE4MTRmZDBhYTQwOWMyYmZmYmQ2ZGIwM2U2NTA4ZmQ0NDM2ODE2MjkxODRmMzMwNDg1ZDhlNzQ1YjNmNTdhYzFmMmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5qQTJOekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('NjE5OWNkOTZiZGM2MTc3MTdjNTA1OWU5MGM5ODFhZWUwZjNlZmFjZjQwZDM1ZjFkYTVmZmY0YmY5MGJkYzE4MTRmZDBhYTQwOWMyYmZmYmQ2ZGIwM2U2NTA4ZmQ0NDM2ODE2MjkxODRmMzMwNDg1ZDhlNzQ1YjNmNTdhYzFmMmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5qQTJOekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('NjE5OWNkOTZiZGM2MTc3MTdjNTA1OWU5MGM5ODFhZWUwZjNlZmFjZjQwZDM1ZjFkYTVmZmY0YmY5MGJkYzE4MTRmZDBhYTQwOWMyYmZmYmQ2ZGIwM2U2NTA4ZmQ0NDM2ODE2MjkxODRmMzMwNDg1ZDhlNzQ1YjNmNTdhYzFmMmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5qQTJOekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('NjE5OWNkOTZiZGM2MTc3MTdjNTA1OWU5MGM5ODFhZWUwZjNlZmFjZjQwZDM1ZjFkYTVmZmY0YmY5MGJkYzE4MTRmZDBhYTQwOWMyYmZmYmQ2ZGIwM2U2NTA4ZmQ0NDM2ODE2MjkxODRmMzMwNDg1ZDhlNzQ1YjNmNTdhYzFmMmEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5qQTJOekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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+\",\"\")"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub