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