Manipulating strings in Excel.

As you will know, Excel very quickly runs of out steam as you try to do any kind of string manipulation with cells. Even very basic requirements cause you reach for VBA, but then you end up with very complex functions highly specific to each string processing task.  Many tools nowadays, especially in the Unix/Linux world allow you to use “Regular Expressions” right as part of their basic functionality. This section will show you how to implement Regular Expressions (RegEX) that you can use both in your workbooks and in your VBA procedures to harness their string processing capabilities, as well as a way to start a RegEx library that you can build up with common tasks. The examples and procedures can all be downloaded. Note that there are many regexes already written on sites such as, http://regexlib.com . Before writing your own, I recommend you check to see if anyone has already done something like it. Many of the examples here come from such libraries.

What is a Regular Expression. 

These are commonly called RegEX, which is how I will refer to them from now on. A regEX is a pattern that can be applied to a string, either to test or transform a string, depending on whether there is a match according to rules contained in that pattern. As with most things that come up through the Unix route, RegEX syntax is both powerful, and concise. This section is not supposed to be a tutorial on RegEx- there are thousands of them available on the internet. Just google "regular expression tutorial". Some of these examples have come from a great regex resource, http://regexlib.com/

Here's a few examples

So applying 'Numeric' to the string abc123  yields 123. Contrast that with a generalized formula in excel to do that.

RegEX can get also very complex - but the are expressing very complex validation concepts that would be complex in VBA and unimaginably so with Excel functions.  Here's one that describes a valid UK post code, from regexlib.com, as are a few in this section.

Aside from extracting string components, you can also use regEX to transform a string. 

This one will reduce multiple spaces inside a string to single spaces, when used in conjunction with the procedures discussed on this page

and this one will format an ip number.

Although all that looks rather complex, there are millions of already written regEX on the web you can find for almost any purpose. The regEX implementation you can download here also provides the concept of a 'preset'. The idea here is that you add regEX patterns to your own personal library and refer to them by name rather than needing to reference the patterns themselves. This makes for easy maintenance and less typing errors.

So for example, you can setup a preset which translates to a regEX pattern, but in your formula you refer to a preset name. You'll see more of that later, but for example, you could set up a preset called IPNUMBER. The formula you would use in your worksheet would be

instead of 

RegEX implementation.

There are 3 functions that a generally available in implementations of RegEX

1. Test - Check if a string matches a RegEx Pattern
2. Replace - Replace any matching string portion(s) with something else
3. Execute - Apply a RegEx pattern to a string and return the result(s) so you can manipulate them.

Excel does not have an implementation of RegEX, but VBscript does, and that is available to VBA developers simply by including a reference to it. If you are writing VBA code then you can build that right into your procedures. However, you cannot directly access it as an Excel formula so in this case you need to wrap them in some VBA user defined functions to make them accessible. In this case, I have created both a wrapper class (cRegEXLib) and some wrapper functions that access that class. Details are at the end of this article and are  available as a download.

examples

Note that the full regEX pattern and formulas may not be shown below in the interests of space, but you will find them in the example sheet in the download section.

These are the formulas for the above

Public Function rxTest(sName As String, s As String, Optional ignorecase As Boolean = True) As Boolean

 Public Function rxString(sName As String, s As String, Optional ignorecase As Boolean = True) As Boolean

Public Function rxReplace(sName As String, sFrom As String, sTo as string Optional ignorecase As Boolean = True) As Boolean

Public Function rxPattern(sName As String) As string

cRegEXLib class

 Presets

Summary