Excel Formula to Extract Postcode from an Address

I was asked recently in class how to extract a postcode from an address in the UK. The person asking needed a formula because the spreadsheet updates often and they wanted an automated solution.

The problem with extracting UK postcodes is that they are highly irregular. They will be at the end of the full address and can come in a different number of characters e.g. E1 6AX, RM3 8HN and LE41 8JX.

They are not as structured as a US zip code may be and harder to extract. Because of this the formula is intense, but I am going to break it down and explain it in detail.

Extract postcode from UK address with Excel formula

Continue reading

Display a Symbol Conditionally using the IF Function

This blog post looks at using the IF function to display a symbol conditionally in a cell. In the image below a thumbs up or thumbs down symbol is shown dependent upon whether the sale of products have improved since last month.

This tutorial will show you how to display any symbol though, so you could insert a smiley face, hour glass, aeroplane and much more.

Display thumbs up symbol using IF function Continue reading

Excel Formula to Find the Least Frequent Value

This tutorial explains an Excel formula to find the least frequent value in a list. This formula will work whether the value is a number, or text. In this example we want to return the name that occurs the least.

The spreadsheet below shows a list of names with the answer in cell D2. Ross is the name that occurs the least in that list.

Excel formula for least frequent value

This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.

{=INDEX(A2:A16,MATCH(MIN(COUNTIF(A2:A16,A2:A16)),COUNTIF(A2:A16,A2:A16),0))}

Continue reading

Count How Many Fridays Between Two Dates

You may need to count the occurrences of a specific weekday between two dates in Excel. For example, how many Fridays between now and a specific date.

There is no real standalone function in Excel to do this, but it can be done. This could be a useful formula to find how many payments, or how many meetings until an end date.

How Many Fridays Until a Specific Date – Excel Formula

The formula below calculates the number of Fridays between the date in cell A2 and the date in cell B2. The formula is explained below.

=B2-A2-NETWORKDAYS.INTL(A2,B2,16)+1

Count the number of fridays

Continue reading

Excel Formula to Display the Sheet Name in a Cell

This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name.

For the formula we will be using the CELL, MID and FIND functions. Let’s begin by looking at the CELL function.

The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, whether it is in a protected state or the filename. Continue reading

Euro 2016 Planner – Excel Template

Euro 2016 is here and I have a Euro 2016 planner to share. The spreadsheet has a separate sheet for the group stage and the knockout stage. Enter the results into the boxes on the sheets and everything is calculated automatically.

The group league tables are ranked as the results are entered. As soon as a groups fixtures have been completed, the knockout schedule is created. The quarters, semi-final and final fixtures are then calculated as these results are entered.

The spreadsheet is unlocked so you are free to check out the formulas and how it all works. Download the Euro 2016 planner spreadsheet.

Euro 2016 Planner Template
Continue reading

Use VLOOKUP to Find the Last Match in a List

VLOOKUP is an awesome Excel function. We all know this. But it certainly has its limitations. One of these is that it can only return the first match from a list.

This is great when looking for a unique value. But what about when the value you are looking for appears multiple times in the list, and you want to return the last match.

Sure we could sort the list so that the last match would become the first, but this is not always an option.

This blog post looks at using the VLOOKUP function to return the last match in a list. The technique and formulas used can be adapted to find the 2nd or 3rd match if required.

VLOOKUP to find last match

Continue reading

Extract the Domain from a List of URL’s

If you are responsible for analysing a list of URL’s in Excel, then you may need to extract the domain from each URL. After extracting the domain you can perform useful reporting tasks such as grouping the domains, or counting the number of unique domains in the list.

This can be quite a difficult task especially if the URL’s are not consistent. In this article we explore a few different approaches to extract the domain from a URL. The method you choose will depend on the appearance of the URL’s in the list and the format you would like to extract the domain in.

This article covers;

  1. Extract the domain including the URL protocol.
  2. Extract the domain ignoring URL protocol.
  3. Extract the domain without the www.
  4. Extract root URL’s that don’t end with a slash (/).
  5. Using helper columns to break up complex formulae.

Continue reading

Pick a Name at Random from a List

In this blog post we look at how to pick a name at random from a list. There are many reasons you may need this kind of random selection from a list.

It may be a neat way of deciding who is next to do the washing up, or to make the tea :). Or it is useful for randomly drawing teams for a knockout cup competition.

To perform this random selection, we are going to use two different functions together. They are INDEX and RANDBETWEEN.

Continue reading

How to Use the TEXT Function in Excel

TEXT Function Examples and Description

The TEXT function is used to convert a numeric value into text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.

Take the example below, where the following formula has been entered into cell C2. The date formula loses its format when concatenated. This is now useless.

=A2&" - "&B2

Date loses formatting when concatenating Continue reading