Products

Documentation keyboard_arrow_right Formulas keyboard_arrow_right Functions keyboard_arrow_right Regexmatch

REGEXMATCH function

PRO

The REGEXMATCH function in Jspreadsheet Formulas Pro is a tool that checks if a specific text aligns with a given pattern, known as a regular expression. It can be used to verify the format of user input, such as email addresses, phone numbers or passwords. The function returns a 'true' result if the text matches the pattern, and 'false' if it doesn't. This can be very useful for maintaining data accuracy and consistency.

Documentation

Determines whether a text matches a regular expression.

Category

Text

Syntax

REGEXMATCH(text, regex)

Parameter Description
text Text that is tested with the regular expression.
regex Regular expression used to test the text.

Behavior

The REGEXMATCH function is used to check if a piece of text matches a specified regular expression (regex). Here is how it generally behaves:

  • Text: The function applies the regex to the text and returns TRUE if there's a match and FALSE if there isn't.
  • Empty Cells: If the cell is empty, the function generally returns FALSE as there is no text to match the regex against.
  • Numbers: Numbers are treated as text in the context of this function. Therefore, the function will try to match the regex against the text representation of the number.
  • Booleans: TRUE and FALSE are treated as text strings "TRUE" and "FALSE" respectively.
  • Errors: If the regex is invalid or if there is an error in the input, the function will return an error.

Common Errors

Error Name Description
#N/A This error is displayed when the regex is invalid.
#VALUE! This error is displayed when the input text is not a string.
#ERROR! This error is displayed when there is a problem with the regex, such as a missing closing bracket.

Best practices

  • Always double-check your regular expressions for errors. A small mistake can cause your function to fail.
  • Remember that the REGEXMATCH function is case-sensitive. If you want to ignore case, use the (?i) flag at the start of your regular expression.
  • Keep your regular expressions as simple as possible for better performance. Complex regular expressions can slow down your spreadsheet.
  • Use REGEXMATCH in combination with other functions to get the most out of it. For example, you can use it with IF to create conditions based on text patterns.

Usage

A few examples using the REGEXMATCH function.

// Match a decimal price
REGEXMATCH("Price: $10.50","[0-9]+\\.[0-9]+$") 
returns TRUE  

REGEXMATCH("Price: $10","[0-9]+\\.[0-9]+$") 
returns FALSE  

// Validate an email format
REGEXMATCH("[email protected]","^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$") 
returns TRUE  

// Case-insensitive match
REGEXMATCH("Hello World","(?i)hello") 
returns TRUE

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

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Email Address",
        "Valid Format?"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A2,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "invalid-email",
        "=REGEXMATCH(A3,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A4,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "not.an.email.address",
        "=REGEXMATCH(A5,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ]
]
  }]
});
</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('M2E5NWJiNjc3OGRkNmNmMmI5YjJiODI1ZWU1ZjYzZGQ1NDhiOGQ3ZGFlYjQxZGVhNTZlZDZmMDQ3MTZiMWY0ZWQzMGQ1YjRlYTBmMjljNmRmYjZlNGJlMzIyY2YyYzllOTM3MWU4ZjgyMmY1N2NlNTE4MjQ4MzYwYWRhNDMxZWIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Email Address",
        "Valid Format?"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A2,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "invalid-email",
        "=REGEXMATCH(A3,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A4,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "not.an.email.address",
        "=REGEXMATCH(A5,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ]
];

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

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Email Address",
        "Valid Format?"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A2,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "invalid-email",
        "=REGEXMATCH(A3,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A4,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "not.an.email.address",
        "=REGEXMATCH(A5,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ]
]

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

// 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 Address",
        "Valid Format?"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A2,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "invalid-email",
        "=REGEXMATCH(A3,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "[email protected]",
        "=REGEXMATCH(A4,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ],
    [
        "not.an.email.address",
        "=REGEXMATCH(A5,\"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$\")"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub