Regular Expressions Excel Add-In

For my internship over the past several months I’ve been working in the Marketing Science department and part of my job has been getting data into MS Access and generating reports. This involves getting lists of prospects from various data sources. This was usually a pretty simple feat involving some basic SQL queries. However, sometimes I was handed data such as addresses that didn’t match any standard format used by IT. In the worst case the data was provided in a pdf which meant I could only export it to a non-delimited text file. I found that I really needed a couple generic regular expression functions to parse out fields to import into MS Access. I found some .xla examples online but I really wanted an easier to use, more extensive, and portable library. I also wanted to include a few basic patterns for so it wasn’t necessary to re-invent the wheel every time.

So, I created a simple Excel Add-In Regular Expressions.xla that adds several custom functions to implement the standard VBScript regular expressions.

Installation & Use


Step 1: After downloading Regular Expressions.xla you can enable it by navigating to Tools >> Add-Ins.

Step 2: In the dialog that appears click Browse and select Regular Expressions.xla in the directory you downloaded it to. After enabling the Add-In you can disable it at any time by reopening this dialog and un-checking the box.

Step 3: To Insert a function select a cell and click the button to bring up the dialog. All of the functions and pre-defined regular expression patterns will be listed under the Regular Expressions category.

Function Descriptions

  • xREPLACE: Replace all portions of the search text matching the pattern with the replacement text.
  • xMATCHES: Find and return the number of matches to a pattern in the search text.
  • xMATCH: Find and return an instance of a match to the pattern in the search text. MatchIndex may be used in the case of multiple matches.
  • xMATCHALL: Find and return a comma-separated list of all matches to the pattern in the search text.
  • xGROUP: Find and return a group from within a matched pattern.
  • xSTARTSWITH: Returns true or false if the search text starts with the pattern.
  • xENDSWITH: Returns true or false if the search text ends with the pattern.

Parameter Descriptions

  • pattern (text): the regular expression to use to match. Regular Expression Syntax
  • searchText (text): the text to search for a match.
  • replacementText (text): the string to replace the matched portion of the string with.
  • ignoreCase (true/false): Set to false for a case-sensitive match. The default value is true.
  • matchIndex (integer): When multiple matches are found, which one should be returned (1+)
  • group (integer): Which subgroup (section within parenthesis) of the regular expression should be returned.

So far I have added basic regular expressions for matching phone numbers, 5 & 9 digit zip codes, emails, and urls. If you have any other useful, generic patterns you can send them to me at: contact@malcolmp.com and I may add them. I used a standard GNU license for this so you are free to re-distribute it and I’d appreciate you letting me know of any improvements so I can add them myself.

Download: Regular Expressions.xla

This entry was posted in Code and tagged , , , , , . Bookmark the permalink.

19 Responses to Regular Expressions Excel Add-In

  1. neki says:

    Thanks for this. I only have a little problem – every time I quit Excel I am asked if I would like to save ‘Regular Expressions.xla’. What do I do wrong?

  2. crazedazn203 says:

    This saved a lot of time for me. Thanks for posting it!

  3. rafael giusti says:

    Waoo Thanks a Lot, what a great solution. It Works smoothly.

    This functions should be included by default in Excel >:\

    Thanks again!!

  4. Frédéric From France says:

    Thansk a lot. It works perfectly.

  5. Michael O'Leary says:

    You beaut! You saved me a chunk of time there writing a module for this

  6. Andy Langton says:

    Really great work here, and a massive timesaver. Can’t beat regular expressions, and I’ve always been surprised that there’s no built in regex functions in excel :)

  7. NTMS says:

    Hi,

    How do I use =xxEMAIL() function?
    In cell G2 I have an e-mail address and in cell J2 I want to check if G2 in e-mail format.
    ==xxEMAIL(G2) gives me an error #Value!

    Can you help?

    Kınd Regards,

    • Malcolm Poindexter says:

      Ya, I’ve been meaning to refactor this and expand it a bit.

      xxEMAIL(), xxPHONE(), xxURL() are just patterns that can be passed to Match, Replace

      Here’s what you use in J2 to match an email:
      xMATCHES(xxEMAIL(),G2,TRUE)

  8. peter says:

    Just to save others some time looking for the answer:

    To use a ‘saved’ match in the replacement string (one that was matched in the regular expression and enclosed with parantheses), use the $ symbol.

    For e.g. our string is “catfood is good”
    Our find expression: ^cat(food)
    Replacement string: $1
    The resulting output in the cell will be: food

    The part of the matched string that was enclosed in parantheses (in this case “food”) was saved in the variable $1. This also works for several saved matches, e.g. $2 etc..)

  9. Matthijs says:

    This is a great tool, thanks. I just ran into one limitation, namely that it is not able to distinct between upper and lower case characters.

    Let’s say for instance that I have a string that ends with two capital letters and I try:
    xMATCH(“[A-Z]{2}$”, xyz) it will return yz, though it only should return something if YZ are upper case.

    Any help would be appreciated!

  10. Richard Benson says:

    Works great, thanks! Any chance you could add tool tips when typing manually? Would be good to get reminded of the parameters without having to go to the wizard.

    • Seb says:

      Great add-in, thanks! Works flawlessly in Excel 2013 so far.
      I second Richard Benson’s comment: it would be great to see a tooltip reminding the expected parameters.

  11. David says:

    I’ve only just found this, but already it’s saved me a shed load of time. Excellent!

  12. Hi Malcolm,

    many thanks for your great Add-In. Anyway I ran into an issue where the function returns an value error: =xMATCHES(“page\=/D\d+”;B88:B91)

    I’m working with Excel 2007. Do you have idea?

    Kind regards
    Mike

  13. Michael says:

    In xGroup function can you have more than one group number? In normal regular expressions you can use \2\1 etc to backreference what is in the ().

  14. Michele says:

    Hi,
    i have two columns, 1st containing regex and 2nd contaning pattern to be matched by regex…
    is it possible to search for every single cell of 2nd column all the cell of 1st column to find some match?

    ex.
    1st col:
    23*
    24*
    25*
    26*
    2nd col:
    23000
    27000

    as output i would like to have something like 3rd column:
    23*
    not matched

    thanks in advance

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>