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.
In this timesheet, the day and night shifts are entered on separate rows. This will make it easier for our calculations. A column (column E) is also used to record whether it is a day or night shift.
Our first task is to calculate the number of hours worked on the shift. To do this the following formula can be used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.
If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used.
Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.
This article looks at 4 advanced techniques for summing values including;
Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup to return a picture dependent upon the contents of a cell.
This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.
In this blog post we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.
A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.
This article looks at four techniques for separating text. Use the links below to jump to a specific technique.