Warning, this is a very geeky post / request!

Is it me or is there no easy way to manage large hex numbers, particularly MAC and WWN addresses in Excel?

As I'm feeling lazy, I need to manage a large number of network MAC addresses and FC WWN addresses using minimal work. Particularly, I'd like to have a formula so that when I enter the MAC or WWN of a dual port network/FC card, the 2nd port's address is automatically filled in. It's always a fixed offset of one or two.

Of course, if life were in base 10, everything would be easy, but as we're talking hex, it's a little tricker. There are the excel functions of hex2dec and dec2hex, so that could have been a solution, but it's limited to 10 digits / 40 bits which means MACs and WWNs are out of scope

I've done a quick trawl on the net and there does appear to be a suggestion or two involving VB which I suppose is inevitable, but it's a bit beyond my abilities

Alternatively, is anyone aware of a freeware program that allows us to do this kind of stuff and then import / export into Excel?

Thanks

Here is your thread

This my head

Sorry xav I have no idea but you got a bump outta me

Is the offset you need to change just the last two digits? Could you split those off, use the hex/dec conversion functions, increment the value and then reconvert and append them back to get around the size limit?

Is the offset you need to change just the last two digits? Could you split those off, use the hex/dec conversion functions, increment the value and then reconvert and append them back to get around the size limit?

I did think of that, but was worried in case the change which is only of one or two would mean changing more than the last 2 digits.

But on second thoughts, I don't think that's really the case. Will check and have a play. Thanks

I've just whipped this up as an example for you

= REPLACE(A1,(LEN(A1)-1),2,(dec2hex(HEX2DEC(RIGHT(A1,2))+2)))

That assumes the following:

- the original value is in (A1)

- you want to add 2 to the address (the last parameter in the hex2dec call)

It has issues in that it will add an extra digit for values of the last hex pair greater than FD, and if that value is less than 0F then it'll drop the first digit during conversion. Nothing a bit of validation/function tweaking wouldn't fix though, I just haven't looked it up yet It'll accept any input length because of the LEN calculation so tweaking multiple different values shouldn't be an issue.

At least that shows that an inline function without VB is perfectly do-able, and should save you some headaches

EDIT: Actually, now I think about it - if you're worried that an increment of the 3rd LSD may happen, just strip the last three digits instead of two and do the maths on those instead.

*bangs head on table*

Edited 10 October, 200817 yr by Interphase

Don't forget if you change a controller card you can get a totally different MAC. Also depending on the item in question the MAC/WWN can sometimes be attributed to the SFP rather than the controller card itself.

Yup, all too aware of changing of NICs or FC cards causing changed MAC / WWN addresses.

Hopefully, this will be an issue of the past as we're now looking at HP blade servers and their virtual MAC / WWN addressing is :drool: nice in terms of addressing and management:D