Excel font formula

We recently experienced a problem which required an Excel font formula to identify at which point within a cell the font size changed. We needed to separate the font size 11 from the font size 7.5 to extract the email address for an orders report (anyone using WooCommerce and trying to download these order reports will identify with this).

In our classes we always tell delegates that Excel always has a formula or function to do what you want (you are not the first person to use Excel, someone has asked Bill Gates for this feature etc). In this case we were wrong and had to build our own user defined function to allow for this excel function.

For the full example, below is what the cell looked like

Adrian MerreckAmerrick@AuditExcel.co.za

You will notice that the surname is right next door to the email address. If there was a space we could use other functions and tricks to extract the email.

However, although the formatting makes it easy for a human to see where the email address starts, Excel would see this all as one word. So we needed to create a UDF to find the character number at which the font size changes and then use LEFT, RIGHT or MID to extract the information we want. This UDF has been incorporated into our Woocommerce download orders spreadsheet, but we will shortly add the UDF.