REGEXEXTRACT Function

Description

Extract text from a string based on a supplied regular expression.

NOTE

• By default, REGEXEXTRACT is case sensitive function and use the PCRE2 ‘flavor’ of regex.
• More syntax:
  – Lookahead and Lookbehind: (?= … ), (?! … ), (?<= … ), (?<! … )
  – Non-capturing group: (?: … ), disable capture groups: (?n)
  – Atomic groups: (?> … )
  – Named Capture: (?<name> … ) or (?P<name> … )
  – Branching structure: (?| … )
  – Inline comments: (?# … )
  – ……………

Syntax

Copied!=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

---

EXAMPLE

Copied!//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=0 [0,2], 𝘤𝘢𝘴𝘦_𝘴𝘦𝘯𝘴𝘪𝘵𝘪𝘷𝘪𝘵𝘺=1 [0,1]

//𝘮𝘢𝘵𝘤𝘩𝘪𝘯𝘨 𝘥𝘢𝘵𝘦 𝘪𝘯 𝘺𝘺𝘺𝘺-𝘮𝘮-𝘥𝘥 𝘧𝘰𝘳𝘮𝘢𝘵.
//𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=0, 𝘴𝘵𝘰𝘱 𝘢𝘵 𝘧𝘪𝘳𝘴𝘵 𝘮𝘢𝘵𝘤𝘩 2020-04-21.
=REGEXEXTRACT("On 2020-04-21, Office 365 was rebranded as Microsoft 365", "((?:19|20)\d\d)[-/-](0[1-9]|1[012])[-/.](0[1-9]|[12][0-9]|3[01])")

//𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=1, 𝘦𝘹𝘵𝘳𝘢𝘤𝘵 𝘢𝘭𝘭 𝘮𝘢𝘵𝘤𝘩𝘦𝘴, 𝘳𝘦𝘵𝘶𝘳𝘯 𝘖𝘧𝘧𝘪𝘤𝘦 2016/𝘖𝘧𝘧𝘪𝘤𝘦 2019.
=REGEXEXTRACT("Microsoft will end support for Office 2016 and Office 2019 in October 2025", "\w+\s\d{4}(?=\s)", 1)

//𝘤𝘰𝘯𝘵𝘢𝘪𝘯𝘴 𝘶𝘱𝘱𝘦𝘳𝘤𝘢𝘴𝘦 𝘸𝘰𝘳𝘥, 𝘳𝘦𝘵𝘶𝘳𝘯 𝘓𝘛𝘚𝘊 2021
=REGEXEXTRACT("Office LTSC 2021 is supported on devices running Windows 10 or Windows 11", "[[:upper:]]+\s\d+")

//𝘳𝘦𝘵𝘶𝘳𝘯 𝘺𝘦𝘢𝘳𝘴 𝘵𝘩𝘢𝘵 𝘤𝘰𝘯𝘵𝘢𝘪𝘯 𝘵𝘩𝘦 𝘴𝘢𝘮𝘦 𝘭𝘢𝘴𝘵 2 𝘥𝘪𝘨𝘪𝘵𝘴, 𝘳𝘦𝘵𝘶𝘳𝘯 2011/2022
=REGEXEXTRACT("2011, 2021, 2022, 2016", "\d{2}(?P<a>\d)\k</a><a>", 1)

//𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=2, 𝘦𝘹𝘵𝘳𝘢𝘤𝘵 𝘤𝘢𝘱𝘵𝘶𝘳𝘪𝘯𝘨 𝘨𝘳𝘰𝘶𝘱𝘴, 𝘳𝘦𝘵𝘶𝘳𝘯 123
=REGEXEXTRACT("123-456-7890", "(\d{3})-\d{3}-\d{4}", 2)</a>
//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=0 [0,2], 𝘤𝘢𝘴𝘦_𝘴𝘦𝘯𝘴𝘪𝘵𝘪𝘷𝘪𝘵𝘺=1 [0,1]

//𝘮𝘢𝘵𝘤𝘩𝘪𝘯𝘨 𝘥𝘢𝘵𝘦 𝘪𝘯 𝘺𝘺𝘺𝘺-𝘮𝘮-𝘥𝘥 𝘧𝘰𝘳𝘮𝘢𝘵.
//𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=0, 𝘴𝘵𝘰𝘱 𝘢𝘵 𝘧𝘪𝘳𝘴𝘵 𝘮𝘢𝘵𝘤𝘩 2020-04-21.
=REGEXEXTRACT("On 2020-04-21, Office 365 was rebranded as Microsoft 365", "((?:19|20)\d\d)[-/-](0[1-9]|1[012])[-/.](0[1-9]|[12][0-9]|3[01])")

//𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=1, 𝘦𝘹𝘵𝘳𝘢𝘤𝘵 𝘢𝘭𝘭 𝘮𝘢𝘵𝘤𝘩𝘦𝘴, 𝘳𝘦𝘵𝘶𝘳𝘯 𝘖𝘧𝘧𝘪𝘤𝘦 2016/𝘖𝘧𝘧𝘪𝘤𝘦 2019.
=REGEXEXTRACT("Microsoft will end support for Office 2016 and Office 2019 in October 2025", "\w+\s\d{4}(?=\s)", 1)

//𝘤𝘰𝘯𝘵𝘢𝘪𝘯𝘴 𝘶𝘱𝘱𝘦𝘳𝘤𝘢𝘴𝘦 𝘸𝘰𝘳𝘥, 𝘳𝘦𝘵𝘶𝘳𝘯 𝘓𝘛𝘚𝘊 2021
=REGEXEXTRACT("Office LTSC 2021 is supported on devices running Windows 10 or Windows 11", "[[:upper:]]+\s\d+")

//𝘳𝘦𝘵𝘶𝘳𝘯 𝘺𝘦𝘢𝘳𝘴 𝘵𝘩𝘢𝘵 𝘤𝘰𝘯𝘵𝘢𝘪𝘯 𝘵𝘩𝘦 𝘴𝘢𝘮𝘦 𝘭𝘢𝘴𝘵 2 𝘥𝘪𝘨𝘪𝘵𝘴, 𝘳𝘦𝘵𝘶𝘳𝘯 2011/2022
=REGEXEXTRACT("2011, 2021, 2022, 2016", "\d{2}(?P<a>\d)\k<a>", 1)

//𝘳𝘦𝘵𝘶𝘳𝘯_𝘮𝘰𝘥𝘦=2, 𝘦𝘹𝘵𝘳𝘢𝘤𝘵 𝘤𝘢𝘱𝘵𝘶𝘳𝘪𝘯𝘨 𝘨𝘳𝘰𝘶𝘱𝘴, 𝘳𝘦𝘵𝘶𝘳𝘯 123
=REGEXEXTRACT("123-456-7890", "(\d{3})-\d{3}-\d{4}", 2)

RELATED