Hex incrementing for MAC addresses

Hello,

We have to create 5000 MAC addresses for product and I'm trying to figure out how to get Excel to automate this. We currently have a hundred or so addresses in this format - D81C14000000004B...and we need to not only increment them, but conjugate them in this format - D8:1C:14:00:00:00:00:4B.

In addition to this, we'll have another set of numbers that needs to be incremented which are also hex numbers and they'll need to be added to the first string separated by a period and then added to a text file.

I'm foreseeing some crazy macros and having to create unique scripting...but then again, I don't know enough about Excel to figure it out...therefore I'm reaching out to the experts.

Thanks much!

Hello jsamuelshn

Find the attached that may work for you. There is a formula for putting colon in your hex numbers. Also a possible way to get progressively larger addresses in Hex.

Hi Marvin,

that is great!! Thank you so much for the solution and such a quick reply!

The only issue I see when working with it is that when I get past the last pair of digits, in the concatenated column, it doesn't seem to be incrementing the concatenated numbers. Maybe I can play around with the formula, but if you have any ideas on how to fix that I would greatly appreciate it.

Thanks again!

Oh, hi again...

I also noticed that as I increment further the mac address is adding numbers rather than incrementing with what is there. So, instead of incrementing D81C1400000000FF to D81C140000000100, it's adding to the end as D81C1400000000100

I can kind of tell from the formula why it's doing that...but would there be another way to set up the formula so it wouldn't be necessary to change the formula after the 2 digits have cycled through?

Like I see it as =LEFT(A76,14)& DEC2HEX(D77), but I'm guessing that it would need to be =LEFT(A77,13)& DEC2HEX(D78) to get the next one to increment properly?

Thanks much!

Hi jsamuelshn,

You were correct about your suggestion above. Instead of uisng the left 14, I used the left 12 and concatinated a Dec2Hex number. Excel has helped us again as it allows a second argument in the Dec2Hex function of "places". This allowed dec2hex to add leading zeros so it was easy.

See the attached that will allow you to add many more addresses.

That's awesome Marvin!

Thanks again...this is going to save a lot of time.

Now, I'm trying to take what you've done and recreate it with another aspect of what I need.

I need to increment this hex number 01091083107500000000 in the same fashion, but when I input that number in excel it drops the first 0, therefore screwing up the whole thing. I've tried formatting the cell to all kinds of different types, but nothing seems to be working.

Any ideas?

And while you're in such a helpful mood ;-)...maybe I can ask if you could help me figure out how to set up a macro to get the Mac address and this other hex number to be input in a text file separated by a comma? Haha! Sounds like it should be easy, but by the time I figure out how to do it...it may take too much time and we're kind of on a deadline.

Thanks again friend!

Oh hey...just an update, I thought I had tried text format before, but the box that showed the example didn't change, so I didn't do it. that worked.

So, if you have any thoughts on the macro...that'd be all I need to complete the project I think.

Thanks again!

Thank you again Marvin,

I have everything I need now.

Best regards!

Thank you Marvin!

I know this is an old thread, but I needed to make a quick list of 700 MAC addresses, your Excel sheet was the perfect solution.
I just wanted to let you know how helpful your solution was even after all these years.
Thank you so much!

-Ryan

Hey Guys! I know this is an old thread but a really easy fix I found to incrementing is to convert the MAC address to a decimal, add 1, and convert it back to a Hex.

My example only increments the right 5. That is all we are worried about. Easily changed be adjusting the LEFT and RIGHT values.

=LEFT(A1,7)&DEC2HEX(HEX2DEC(RIGHT(A1,5))+1)

This will allow you to increment MAC address' with ease!

Enjoy!

Drew