Products

REGEXEXTRACT function

PRO

The REGEXEXTRACT function in Jspreadsheet Formulas Pro is a powerful tool that allows you to extract specific parts of a text based on a given pattern, known as a regular expression. This function is especially useful when you need to sift through large amounts of text and pull out certain pieces of information. For example, you could use it to extract all email addresses from a long document. The REGEXEXTRACT function makes this process much more efficient and accurate by automating it.

Documentation

Extracts parts of a text using a regular expression.

Category

Text

Syntax

REGEXEXTRACT(text, regex)

Parameter Description
text Text from which the parts will be extracted.
regex Regular expression that dictates which parts should be extracted.

Behavior

The REGEXEXTRACT function is used to extract matching substrings according to a regular expression. Here's how it handles different types of data:

  • Empty cells: If a cell is empty, REGEXEXTRACT will return an empty string.
  • Text: REGEXEXTRACT operates on text strings. The function will match the regular expression against the text and return the first matching substring.
  • Numbers: If a number is passed, it will be treated as a text string and REGEXEXTRACT will try to match the regular expression against it.
  • Booleans: Booleans are converted to their text representations ('TRUE' or 'FALSE') and then REGEXEXTRACT will try to match the regular expression against these.
  • Errors: If REGEXEXTRACT cannot find a match or if the regular expression is invalid, it will return a #N/A error.

Common Errors

Error Description
#VALUE! This error is returned when the given regular expression is invalid.
#N/A This error is returned when REGEXEXTRACT cannot find a match.
#ERROR! Returned when the function is called with missing arguments or an invalid number of parameters.

Best practices

  • Always ensure that the regular expression is valid and correctly formatted to avoid #VALUE! errors.
  • Remember that REGEXEXTRACT will return the first match it finds. If you want to extract all matches, you may need to use an array formula or script.
  • Be aware that REGEXEXTRACT is case sensitive. If you need to perform a case insensitive extraction, make sure to adjust your regular expression accordingly.
  • Check your data type. If you want to extract data from numbers or booleans, make sure they are converted to text beforehand.

Usage

A few examples using the REGEXEXTRACT function.

// Extract a decimal number from a string
REGEXEXTRACT("The price today is $826.25", "[0-9]+\\.[0-9]+") 
returns "826.25"

// Extract the first email from a sentence
REGEXEXTRACT("Contact us at [email protected] for help", "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}") 
returns "[email protected]"

// Extract a year from a date string
REGEXEXTRACT("Release date: 2025-08-21", "\\d{4}") 
returns "2025"

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Email Address",
        "Domain",
        "Extract Domain"
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A2, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A3, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A4, \"@(.+)\")",
        ""
    ]
]
  }]
});
</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('YTI0ZGYyNzJhNTE3NzVmNTMyMGFjOTJkYTI3ODM4NjJjM2Q4MDIwNzc4MmRmNzFlZjFlNGViZjI2ZDI1ZGUyMDc4Y2Q5ZjE5NGE5YzVkMDgyYTRjZDJmMzBiMDIzZThiYjNkZDVmYWFmMDljZGE2MTRhYzJlZTc2ZjZiOTI3ZTYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3lOekk0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Email Address",
        "Domain",
        "Extract Domain"
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A2, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A3, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A4, \"@(.+)\")",
        ""
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Email Address",
        "Domain",
        "Extract Domain"
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A2, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A3, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A4, \"@(.+)\")",
        ""
    ]
]

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

// 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",
        "Domain",
        "Extract Domain"
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A2, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A3, \"@(.+)\")",
        ""
    ],
    [
        "[email protected]",
        "=REGEXEXTRACT(A4, \"@(.+)\")",
        ""
    ]
]
            }]
        });
    }
}