Blog Archives

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:


^https?://(.+)/.+$

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.

 

Simple php function to filter out unwanted text char types.

Just wrote a little function that will remove all char types from a string or text (if thats in the string) that are not given in the chars list. In my example I only wanted alpha chars and spaces. It also converts all to lower caps.

It walks through the given string using a substr function to locate the char to be evaluated. If the char is found in the given allowed string using a strstr function it will add it to the return string, else it will simply discard it. Before evaluation it also converts the val to a lowercap.

<?php

function cleanstr($s, $c, $l=true){
        $r='';
        $l=strlen($s);
        if($l){$s=strtolower($s);}
        for($i=0;$i<$l;$i++){
                $v=substr($s, $i, 1);
                if(strstr($c, $v)){ $r .=$v;}
        }
        return $r;
}

$string='abcdef $%%# Chris';
$chars=' abcdefhijklmnopqrstuvwxyz';
echo cleanstr($string, $chars);
?>

Prints out…

php ./test.php
abcdef  chris