Home // Blog & Excel Tips // Excel font formula

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

Excel font formula

Comments Closed
Uncategorised

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.