By Portland Runner

2014-03-20 19:09:13 8 Comments

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation?

  • In-cell function to return a matched pattern or replaced value in a string.
  • Sub to loop through a column of data and extract matches to adjacent cells.
  • What setup is necessary?
  • What are Excel's special characters for Regular expressions?

I understand Regex is not ideal for many situations (To use or not to use regular expressions?) since excel can use Left, Mid, Right, Instr type commands for similar manipulations.


@JvdV 2020-02-12 16:19:40

To add to the valuable content, I would like to create this reminder on why sometimes RegEx within VBA is not ideal. Not all expressions are supported, but instead may throw an Error 5017 and may leave the author guessing (which I am a victim of myself).

Whilst we can find some sources on what is supported, it would be helpfull to know which metacharacters etc. are not supported. A more in-depth explaination can be found here. Mentioned in this source:

"Although "VBScript’s regular expression ... version 5.5 implements quite a few essential regex features that were missing in previous versions of VBScript. ... JavaScript and VBScript implement Perl-style regular expressions. However, they lack quite a number of advanced features available in Perl and other modern regular expression flavors:"

So, not supported are:

  • Start of String ancor \A, alternatively use the ^ caret to match postion before 1st char in string
  • End of String ancor \Z, alternatively use the $ dollar sign to match postion after last char in string
  • Positive LookBehind, e.g.: (?<=a)b (whilst postive LookAhead is supported)
  • Negative LookBehind, e.g.: (?<!a)b (whilst negative LookAhead is supported)
  • Atomic Grouping
  • Possessive Quantifiers
  • Unicode e.g.: \{uFFFF}
  • Named Capturing Groups. Alternatively use Numbered Capturing Groups
  • Inline modifiers, e.g.: /i (case sensitivity) or /g (global) etc. Set these through the RegExp object properties > RegExp.Global = True and RegExp.IgnoreCase = True if available.
  • Conditionals
  • Regular Expression Comments. Add these with regular ' comments in script

I already hit a wall more than once using regular expressions within VBA. Usually with LookBehind but sometimes I even forget the modifiers. I have not experienced all these above mentioned backdrops myself but thought I would try to be extensive referring to some more in-depth information. Feel free to comment/correct/add. Big shout out to for a wealth of information.

P.S. You have mentioned regular VBA methods and functions, and I can confirm they (at least to myself) have been helpful in their own ways where RegEx would fail.

@Portland Runner 2014-03-20 19:09:13

Regular expressions are used for Pattern Matching.

