Times like these

Sometimes you just want to get something ‘easy’ done quickly and look around to find the appropriate tooling to do so. Quickly because done by hand it will take a lifetime, a lifetime I rather spend on different things.

In many cases you`ll find Excel as a perfect tool to quickly evaluate, filter or alter datasets to your liking. Even though Excel offers a bunch of neat little functions that can get allot done, it will clutter up quickly. For instance this example, extract the fully qualified domain name and port from a well formatted URL, for example: https://myweblogichost.domain.ext:7001/.

=MID(B2;(FIND("/"; B2; 1)+2);((FIND("/"; B2; (FIND("/"; B2; 1)+2)))-(FIND("/"; B2; 1)+2)))

Sadly sometimes you need pretty much creativity to mock an function like this up. Most users might never come to a working example and keep tinkering days after end. Its times like these I wonder why Excel doesn’t simply support regular Expressions. Oke, oke, Excel does support Regular Expressions when you enable the correct VBA lib through the developer console.

Why write this? Native support for regular expressions would make our lives so much easier. To give you an example, the function above could have been replaced with:


This little regular expression means: Look for ‘http’ at the ‘^’ start of the string, that might or might not (?) be followed by an ‘s’.  then the string ‘://’ should follow. After that start capturing ‘(‘ any char ‘.’ with one or more ‘+’ repetitions. Stop capturing ‘)’ when you find ‘/’ followed by any char ‘.’ with one or more ‘+’ repetitions. Finally return the captured string (usually done by the function handling Regular Expressions).

If you ask me its readable, understandable and logical. Why Microsoft does not implement this natively in Excel still eludes me.

I really hope Microsoft at one point will implement this.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s