A MAC (Media Access Control) address is a unique identifier assigned to most network adapters. Two common IEEE 802 standards display a MAC address in 6 groups of 2 hexadecimal digits separated by a colon (:) or hyphen (-) like this:
"01-23-45-67-89-ab"
"01:...Read moreIn this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to. The challenge is that the code may be in any one of the six columns, and...Read more
The goal in this example is to use a formula to report which things exist in a cell. The list of things to check for is in the named range things (E5:E9). The result is returned as a comma separated text string.
The first step in...Read more
In this example, the goal is to return an array with 7 subtotals, one for each day of the week, as seen in columns C:I. The numbers to sum are contained in data which is the named range C5:I13. This is an example of a problem where the goal is...Read more
Excel does not provide a formula function to append or combine ranges, either horizontally or vertically. You can use Power Query for this task, and this makes sense for data transformations that must be automated and repeated on an on-going basis. However, you can also use the...Read more
In this example, the goal is to return an array with nine subtotals, one for each of the colors named in column B. The numbers to sum are contained in data which is the named range C5:I13. This is an example of a problem where the goal is to...Read more
Although FILTER is commonly used to filter rows, you can also filter columns. The trick is to use an include argument that operates on columns instead of rows. In this example, we use a hard-coded array constant to filter out unwanted columns, but...Read more
This is an experimental formula to strip characters from text. The experimental part is using character codes instead of regular characters as a way to make the formula case-sensitive, and providing a way to reverse the logic of the...Read more
The length of a line can be calculated with the distance formula, which looks like this:
...Read more