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.
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.
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.
This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.
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 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.
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;
Extract the domain including the URL protocol.
Extract the domain ignoring URL protocol.
Extract the domain without the www.
Extract root URL’s that don’t end with a slash (/).
Using helper columns to break up complex formulae.