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.

 

Square or any Root programatically?

Ever wondered how to programatically get the root of any number programatically?

A square root of any number in excel can be found using the function “=SQRT(#);” where # is the number you want to have the root from. When you test this with the number 3 (commonly used to calculate complex “real” power consumption of a three fase system) You should get this as an result.

1,732050808

Because a “square” root is the opposite of a square power from that number you can use the following to find the same root using that same power ^ sign.

Square Root = 3^(1/2) = SQRT(#) = 3^0.5

If you add this example in excel as a check value.

=3^(1/2)

You will notice that the answer is also

1,732050808

Using the formula for complex power calculation in PHP will give you

$Cosphi = ($Cosphi > 1) ? "1" : $Cosphi;
$Phases = ($Phases < 2) ? "2" : $Phases;

$Pw = $U . $I . $Cosphi . ($Phases^(1/2));

Yea! Its that easy!! 😉