REGEXREPLACE Function

Description

Replace text from a string with another string, based on a supplied regular expression.

NOTE

• By default, REGEXREPLACE 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!=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

---

EXAMPLE

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

//𝘳𝘦𝘮𝘰𝘷𝘦 𝘱𝘩𝘰𝘯𝘦 𝘯𝘶𝘮𝘣𝘦𝘳 𝘱𝘳𝘦𝘧𝘪𝘹, 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘚𝘰𝘯𝘪𝘢 𝘙𝘦𝘦𝘴 555-4195'
=REGEXREPLACE("Sonia Rees(378) 555-4195", "\(.*?\)", "")

//𝘳𝘦𝘱𝘭𝘢𝘤𝘦 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘨𝘳𝘰𝘶𝘱𝘴 𝘢𝘵 𝘰𝘯𝘤𝘦.
//𝘰𝘤𝘤𝘶𝘳𝘳𝘦𝘯𝘤𝘦=0, 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘔𝘰𝘵𝘰𝘳𝘣𝘪𝘬𝘦 𝘩𝘢𝘵𝘦𝘴 𝘊𝘢𝘳, 𝘊𝘢𝘳 𝘩𝘢𝘵𝘦𝘴 𝘗𝘭𝘢𝘯𝘦 𝘣𝘶𝘵 𝘗𝘭𝘢𝘯𝘦 𝘭𝘰𝘷𝘦𝘴 𝘔𝘰𝘵𝘰𝘳𝘣𝘪𝘬𝘦 𝘢𝘯𝘥 𝘊𝘢𝘳'
=REGEXREPLACE("Red hates Green, Green hates Yellow but Yellow loves Red and Green.", "(?<name1>Red)|(?<name2>Green)|(?<name3>Yellow)", "${name1:+Motorbike:${name2:+Car:Plane}}")

//𝘵𝘦𝘹𝘵 𝘤𝘢𝘴𝘦 𝘤𝘰𝘯𝘷𝘦𝘳𝘴𝘪𝘰𝘯, 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘟𝘊𝘌𝘓, 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘞𝘖𝘙𝘋'
=REGEXREPLACE("Microsoft Excel, Microsoft Word.", "\s([^M]+)", " \U$1")</name3></name2></name1>
//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘰𝘤𝘤𝘶𝘳𝘳𝘦𝘯𝘤𝘦=0[0,<0,<0], 𝘤𝘢𝘴𝘦_𝘴𝘦𝘯𝘴𝘪𝘵𝘪𝘷𝘪𝘵𝘺=1[0,1]

//𝘳𝘦𝘮𝘰𝘷𝘦 𝘱𝘩𝘰𝘯𝘦 𝘯𝘶𝘮𝘣𝘦𝘳 𝘱𝘳𝘦𝘧𝘪𝘹, 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘚𝘰𝘯𝘪𝘢 𝘙𝘦𝘦𝘴 555-4195'
=REGEXREPLACE("Sonia Rees(378) 555-4195", "\(.*?\)", "")

//𝘳𝘦𝘱𝘭𝘢𝘤𝘦 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘨𝘳𝘰𝘶𝘱𝘴 𝘢𝘵 𝘰𝘯𝘤𝘦.
//𝘰𝘤𝘤𝘶𝘳𝘳𝘦𝘯𝘤𝘦=0, 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘔𝘰𝘵𝘰𝘳𝘣𝘪𝘬𝘦 𝘩𝘢𝘵𝘦𝘴 𝘊𝘢𝘳, 𝘊𝘢𝘳 𝘩𝘢𝘵𝘦𝘴 𝘗𝘭𝘢𝘯𝘦 𝘣𝘶𝘵 𝘗𝘭𝘢𝘯𝘦 𝘭𝘰𝘷𝘦𝘴 𝘔𝘰𝘵𝘰𝘳𝘣𝘪𝘬𝘦 𝘢𝘯𝘥 𝘊𝘢𝘳'
=REGEXREPLACE("Red hates Green, Green hates Yellow but Yellow loves Red and Green.", "(?<name1>Red)|(?<name2>Green)|(?<name3>Yellow)", "${name1:+Motorbike:${name2:+Car:Plane}}")

//𝘵𝘦𝘹𝘵 𝘤𝘢𝘴𝘦 𝘤𝘰𝘯𝘷𝘦𝘳𝘴𝘪𝘰𝘯, 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘟𝘊𝘌𝘓, 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘞𝘖𝘙𝘋'
=REGEXREPLACE("Microsoft Excel, Microsoft Word.", "\s([^M]+)", " \U$1")

RELATED