Thursday, March 11, 2010

Regular Expressions in Excel

Once I learned a bit about regular expressions in Actionscript 2, I wanted to use them in Microsoft Excel.

It turns out that there is a VBA extension for Regular Expressions and a installable set of custom functions for Excel that includes regular expressions. To make it work, here is what I did:
  1. In Excel, hit alt-F11 to bring up the code screen.
  2. Choose Tools | References and check Microsoft VBScript Regular Expressions 5.5
  3. Close Excel.
  4. Download and install Morefunc 5 from download.cnet.com.
  5. Open Excel and start writing functions like "=REGEX.SUBSTITUTE(V2, " .......$| ....... |^....... |^......$|^.......$"," ???????? ")"


To learn more, check out the Morefunc documentation, sites like this, or sites that I referred to in my previous post. You can also write your own functions like lispy does.

Open Question: What do Excel for Mac users do?

Pretty soon you will be ordering this cup:

4 comments:

Ross Isenegger said...

My first user-defined function is one I use all the time to create semi-colon separated strings of email addresses. I found it at http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/.

Matt Penner said...

Alas, it is not compatible with Excel 2010. The install didn't put anything in the Add-ins after manually adding it to Excel some functions work and some don't. for instance, while Regex.Find works just fine Regex.Len simply returns a #NUM error. :(

bruce mcpherson said...

Here's a library of useful excel.regex

http://ramblings.mcpher.com/Home/excelquirks/regular-expressions

Ross Isenegger said...

I have found one approach that works in Excel 2010: use IronSpread to program in Python against an Excel sheet and have access to Python's re package. See http://mathfest.blogspot.ca/2012/06/using-ironspread-and-regular.html