To use in Excel follow these steps:

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

  • Select "Developer" tab (I don't have this tab what do I do?)
  • Select "Visual Basic" icon from 'Code' ribbon section
  • In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
  • Select "References"
  • Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
  • Click "OK"

Step 2: Define your pattern

Basic definitions:

- Range.

  • E.g. a-z matches an lower case letters from a to z
  • E.g. 0-5 matches any number from 0 to 5

[] Match exactly one of the objects inside these brackets.

  • E.g. [a] matches the letter a
  • E.g. [abc] matches a single letter which can be a, b or c
  • E.g. [a-z] matches any single lower case letter of the alphabet.

() Groups different matches for return purposes. See examples below.

{} Multiplier for repeated copies of pattern defined before it.

  • E.g. [a]{2} matches two consecutive lower case letter a: aa
  • E.g. [a]{1,3} matches at least one and up to three lower case letter a, aa, aaa

+ Match at least one, or more, of the pattern defined before it.

  • E.g. a+ will match consecutive a's a, aa, aaa, and so on

? Match zero or one of the pattern defined before it.

  • E.g. Pattern may or may not be present but can only be matched one time.
  • E.g. [a-z]? matches empty string or any single lower case letter.

* Match zero or more of the pattern defined before it. - E.g. Wildcard for pattern that may or may not be present. - E.g. [a-z]* matches empty string or string of lower case letters.

. Matches any character except newline \n

  • E.g. a. Matches a two character string starting with a and ending with anything except \n

| OR operator

  • E.g. a|b means either a or b can be matched.
  • E.g. red|white|orange matches exactly one of the colors.

^ NOT operator

  • E.g. [^0-9] character can not contain a number
  • E.g. [^aA] character can not be lower case a or upper case A

\ Escapes special character that follows (overrides above behavior)

  • E.g. \., \\, \(, \?, \$, \^

Anchoring Patterns:

^ Match must occur at start of string

  • E.g. ^a First character must be lower case letter a
  • E.g. ^[0-9] First character must be a number.

$ Match must occur at end of string

  • E.g. a$ Last character must be lower case letter a

Precedence table:

Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |

Predefined Character Abbreviations:

abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

Example 1: Run as macro

The following example macro looks at the value in cell A1 to see if the first 1 or 2 characters are digits. If so, they are removed and the rest of the string is displayed. If not, then a box appears telling you that no match is found. Cell A1 values of 12abc will return abc, value of 1abc will return abc, value of abc123 will return "Not Matched" because the digits were not at the start of the string.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1")

    If strPattern <> "" Then
        strInput = Myrange.Value

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
            MsgBox ("Not matched")
        End If
    End If
End Sub

Example 2: Run as an in-cell function

This example is the same as example 1 but is setup to run as an in-cell function. To use, change the code to this:

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

Place your strings ("12abc") in cell A1. Enter this formula =simpleCellRegex(A1) in cell B1 and the result will be "abc".

results image

Example 3: Loop Through Range

This example is the same as example 1 but loops through a range of cells.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
                MsgBox ("Not matched")
            End If
        End If
End Sub

Example 4: Splitting apart different patterns

This example loops through a range (A1, A2 & A3) and looks for a string starting with three digits followed by a single alpha character and then 4 numeric digits. The output splits apart the pattern matches into adjacent cells by using the (). $1 represents the first pattern matched within the first set of ().

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

        If strPattern <> "" Then
            strInput = C.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
End Sub


results image

Additional Pattern Examples

String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit

@Kiril 2015-03-13 10:28:07

You should not forget to Set regEx = Nothing. You will get Out Of Memory exceptions, when that Sub is executed frequently enought.

@Armfoot 2015-05-13 14:58:34

I adapted example 4 with SubMatches for treating more complex regex, basically I don't use replace when splitting, if anyone is interested:…

@ZygD 2015-12-05 11:23:33

Late binding line: Set regEx = CreateObject("VBScript.RegExp")

@Zev Spitz 2015-12-18 08:45:20

This is an excellent answer, but with the following reservations. (1) This regular expression syntax should be supported by most regular expression engines, not just the VBScript regex library. (2) It's true that Excel has a unique way (other than iterating over the objects) of calling functions (in-cell functions) for use with regular expressions, but so does Access.

@user5359531 2017-02-09 17:55:14

It seems that Microsoft VBScript Regular Expressions 5.5 is not available in OS X / macOS? I do not have that option available in Microsoft Office 2016 on Mac. From what I can tell, it is pretty much impossible to get native regex support in Mac versions.

@DukeSilver 2018-07-10 22:46:42

Great answer. One improvement: I've had trouble getting the carat (^) to work as negation, but recently found this answer which uses ?!. This works for me. For instance, finding "nurse" but not "practitioner": (?=.*(nurse))(?!(?=.*(practitioner)))

@Superdooperhero 2019-02-03 15:16:50

Can one match we by just using we and how does one match a starting and ending word boundary? I tried \Bwe\B with IgnoreCase = True but no luck. Also not sure what Global does.

@Giacomo 2019-02-08 10:44:43

this answer is not useful for Excel on Mac as Microsoft VBScript Regular Expressions 5.5 doesn't seem to be available.

@Portland Runner 2019-02-08 17:39:12

@giac_man You're right, and I never claimed it was. RegExp is one of many that VBA on the Mac does not have. See this thread about RegEx on Mac

@youcantryreachingme 2019-05-07 01:34:19

I get #NAME? error. Have pasted example 2 code into VBA Project (myfilename.xlsm) > Microsoft Excel Objects > ThisWorkbook

@Portland Runner 2019-05-07 14:49:18

@youcantryreachingme - What line is the error on? Are you on windows or mac? What version of Excel? Did you include the reference library in step 1? I've tested this on several systems with no errors so I'm curious of your setup.

@youcantryreachingme 2019-05-08 22:59:51

@PortlandRunner Office 365 ProPlus Excel version 1808 Build 10730.20334. Yes I went through step 1 but on re-checking the setting today, it hasn't persisted (and my OS seems to have restarted during the last 48 hours while the laptop was sleeping). Enabled that reference again now, saved, and it persists. Code still present in location described in last comment. I enter following into a cell: "=simpleCellRegex(L496)" and get #NAME?. Cell contains an email address. I updated this strPattern to match valid emails. OS Win10 Pro 64bit (10.0 Build 17134).

@Portland Runner 2019-05-09 04:00:16

Okay, I'm pretty sure it's because the code is in ThisWorkbook. Try moving the code to a separate Module.

@youcantryreachingme 2019-05-10 05:52:01

@PortlandRunner in the "project explorer" (?) this excel file lacked a "Modules" subfolder, although another file showed one. Right-clicked the file and chose 'insert module', then double-clicked "Module 1" and pasted the code. Saved. Back to workbook and keyed in the function again - it worked. Might be noteworthy in the answer, for the sake of the inexperienced like me? Thanks for the help.

@omegastripes 2019-07-23 18:25:14

Static declaration might improve performance, check the example.

@JvdV 2020-02-12 12:37:05

What I would find a valuable contribution (a suggestion) to this post is to include what VBA does not support (or the other way around: what is supported). For example, for some reason positive/negative lookaheads are supported while lookbehinds are not. Or would this fall outside the scope over here? @PortlandRunner

@Portland Runner 2020-02-12 15:04:12

@JvdV - I agree, that would be useful information. If you would like to post an additional answer with this valuable information I'll support it :-)

@Alex Roseland 2019-11-26 20:20:20

This isn't a direct answer but may provide a more efficient alternative for your consideration. Which is that Google Sheets has several built in Regex Functions these can be very convenient and help circumvent some of the technical procedures in Excel. Obviously there are some advantages to using Excel on your PC but for the large majority of users Google Sheets will offer an identical experience and may offer some benefits in portability and sharing of documents.

They offer

REGEXEXTRACT: Extracts matching substrings according to a regular expression.

REGEXREPLACE: Replaces part of a text string with a different text string using regular expressions.

SUBSTITUTE: Replaces existing text with new text in a string.

REPLACE: Replaces part of a text string with a different text string.

You can type these directly into a cell like so and will produce whatever you'd like

=REGEXMATCH(A2, "[0-9]+")

They also work quite well in combinations with other functions such as IF statements like so:


enter image description here

Hopefully this provides a simple workaround for users who feel taunted by the VBS component of Excel.

@Portland Runner 2019-11-27 00:08:03

Thanks for sharing Alex. This is useful for those looking for Google version. You might consider writing & answering another question specific to Google Sheets Regex as it has it's own nomenclature and would be very useful to others. Regardless, you have my upvote!

@FreeSoftwareServers 2019-03-22 02:57:02

I don't want to have to enable a reference library as I need my scripts to be portable. The Dim foo As New VBScript_RegExp_55.RegExp line caused User Defined Type Not Defined errors, but I found a solution that worked for me.

What you'll want to do is put an example string in cell A1, then test your strPattern. Once that's working adjust then rng as desired.

Public Sub RegExSearch()
    Dim regexp As Object
    'Dim regex As New VBScript_RegExp_55.regexp 'Caused "User Defined Type Not Defined" Error
    Dim rng As Range, rcell As Range
    Dim strInput As String, strPattern As String

    Set regexp = CreateObject("vbscript.regexp")
    Set rng = ActiveSheet.Range("A1:A1")

    strPattern = "([a-z]{2})([0-9]{8})"
    'Search for 2 Letters then 8 Digits Eg: XY12345678 = Matched

    With regexp
        .Global = False
        .MultiLine = False
        .ignoreCase = True
        .Pattern = strPattern
    End With

    For Each rcell In rng.Cells

        If strPattern <> "" Then
            strInput = rcell.Value

            If regexp.test(strInput) Then
                MsgBox rcell & " Matched in Cell " & rcell.Address
                MsgBox "No Matches!"
            End If
        End If
End Sub

@chris neilsen 2020-02-21 01:36:31

I don't want to have to enable a reference library as I need my scripts to be portable - the VBScript_RegExp_55 library is pretty much ubiquitous so carries very low risk of not being on a particular target machine. And anyway, switching from Early Bound to Late Bound doesn't solve the portability problem (the code will still error, only at run time rather than compile time)

@FreeSoftwareServers 2020-02-21 02:49:06

No, that's not true at all, this script can be shared with another user without them enabling anything which is portable. Thanks tho

@chris neilsen 2020-02-21 03:08:50

Sure, but the RegEx code won't be usable. If the RegEx stuff is part of a larger library workbook, I guess having it not throw a compile error therefore allowing other parts to be used could be considered useful

@Vikas Gautam 2015-08-17 23:03:34

Here is my attempt:

Function RegParse(ByVal pattern As String, ByVal html As String)
    Dim regex   As RegExp
    Set regex = New RegExp

    With regex
        .IgnoreCase = True  'ignoring cases while regex engine performs the search.
        .pattern = pattern  'declaring regex pattern.
        .Global = False     'restricting regex to find only first match.

        If .Test(html) Then         'Testing if the pattern matches or not
            mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
            RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
            RegParse = "#N/A"
        End If

    End With
End Function

@DeezCashews 2017-03-18 02:41:11

I needed to use this as a cell function (like SUM or VLOOKUP) and found that it was easy to:

  1. Make sure you are in a Macro Enabled Excel File (save as xlsm).
  2. Open developer tools Alt + F11
  3. Add Microsoft VBScript Regular Expressions 5.5 as in other answers
  4. Create the following function either in workbook or in its own module:

    Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
        Dim regex As New VBScript_RegExp_55.RegExp
        Dim strInput As String
        strInput = myRange.Value
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
        REGPLACE = regex.Replace(strInput, outputPattern)
    End Function
  5. Then you can use in cell with =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ex: "A 243" to "A243")

@Thor 2017-06-01 17:47:51

This naming of outputPattern threw me off. It's the replacement value.

@DeezCashews 2017-06-01 17:50:44

Yes. I suppose I left it named pattern so it was clear it wasn't just string substitution and you could use regex matching groups like $1 $2 etc.

@jgreve 2017-07-28 21:19:35

Here is a regex_subst() function. Examples:

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

Here is the simplified code (simpler for me, anyway). I couldn't figure out how to build a suitable output pattern using the above to work like my examples:

Function regex_subst( _
     strInput As String _
   , matchPattern As String _
   , Optional ByVal replacePattern As String = "" _
) As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With

    regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function

@SAm 2017-03-30 21:28:15

Expanding on patszim's answer for those in a rush.

  1. Open Excel workbook.
  2. Alt+F11 to open VBA/Macros window.
  3. Add reference to regex under Tools then References
    ![Excel VBA Form add references
  4. and selecting Microsoft VBScript Regular Expression 5.5
    ![Excel VBA add regex reference
  5. Insert a new module (code needs to reside in the module otherwise it doesn't work).
    ![Excel VBA insert code module
  6. In the newly inserted module,
    ![Excel VBA insert code into module
  7. add the following code:

    Function RegxFunc(strInput As String, regexPattern As String) As String
        Dim regEx As New RegExp
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .pattern = regexPattern
        End With
        If regEx.Test(strInput) Then
            Set matches = regEx.Execute(strInput)
            RegxFunc = matches(0).Value
            RegxFunc = "not matched"
        End If
    End Function
  8. The regex pattern is placed in one of the cells and absolute referencing is used on it. ![Excel regex function in-cell usage Function will be tied to workbook that its created in.
    If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

@Mark Stewart 2019-06-07 14:29:28

Thanks for mentioning it needs to be in Personal.xlsb to be available in all Excel documents you work on. Most (?) other answers don't make that clear. Personal.XLSB would go in the folder (might need to create the folder) C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart folder

@Patrick Böker 2015-01-27 17:47:12

To make use of regular expressions directly in Excel formulas the following UDF (user defined function) can be of help. It more or less directly exposes regular expression functionality as an excel function.

How it works

It takes 2-3 parameters.

  1. A text to use the regular expression on.
  2. A regular expression.
  3. A format string specifying how the result should look. It can contain $0, $1, $2, and so on. $0 is the entire match, $1 and up correspond to the respective match groups in the regular expression. Defaults to $0.

Some examples

Extracting an email address:

=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+", "$0")

Results in: [email protected]

Extracting several substrings:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Results in: E-Mail: [email protected], Name: Peter Gordon

To take apart a combined string in a single cell into its components in multiple cells:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

Results in: Peter Gordon [email protected] ...

How to use

To use this UDF do the following (roughly based on this Microsoft page. They have some good additional info there!):

  1. In Excel in a Macro enabled file ('.xlsm') push ALT+F11 to open the Microsoft Visual Basic for Applications Editor.
  2. Add VBA reference to the Regular Expressions library (shamelessly copied from Portland Runners++ answer):
    1. Click on Tools -> References (please excuse the german screenshot) Tools -> References
    2. Find Microsoft VBScript Regular Expressions 5.5 in the list and tick the checkbox next to it.
    3. Click OK.
  3. Click on Insert Module. If you give your module a different name make sure the Module does not have the same name as the UDF below (e.g. naming the Module Regex and the function regex causes #NAME! errors).

    Second icon in the icon row -> Module

  4. In the big text window in the middle insert the following:

    Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
        Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
        Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
        Dim replaceNumber As Integer
        With inputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
        With outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        End With
        With outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With
        Set inputMatches = inputRegexObj.Execute(strInput)
        If inputMatches.Count = 0 Then
            regex = False
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            For Each replaceMatch In replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber
                If replaceNumber = 0 Then
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                    If replaceNumber > inputMatches(0).SubMatches.Count Then
                        'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Exit Function
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            regex = outputPattern
        End If
    End Function
  5. Save and close the Microsoft Visual Basic for Applications Editor window.

@Chris Hunt 2015-02-24 19:03:36

This answer combined with the steps here to create an Add-In, has been very helpful. Thank you. Make sure you don't give your module and function the same name!

@Chris 2015-09-28 14:57:13

Just reiterating the comment above from Chris Hunt. Don't call your Module 'Regex' as well. Thought I was going mad for a while as the function wouldn't work due to a #NAME error

@Enissay 2016-08-15 20:46:26

Well, I'm gone nuts as I tried everything (including changing modules/names) and still getting the #NAME error >_>

@Patrick Böker 2016-08-16 07:27:39

@Enissay: Try creating a minimal Function foo() As Variant \n foo="Hello World" \n End Function UDF to see if that works. If yes, work your way up to the full thing above, if no something basic is broken (macros disabled?).

@Vijay 2017-04-27 09:24:24

I would recommend This tool it works better than the above version, there are flaws in the above version, doesn't work well with multiline matching. And it is an addin so easier to use.

@Dante 2020-04-12 16:52:29

Anyone know what might have changed in three years? Today, with Excel 2003 build 12624.20382 (part of Office Pro Plus 2016 - fully updated; install in Win10 Pro OS version 1909 build 18363.720), all 5 examples above uniformly return the zero (0). No errors, just the number zero (0). Too bad regex is not a native part of such a ubiquitous application.

@Vadim 2020-04-28 18:30:26

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] What is a non-capturing group in regular expressions?

43 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1095787 View
  • 1892 Score
  • 43 Answer
  • Tags:   c# .net excel file-io

73 Answered Questions

30 Answered Questions

[SOLVED] Regular expression to match a line that doesn't contain a word

21 Answered Questions

[SOLVED] How do you access the matched groups in a JavaScript regular expression?

  • 2009-01-11 07:21:20
  • nickf
  • 770070 View
  • 1369 Score
  • 21 Answer
  • Tags:   javascript regex

18 Answered Questions

[SOLVED] Regular Expression for alphanumeric and underscores

  • 2008-12-03 04:25:27
  • Jim
  • 1095930 View
  • 585 Score
  • 18 Answer
  • Tags:   regex

8 Answered Questions

[SOLVED] Is there a regular expression to detect a valid regular expression?

  • 2008-10-05 17:07:35
  • psytek
  • 208415 View
  • 1007 Score
  • 8 Answer
  • Tags:   regex

20 Answered Questions

[SOLVED] How do you use a variable in a regular expression?

  • 2009-01-30 00:11:05
  • JC Grubbs
  • 762880 View
  • 1380 Score
  • 20 Answer
  • Tags:   javascript regex

12 Answered Questions

[SOLVED] Regular Expressions: Is there an AND operator?

  • 2009-01-22 16:49:14
  • Hugoware
  • 768091 View
  • 708 Score
  • 12 Answer
  • Tags:   regex lookahead

2 Answered Questions

[SOLVED] Regular Expressions in Excel VBA

Sponsored Content