I came across a need to use regular expressions in a spreadsheet today. Usually in this case I would look at using a more powerful tool such as Resolver One, which I have added into my toolbox and used for many side projects, but in this case I was stuck using Excel for legacy reasons. Sadly even the latest version of Excel 2010 does not have built in support for Regular Expressions so you either have to drop into VBA code and write your own Regex functions or install an add-on that has pre-built functions. This blog post brought me to a nice free add-on for Excel called Morefunc which has a bunch of Regex functions, but it doesn’t work right out of the box with Excel 2010. After installing it I had to follow these instructions to manually add the Morefunc.xll, Morefunc11.xla, and Morefunc12.xlam files from the “C:\Program Files\Morefunc” folder. Once I did that and allowed macros on the spreadsheet then the new functions showed up under the Morefunc category and under the Functions section of the Formula ribbon group. Since I didn’t see these instructions posted anywhere else I figured I would post them here so that others could find them.
Enjoy!
Posts
4 comments:
Hey Greg,
I just found Morefunc too and was pretty excited. Once I did the manual install much like you did, however, I found that some functions worked and some didn't. Did you find this?
For instance, on a blank spreadsheet I enter "abc123" into cell A1. On cell B1 I enter =REGEX.FIND(A1, "b") which correctly returns 2. On cell C1 I enter =REGEX.LEN(A1, "b") which gives an Excel #NUM! error. :(
I'll have to keep looking. Unfortunately I don't see any source code. Perhaps this is an easy fix but I see no real work on the project since 2007. That's too bad.
I would love to hear if you find anything else that works for you.
I can replicate your issue: It crashes Excel 2010 when I complete the steps you outlined. MoreFunc is a bit rough around the edges, but for simple Regex search or replace it meet my needs. Anything more complex than that might be a better fit for a VBA macro or module. Better yet, use a more powerful tool like Resolver One, which has native RegEx support.
Another option would be to avoid methods that cause a crash or provide invalid results. For instance, you can probably use =LEN(REGEX.MID(A1,"b")) instead of =REGEX.LEN(A1, "b").
This might also yield another solution.
I used to have Excel 2003 and now upgraded to Excel 2010. I have an old Excel 2003 file with Array.Join funtion such as =SMALL(IF(ARRAY.JOIN(H7,H19,H31,H43)<>0,ARRAY.JOIN(H7,H19,H31,H43)),1)
I can't open up the Excel 2010 file with Array.Join function. It crashes all the time when I have the Morefunc.xll, Morefunc11.xla, and Morefunc12.xlam turn-on.
If I turn off the Morefunc.xll, Morefunc11.xla, and Morefunc12.xlam in Excel 2010, then it will open up the file, but the formula Array.Join no longer work
Do you know why? How can my make MoreFunc work in Excel 2010?
Sorry... I haven't worked with Excel 2010 and Morefunc much. You might try asking on http://superuser.com/ and see if anyone there has some suggestions.
Post a Comment