A vulnerability scanner like Nessus or Qualys will record the MAC address of every computer it finds. But Qualys doesn’t output the MAC address in a nice column format. It mixes a lot of other data into the cell. So I had to figure out how to extract a MAC address from Excel data to give an infrastructure team an inventory they wanted.
MAC addresses are 17 characters long, six hexadecimal numbers usually separated by a colon. To extract the MAC addresses buried in a mess of other data, you can use this formula:
=MID(K2,SEARCH(“??:??:??:??:??:??”,K2),17)
This formula assumes your data is in column K. If it’s not, replace K2 with the correct cell, then fill down. This formula does no error checking, relying instead on Excel’s wildcards, but should work well enough for these purposes. Note that I use the SEARCH function, not the FIND function. The FIND function doesn’t support wildcards.
When you’re done, you may want to highlight that column, copy it, then do a paste special and select values. This will replace your formula with the plaintext, so you can copy and paste directly out of Excel without accidentally copying your formula instead of the data you wanted.
In case you’re interested in extracting this data from Qualys scan data, QIDs 43007, 45232 and 78004 are the ones you’re after, and the data is in a column named Results. Note that the Qualys QIDs generally require an authenticated scan to get MAC addresses.
How the formula works to extract a MAC address from Excel data
The formula uses two different functions to extract a MAC address.
MID takes a substring out of the middle of a string. Normally it takes two arguments: the cell, how far into the cell to extract from, and the length of the substring to extract. The problem is, we don’t necessarily know how far into the cell the MAC address starts. So we use the SEARCH function to find it. It happens the SEARCH function returns the position of the match. So all we need is to use the SEARCH function to look for something that looks like a MAC address, and that works as the second value for MID. The third value MID needs is the number 17, since MAC addresses are 17 characters long.
I’ve done a similar trick before to extract the first three octets of an IPv4 IP address. That formula is a little simpler.
David Farquhar is a computer security professional, entrepreneur, and author. He started his career as a part-time computer technician in 1994, worked his way up to system administrator by 1997, and has specialized in vulnerability management since 2013. He invests in real estate on the side and his hobbies include O gauge trains, baseball cards, and retro computers and video games. A University of Missouri graduate, he holds CISSP and Security+ certifications. He lives in St. Louis with his family.