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:
- In Excel, hit alt-F11 to bring up the code screen.
- Choose Tools | References and check Microsoft VBScript Regular Expressions 5.5
- Close Excel.
- Download and install Morefunc 5 from download.cnet.com.
- 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:
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/.
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. :(
Here's a library of useful excel.regex
http://ramblings.mcpher.com/Home/excelquirks/regular-expressions
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
Post a Comment