Showing posts with label regular expression. Show all posts
Showing posts with label regular expression. Show all posts

Tuesday, June 19, 2012

Using IronSpread and Regular Expressions in Excel

I get a lot of traffic on this blog related to Regular Expressions in Excel, much of it from http://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function.  There are comments there that indicate that it does not work with Office 2010.

I stumbled upon www.ironspread.com, which allows you to use Python to "program" Excel 2007 and 2010 sheets.  Python has excellent support for Regular Expressions.  It looks like there are other libraries for Python that can be used with Excel and OpenOffice; PyUNO for one.

I quickly cooked up the following example using a regular expression for valid email addresses found at http://www.regular-expressions.info/email.html.

The Excel sheet looks like:


The Python program looks like:

 # see http://www.regular-expressions.info/email.html for discussion about  
 # regular expressions to check email addresses  
 import re  
 emailRE = r"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)\b"  
 for i in range(1, 100):  
   currCellValue = Cell(i,1).value  
   if currCellValue == 0:  
     break  
   try:  
     m = re.match(emailRE, currCellValue).group(0)  
     if m == currCellValue:  
       # matches full address  
       Cell(i,2).value = ""  
       Cell(i,3).value = ""  
     else:  
       # matches part of address  
       Cell(i,2).value = "Suggestion"  
       Cell(i,3).value = m  
   except:  
     # no match  
     Cell(i,2).value = "Invalid"  
     Cell(i,3).value = ""  

and once finished, columns B and C are filled in


I am a novice programmer in this environment, so I hope there are no glaring newbie mistakes and that the example is illustrative.

IronSpread installs a Python editor, IDLE, and a command shell which can be used to test your work.  I still love Sublime Text 2 as an editor, so give a command like:
execfile("c:/Users/Ross/Documents/EmailAddresses.py")
in the shell to run the program saved by Sublime.  Any print statements will output to the shell.  I have linked to the Excel Sheet and Python program above if you want to try them out.

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:

Regular Expressions in Actionscript 2

Regular Expressions are a powerful method of working with text strings based on dynamic patterns. For example, you might want to extract all the numbers from a string like "Michael travels 550km in 6.2 hours using 40L of gasoline", or replace any occurrence of "INV" followed by an arbitrary number of alphanumerics followed by a space with "- reference removed -".

Regular Expressions are useful in parsing user input or creating grammars for strings.

Flash decided not to support a RegExp class in Actionscript 2, though it is implemented in Actionscript 3, Javascript, and JScript.

Pavils Jurjans
bemoaned this fact and created his own RegExp class for Flash 5, which was encapsulated by Joey Lott into a .as file for Actionscript 2. Pavils also created a very handy tester, which together with websina's page, helped me to get started with regular expressions.


The CLIPS programmers have started to use our version of the class, which has been documented with ZenDocs, and includes a trim, rtrim, ltrim and slightly modified replace method. A simple tester .fla is available to interact with the class.

I probably feel as excited learning this new skill as the fellow in this xkcd.com comic.