excel VBA to find correct formula to get result from hexcode












-2














Various reverse-engineered versions of the allocation algorithmsused by different countries to allocate 24-bit ICAO addresses based
on the aircraft registration These were worked out by looking at the allocation patterns and working backwards to an algorithm that generates that pattern,spot-checking aircraft to see if it worked.
YMMV.



    registration_from_hexid = (function () {
// hide the guts in a closure

var limited_alphabet = "ABCDEFGHJKLMNPQRSTUVWXYZ"; // 24 chars; no I,O
var full_alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; // 26 chars



// handles 3-letter suffixes assigned with a regular pattern
//
// start: first hexid of range
// s1: major stride (interval between different first letters)
// s2: minor stride (interval between different second letters)
// prefix: the registration prefix
//
// optionally:
// alphabet: the alphabet to use (defaults full_alphabet)
// first: the suffix to use at the start of the range (default: AAA)
// last: the last valid suffix in the range (default: ZZZ)

var stride_mappings = [
{ start: 0x008011, s1: 26*26, s2: 26, prefix: "ZS-" },

{ start: 0x390000, s1: 1024, s2: 32, prefix: "F-G" },
{ start: 0x398000, s1: 1024, s2: 32, prefix: "F-H" },

{ start: 0x3C4421, s1: 1024, s2: 32, prefix: "D-A", first:
'AAA', last: 'OZZ' },
{ start: 0x3C0001, s1: 26*26, s2: 26, prefix: "D-A", first:
'PAA', last: 'ZZZ' },
{ start: 0x3C8421, s1: 1024, s2: 32, prefix: "D-B", first:
'AAA', last: 'OZZ' },
{ start: 0x3C2001, s1: 26*26, s2: 26, prefix: "D-B", first:
'PAA', last: 'ZZZ' },
{ start: 0x3CC000, s1: 26*26, s2: 26, prefix: "D-C" },


{ start: 0x3D04A8, s1: 26*26, s2: 26, prefix: "D-E" },


{ start: 0x3D4950, s1: 26*26, s2: 26, prefix: "D-F" },
{ start: 0x3D8DF8, s1: 26*26, s2: 26, prefix: "D-G" },
{ start: 0x3DD2A0, s1: 26*26, s2: 26, prefix: "D-H" },
{ start: 0x3E1748, s1: 26*26, s2: 26, prefix: "D-I" },

{ start: 0x448421, s1: 1024, s2: 32, prefix: "OO-" },
{ start: 0x458421, s1: 1024, s2: 32, prefix: "OY-" },
{ start: 0x460000, s1: 26*26, s2: 26, prefix: "OH-" },
{ start: 0x468421, s1: 1024, s2: 32, prefix: "SX-" },
{ start: 0x490421, s1: 1024, s2: 32, prefix: "CS-" },
{ start: 0x4A0421, s1: 1024, s2: 32, prefix: "YR-" },
{ start: 0x4B8421, s1: 1024, s2: 32, prefix: "TC-" },
{ start: 0x740421, s1: 1024, s2: 32, prefix: "JY-" },
{ start: 0x760421, s1: 1024, s2: 32, prefix: "AP-" },
{ start: 0x768421, s1: 1024, s2: 32, prefix: "9V-" },
{ start: 0x778421, s1: 1024, s2: 32, prefix: "YK-" },
{ start: 0x7C0000, s1: 1296, s2: 36, prefix: "VH-" },
{ start: 0xC00001, s1: 26*26, s2: 26, prefix: "C-F" },
{ start: 0xC044A9, s1: 26*26, s2: 26, prefix: "C-G" },
{ start: 0xE01041, s1: 4096, s2: 64, prefix: "LV-" }
];
function lookup(hexid) {
var hexid = +("0x" + hexid);

reg = n_reg(hexid);
if (reg)
return reg;

reg = ja_reg(hexid);
if (reg)
return reg;

reg = hl_reg(hexid);
if (reg)
return reg;

reg = numeric_reg(hexid);
if (reg)
return reg;

reg = stride_reg(hexid);
if (reg)
return reg;

return null;
}

function stride_reg(hexid) {
// try the mappings in stride_mappings
var i;
for (i = 0; i < stride_mappings.length; ++i) {
var mapping = stride_mappings[i];
if (hexid < mapping.start || hexid > mapping.end)
continue;

var offset = hexid - mapping.start + mapping.offset;

var i1 = Math.floor(offset / mapping.s1);
offset = offset % mapping.s1;
var i2 = Math.floor(offset / mapping.s2);
offset = offset % mapping.s2;
var i3 = offset;

if (i1 < 0 || i1 >= mapping.alphabet.length ||
i2 < 0 || i2 >= mapping.alphabet.length ||
i3 < 0 || i3 >= mapping.alphabet.length)
continue;

return mapping.prefix + mapping.alphabet.charAt(i1) +
mapping.alphabet.charAt(i2) + mapping.alphabet.charAt(i3);
}

// nothing
return null;
}


// US N-numbers
//

function n_letters(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(Math.floor(rem / 25)) +
n_letter(rem % 25);
}

function n_letter(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(rem);


function n_reg(hexid) {
var offset = hexid - 0xA00001;
if (offset < 0 || offset >= 915399) {
return null;
}

var digit1 = Math.floor(offset / 101711) + 1;
var reg = "N" + digit1;
offset = offset % 101711;
if (offset <= 600) {
// Na, NaA .. NaZ, NaAA .. NaZZ
return reg + n_letters(offset);
}

// Na0* .. Na9*
offset -= 601;

var digit2 = Math.floor(offset / 10111);
reg += digit2;
offset = offset % 10111;

if (offset <= 600) {
// Nab, NabA..NabZ, NabAA..NabZZ
return reg + n_letters(offset);
}

// Nab0* .. Nab9*
offset -= 601;

var digit3 = Math.floor(offset / 951);
reg += digit3;
offset = offset % 951;

if (offset <= 600) {
// Nabc, NabcA .. NabcZ, NabcAA .. NabcZZ
return reg + n_letters(offset);
}

// Nabc0* .. Nabc9*
offset -= 601;

var digit4 = Math.floor(offset / 35);
reg += digit4.toFixed(0);
offset = offset % 35;

if (offset <= 24) {
// Nabcd, NabcdA .. NabcdZ
return reg + n_letter(offset);
}

// Nabcd0 .. Nabcd9
offset -= 25;
return reg + offset.toFixed

(0);









share|improve this question
























  • No one understands what you're asking, please clarify
    – iamanigeeit
    Nov 23 at 9:16










  • I have a list of codes which I don't have the hex value or binary value. I was trying to work out what the formula is , as you can see some of the codes have a mixture of letters and numbers. So how does 432B get converted to Hex - A52AA5
    – Mr North
    Nov 23 at 9:44










  • @MrNorth So what I understand (but maybe I am wrong) is that you are looking for a single Excel formula that will convert the 4 codes in the Hex equivalent??
    – Peter K.
    Nov 23 at 11:43










  • yes that is correct
    – Mr North
    Nov 23 at 12:51










  • Each code has the letter N at the start , not sure if that is relevant ? N1 , N2 , N432B , N678XX
    – Mr North
    Nov 23 at 13:05
















-2














Various reverse-engineered versions of the allocation algorithmsused by different countries to allocate 24-bit ICAO addresses based
on the aircraft registration These were worked out by looking at the allocation patterns and working backwards to an algorithm that generates that pattern,spot-checking aircraft to see if it worked.
YMMV.



    registration_from_hexid = (function () {
// hide the guts in a closure

var limited_alphabet = "ABCDEFGHJKLMNPQRSTUVWXYZ"; // 24 chars; no I,O
var full_alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; // 26 chars



// handles 3-letter suffixes assigned with a regular pattern
//
// start: first hexid of range
// s1: major stride (interval between different first letters)
// s2: minor stride (interval between different second letters)
// prefix: the registration prefix
//
// optionally:
// alphabet: the alphabet to use (defaults full_alphabet)
// first: the suffix to use at the start of the range (default: AAA)
// last: the last valid suffix in the range (default: ZZZ)

var stride_mappings = [
{ start: 0x008011, s1: 26*26, s2: 26, prefix: "ZS-" },

{ start: 0x390000, s1: 1024, s2: 32, prefix: "F-G" },
{ start: 0x398000, s1: 1024, s2: 32, prefix: "F-H" },

{ start: 0x3C4421, s1: 1024, s2: 32, prefix: "D-A", first:
'AAA', last: 'OZZ' },
{ start: 0x3C0001, s1: 26*26, s2: 26, prefix: "D-A", first:
'PAA', last: 'ZZZ' },
{ start: 0x3C8421, s1: 1024, s2: 32, prefix: "D-B", first:
'AAA', last: 'OZZ' },
{ start: 0x3C2001, s1: 26*26, s2: 26, prefix: "D-B", first:
'PAA', last: 'ZZZ' },
{ start: 0x3CC000, s1: 26*26, s2: 26, prefix: "D-C" },


{ start: 0x3D04A8, s1: 26*26, s2: 26, prefix: "D-E" },


{ start: 0x3D4950, s1: 26*26, s2: 26, prefix: "D-F" },
{ start: 0x3D8DF8, s1: 26*26, s2: 26, prefix: "D-G" },
{ start: 0x3DD2A0, s1: 26*26, s2: 26, prefix: "D-H" },
{ start: 0x3E1748, s1: 26*26, s2: 26, prefix: "D-I" },

{ start: 0x448421, s1: 1024, s2: 32, prefix: "OO-" },
{ start: 0x458421, s1: 1024, s2: 32, prefix: "OY-" },
{ start: 0x460000, s1: 26*26, s2: 26, prefix: "OH-" },
{ start: 0x468421, s1: 1024, s2: 32, prefix: "SX-" },
{ start: 0x490421, s1: 1024, s2: 32, prefix: "CS-" },
{ start: 0x4A0421, s1: 1024, s2: 32, prefix: "YR-" },
{ start: 0x4B8421, s1: 1024, s2: 32, prefix: "TC-" },
{ start: 0x740421, s1: 1024, s2: 32, prefix: "JY-" },
{ start: 0x760421, s1: 1024, s2: 32, prefix: "AP-" },
{ start: 0x768421, s1: 1024, s2: 32, prefix: "9V-" },
{ start: 0x778421, s1: 1024, s2: 32, prefix: "YK-" },
{ start: 0x7C0000, s1: 1296, s2: 36, prefix: "VH-" },
{ start: 0xC00001, s1: 26*26, s2: 26, prefix: "C-F" },
{ start: 0xC044A9, s1: 26*26, s2: 26, prefix: "C-G" },
{ start: 0xE01041, s1: 4096, s2: 64, prefix: "LV-" }
];
function lookup(hexid) {
var hexid = +("0x" + hexid);

reg = n_reg(hexid);
if (reg)
return reg;

reg = ja_reg(hexid);
if (reg)
return reg;

reg = hl_reg(hexid);
if (reg)
return reg;

reg = numeric_reg(hexid);
if (reg)
return reg;

reg = stride_reg(hexid);
if (reg)
return reg;

return null;
}

function stride_reg(hexid) {
// try the mappings in stride_mappings
var i;
for (i = 0; i < stride_mappings.length; ++i) {
var mapping = stride_mappings[i];
if (hexid < mapping.start || hexid > mapping.end)
continue;

var offset = hexid - mapping.start + mapping.offset;

var i1 = Math.floor(offset / mapping.s1);
offset = offset % mapping.s1;
var i2 = Math.floor(offset / mapping.s2);
offset = offset % mapping.s2;
var i3 = offset;

if (i1 < 0 || i1 >= mapping.alphabet.length ||
i2 < 0 || i2 >= mapping.alphabet.length ||
i3 < 0 || i3 >= mapping.alphabet.length)
continue;

return mapping.prefix + mapping.alphabet.charAt(i1) +
mapping.alphabet.charAt(i2) + mapping.alphabet.charAt(i3);
}

// nothing
return null;
}


// US N-numbers
//

function n_letters(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(Math.floor(rem / 25)) +
n_letter(rem % 25);
}

function n_letter(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(rem);


function n_reg(hexid) {
var offset = hexid - 0xA00001;
if (offset < 0 || offset >= 915399) {
return null;
}

var digit1 = Math.floor(offset / 101711) + 1;
var reg = "N" + digit1;
offset = offset % 101711;
if (offset <= 600) {
// Na, NaA .. NaZ, NaAA .. NaZZ
return reg + n_letters(offset);
}

// Na0* .. Na9*
offset -= 601;

var digit2 = Math.floor(offset / 10111);
reg += digit2;
offset = offset % 10111;

if (offset <= 600) {
// Nab, NabA..NabZ, NabAA..NabZZ
return reg + n_letters(offset);
}

// Nab0* .. Nab9*
offset -= 601;

var digit3 = Math.floor(offset / 951);
reg += digit3;
offset = offset % 951;

if (offset <= 600) {
// Nabc, NabcA .. NabcZ, NabcAA .. NabcZZ
return reg + n_letters(offset);
}

// Nabc0* .. Nabc9*
offset -= 601;

var digit4 = Math.floor(offset / 35);
reg += digit4.toFixed(0);
offset = offset % 35;

if (offset <= 24) {
// Nabcd, NabcdA .. NabcdZ
return reg + n_letter(offset);
}

// Nabcd0 .. Nabcd9
offset -= 25;
return reg + offset.toFixed

(0);









share|improve this question
























  • No one understands what you're asking, please clarify
    – iamanigeeit
    Nov 23 at 9:16










  • I have a list of codes which I don't have the hex value or binary value. I was trying to work out what the formula is , as you can see some of the codes have a mixture of letters and numbers. So how does 432B get converted to Hex - A52AA5
    – Mr North
    Nov 23 at 9:44










  • @MrNorth So what I understand (but maybe I am wrong) is that you are looking for a single Excel formula that will convert the 4 codes in the Hex equivalent??
    – Peter K.
    Nov 23 at 11:43










  • yes that is correct
    – Mr North
    Nov 23 at 12:51










  • Each code has the letter N at the start , not sure if that is relevant ? N1 , N2 , N432B , N678XX
    – Mr North
    Nov 23 at 13:05














-2












-2








-2


0





Various reverse-engineered versions of the allocation algorithmsused by different countries to allocate 24-bit ICAO addresses based
on the aircraft registration These were worked out by looking at the allocation patterns and working backwards to an algorithm that generates that pattern,spot-checking aircraft to see if it worked.
YMMV.



    registration_from_hexid = (function () {
// hide the guts in a closure

var limited_alphabet = "ABCDEFGHJKLMNPQRSTUVWXYZ"; // 24 chars; no I,O
var full_alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; // 26 chars



// handles 3-letter suffixes assigned with a regular pattern
//
// start: first hexid of range
// s1: major stride (interval between different first letters)
// s2: minor stride (interval between different second letters)
// prefix: the registration prefix
//
// optionally:
// alphabet: the alphabet to use (defaults full_alphabet)
// first: the suffix to use at the start of the range (default: AAA)
// last: the last valid suffix in the range (default: ZZZ)

var stride_mappings = [
{ start: 0x008011, s1: 26*26, s2: 26, prefix: "ZS-" },

{ start: 0x390000, s1: 1024, s2: 32, prefix: "F-G" },
{ start: 0x398000, s1: 1024, s2: 32, prefix: "F-H" },

{ start: 0x3C4421, s1: 1024, s2: 32, prefix: "D-A", first:
'AAA', last: 'OZZ' },
{ start: 0x3C0001, s1: 26*26, s2: 26, prefix: "D-A", first:
'PAA', last: 'ZZZ' },
{ start: 0x3C8421, s1: 1024, s2: 32, prefix: "D-B", first:
'AAA', last: 'OZZ' },
{ start: 0x3C2001, s1: 26*26, s2: 26, prefix: "D-B", first:
'PAA', last: 'ZZZ' },
{ start: 0x3CC000, s1: 26*26, s2: 26, prefix: "D-C" },


{ start: 0x3D04A8, s1: 26*26, s2: 26, prefix: "D-E" },


{ start: 0x3D4950, s1: 26*26, s2: 26, prefix: "D-F" },
{ start: 0x3D8DF8, s1: 26*26, s2: 26, prefix: "D-G" },
{ start: 0x3DD2A0, s1: 26*26, s2: 26, prefix: "D-H" },
{ start: 0x3E1748, s1: 26*26, s2: 26, prefix: "D-I" },

{ start: 0x448421, s1: 1024, s2: 32, prefix: "OO-" },
{ start: 0x458421, s1: 1024, s2: 32, prefix: "OY-" },
{ start: 0x460000, s1: 26*26, s2: 26, prefix: "OH-" },
{ start: 0x468421, s1: 1024, s2: 32, prefix: "SX-" },
{ start: 0x490421, s1: 1024, s2: 32, prefix: "CS-" },
{ start: 0x4A0421, s1: 1024, s2: 32, prefix: "YR-" },
{ start: 0x4B8421, s1: 1024, s2: 32, prefix: "TC-" },
{ start: 0x740421, s1: 1024, s2: 32, prefix: "JY-" },
{ start: 0x760421, s1: 1024, s2: 32, prefix: "AP-" },
{ start: 0x768421, s1: 1024, s2: 32, prefix: "9V-" },
{ start: 0x778421, s1: 1024, s2: 32, prefix: "YK-" },
{ start: 0x7C0000, s1: 1296, s2: 36, prefix: "VH-" },
{ start: 0xC00001, s1: 26*26, s2: 26, prefix: "C-F" },
{ start: 0xC044A9, s1: 26*26, s2: 26, prefix: "C-G" },
{ start: 0xE01041, s1: 4096, s2: 64, prefix: "LV-" }
];
function lookup(hexid) {
var hexid = +("0x" + hexid);

reg = n_reg(hexid);
if (reg)
return reg;

reg = ja_reg(hexid);
if (reg)
return reg;

reg = hl_reg(hexid);
if (reg)
return reg;

reg = numeric_reg(hexid);
if (reg)
return reg;

reg = stride_reg(hexid);
if (reg)
return reg;

return null;
}

function stride_reg(hexid) {
// try the mappings in stride_mappings
var i;
for (i = 0; i < stride_mappings.length; ++i) {
var mapping = stride_mappings[i];
if (hexid < mapping.start || hexid > mapping.end)
continue;

var offset = hexid - mapping.start + mapping.offset;

var i1 = Math.floor(offset / mapping.s1);
offset = offset % mapping.s1;
var i2 = Math.floor(offset / mapping.s2);
offset = offset % mapping.s2;
var i3 = offset;

if (i1 < 0 || i1 >= mapping.alphabet.length ||
i2 < 0 || i2 >= mapping.alphabet.length ||
i3 < 0 || i3 >= mapping.alphabet.length)
continue;

return mapping.prefix + mapping.alphabet.charAt(i1) +
mapping.alphabet.charAt(i2) + mapping.alphabet.charAt(i3);
}

// nothing
return null;
}


// US N-numbers
//

function n_letters(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(Math.floor(rem / 25)) +
n_letter(rem % 25);
}

function n_letter(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(rem);


function n_reg(hexid) {
var offset = hexid - 0xA00001;
if (offset < 0 || offset >= 915399) {
return null;
}

var digit1 = Math.floor(offset / 101711) + 1;
var reg = "N" + digit1;
offset = offset % 101711;
if (offset <= 600) {
// Na, NaA .. NaZ, NaAA .. NaZZ
return reg + n_letters(offset);
}

// Na0* .. Na9*
offset -= 601;

var digit2 = Math.floor(offset / 10111);
reg += digit2;
offset = offset % 10111;

if (offset <= 600) {
// Nab, NabA..NabZ, NabAA..NabZZ
return reg + n_letters(offset);
}

// Nab0* .. Nab9*
offset -= 601;

var digit3 = Math.floor(offset / 951);
reg += digit3;
offset = offset % 951;

if (offset <= 600) {
// Nabc, NabcA .. NabcZ, NabcAA .. NabcZZ
return reg + n_letters(offset);
}

// Nabc0* .. Nabc9*
offset -= 601;

var digit4 = Math.floor(offset / 35);
reg += digit4.toFixed(0);
offset = offset % 35;

if (offset <= 24) {
// Nabcd, NabcdA .. NabcdZ
return reg + n_letter(offset);
}

// Nabcd0 .. Nabcd9
offset -= 25;
return reg + offset.toFixed

(0);









share|improve this question















Various reverse-engineered versions of the allocation algorithmsused by different countries to allocate 24-bit ICAO addresses based
on the aircraft registration These were worked out by looking at the allocation patterns and working backwards to an algorithm that generates that pattern,spot-checking aircraft to see if it worked.
YMMV.



    registration_from_hexid = (function () {
// hide the guts in a closure

var limited_alphabet = "ABCDEFGHJKLMNPQRSTUVWXYZ"; // 24 chars; no I,O
var full_alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; // 26 chars



// handles 3-letter suffixes assigned with a regular pattern
//
// start: first hexid of range
// s1: major stride (interval between different first letters)
// s2: minor stride (interval between different second letters)
// prefix: the registration prefix
//
// optionally:
// alphabet: the alphabet to use (defaults full_alphabet)
// first: the suffix to use at the start of the range (default: AAA)
// last: the last valid suffix in the range (default: ZZZ)

var stride_mappings = [
{ start: 0x008011, s1: 26*26, s2: 26, prefix: "ZS-" },

{ start: 0x390000, s1: 1024, s2: 32, prefix: "F-G" },
{ start: 0x398000, s1: 1024, s2: 32, prefix: "F-H" },

{ start: 0x3C4421, s1: 1024, s2: 32, prefix: "D-A", first:
'AAA', last: 'OZZ' },
{ start: 0x3C0001, s1: 26*26, s2: 26, prefix: "D-A", first:
'PAA', last: 'ZZZ' },
{ start: 0x3C8421, s1: 1024, s2: 32, prefix: "D-B", first:
'AAA', last: 'OZZ' },
{ start: 0x3C2001, s1: 26*26, s2: 26, prefix: "D-B", first:
'PAA', last: 'ZZZ' },
{ start: 0x3CC000, s1: 26*26, s2: 26, prefix: "D-C" },


{ start: 0x3D04A8, s1: 26*26, s2: 26, prefix: "D-E" },


{ start: 0x3D4950, s1: 26*26, s2: 26, prefix: "D-F" },
{ start: 0x3D8DF8, s1: 26*26, s2: 26, prefix: "D-G" },
{ start: 0x3DD2A0, s1: 26*26, s2: 26, prefix: "D-H" },
{ start: 0x3E1748, s1: 26*26, s2: 26, prefix: "D-I" },

{ start: 0x448421, s1: 1024, s2: 32, prefix: "OO-" },
{ start: 0x458421, s1: 1024, s2: 32, prefix: "OY-" },
{ start: 0x460000, s1: 26*26, s2: 26, prefix: "OH-" },
{ start: 0x468421, s1: 1024, s2: 32, prefix: "SX-" },
{ start: 0x490421, s1: 1024, s2: 32, prefix: "CS-" },
{ start: 0x4A0421, s1: 1024, s2: 32, prefix: "YR-" },
{ start: 0x4B8421, s1: 1024, s2: 32, prefix: "TC-" },
{ start: 0x740421, s1: 1024, s2: 32, prefix: "JY-" },
{ start: 0x760421, s1: 1024, s2: 32, prefix: "AP-" },
{ start: 0x768421, s1: 1024, s2: 32, prefix: "9V-" },
{ start: 0x778421, s1: 1024, s2: 32, prefix: "YK-" },
{ start: 0x7C0000, s1: 1296, s2: 36, prefix: "VH-" },
{ start: 0xC00001, s1: 26*26, s2: 26, prefix: "C-F" },
{ start: 0xC044A9, s1: 26*26, s2: 26, prefix: "C-G" },
{ start: 0xE01041, s1: 4096, s2: 64, prefix: "LV-" }
];
function lookup(hexid) {
var hexid = +("0x" + hexid);

reg = n_reg(hexid);
if (reg)
return reg;

reg = ja_reg(hexid);
if (reg)
return reg;

reg = hl_reg(hexid);
if (reg)
return reg;

reg = numeric_reg(hexid);
if (reg)
return reg;

reg = stride_reg(hexid);
if (reg)
return reg;

return null;
}

function stride_reg(hexid) {
// try the mappings in stride_mappings
var i;
for (i = 0; i < stride_mappings.length; ++i) {
var mapping = stride_mappings[i];
if (hexid < mapping.start || hexid > mapping.end)
continue;

var offset = hexid - mapping.start + mapping.offset;

var i1 = Math.floor(offset / mapping.s1);
offset = offset % mapping.s1;
var i2 = Math.floor(offset / mapping.s2);
offset = offset % mapping.s2;
var i3 = offset;

if (i1 < 0 || i1 >= mapping.alphabet.length ||
i2 < 0 || i2 >= mapping.alphabet.length ||
i3 < 0 || i3 >= mapping.alphabet.length)
continue;

return mapping.prefix + mapping.alphabet.charAt(i1) +
mapping.alphabet.charAt(i2) + mapping.alphabet.charAt(i3);
}

// nothing
return null;
}


// US N-numbers
//

function n_letters(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(Math.floor(rem / 25)) +
n_letter(rem % 25);
}

function n_letter(rem) {
if (rem == 0)
return "";

--rem;
return limited_alphabet.charAt(rem);


function n_reg(hexid) {
var offset = hexid - 0xA00001;
if (offset < 0 || offset >= 915399) {
return null;
}

var digit1 = Math.floor(offset / 101711) + 1;
var reg = "N" + digit1;
offset = offset % 101711;
if (offset <= 600) {
// Na, NaA .. NaZ, NaAA .. NaZZ
return reg + n_letters(offset);
}

// Na0* .. Na9*
offset -= 601;

var digit2 = Math.floor(offset / 10111);
reg += digit2;
offset = offset % 10111;

if (offset <= 600) {
// Nab, NabA..NabZ, NabAA..NabZZ
return reg + n_letters(offset);
}

// Nab0* .. Nab9*
offset -= 601;

var digit3 = Math.floor(offset / 951);
reg += digit3;
offset = offset % 951;

if (offset <= 600) {
// Nabc, NabcA .. NabcZ, NabcAA .. NabcZZ
return reg + n_letters(offset);
}

// Nabc0* .. Nabc9*
offset -= 601;

var digit4 = Math.floor(offset / 35);
reg += digit4.toFixed(0);
offset = offset % 35;

if (offset <= 24) {
// Nabcd, NabcdA .. NabcdZ
return reg + n_letter(offset);
}

// Nabcd0 .. Nabcd9
offset -= 25;
return reg + offset.toFixed

(0);






excel vba excel-vba excel-formula






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 23 at 16:38

























asked Nov 22 at 17:47









Mr North

125




125












  • No one understands what you're asking, please clarify
    – iamanigeeit
    Nov 23 at 9:16










  • I have a list of codes which I don't have the hex value or binary value. I was trying to work out what the formula is , as you can see some of the codes have a mixture of letters and numbers. So how does 432B get converted to Hex - A52AA5
    – Mr North
    Nov 23 at 9:44










  • @MrNorth So what I understand (but maybe I am wrong) is that you are looking for a single Excel formula that will convert the 4 codes in the Hex equivalent??
    – Peter K.
    Nov 23 at 11:43










  • yes that is correct
    – Mr North
    Nov 23 at 12:51










  • Each code has the letter N at the start , not sure if that is relevant ? N1 , N2 , N432B , N678XX
    – Mr North
    Nov 23 at 13:05


















  • No one understands what you're asking, please clarify
    – iamanigeeit
    Nov 23 at 9:16










  • I have a list of codes which I don't have the hex value or binary value. I was trying to work out what the formula is , as you can see some of the codes have a mixture of letters and numbers. So how does 432B get converted to Hex - A52AA5
    – Mr North
    Nov 23 at 9:44










  • @MrNorth So what I understand (but maybe I am wrong) is that you are looking for a single Excel formula that will convert the 4 codes in the Hex equivalent??
    – Peter K.
    Nov 23 at 11:43










  • yes that is correct
    – Mr North
    Nov 23 at 12:51










  • Each code has the letter N at the start , not sure if that is relevant ? N1 , N2 , N432B , N678XX
    – Mr North
    Nov 23 at 13:05
















No one understands what you're asking, please clarify
– iamanigeeit
Nov 23 at 9:16




No one understands what you're asking, please clarify
– iamanigeeit
Nov 23 at 9:16












I have a list of codes which I don't have the hex value or binary value. I was trying to work out what the formula is , as you can see some of the codes have a mixture of letters and numbers. So how does 432B get converted to Hex - A52AA5
– Mr North
Nov 23 at 9:44




I have a list of codes which I don't have the hex value or binary value. I was trying to work out what the formula is , as you can see some of the codes have a mixture of letters and numbers. So how does 432B get converted to Hex - A52AA5
– Mr North
Nov 23 at 9:44












@MrNorth So what I understand (but maybe I am wrong) is that you are looking for a single Excel formula that will convert the 4 codes in the Hex equivalent??
– Peter K.
Nov 23 at 11:43




@MrNorth So what I understand (but maybe I am wrong) is that you are looking for a single Excel formula that will convert the 4 codes in the Hex equivalent??
– Peter K.
Nov 23 at 11:43












yes that is correct
– Mr North
Nov 23 at 12:51




yes that is correct
– Mr North
Nov 23 at 12:51












Each code has the letter N at the start , not sure if that is relevant ? N1 , N2 , N432B , N678XX
– Mr North
Nov 23 at 13:05




Each code has the letter N at the start , not sure if that is relevant ? N1 , N2 , N432B , N678XX
– Mr North
Nov 23 at 13:05












1 Answer
1






active

oldest

votes


















1














The mode s hex code is used in aerospace/navigation.

From wikipedia :




Mode S equipped aircraft are assigned a unique ICAO 24-bit address or (informally) Mode-S "hex code" upon national registration and this address becomes a part of the aircraft's Certificate of Registration. Normally, the address is never changed, however, the transponders are reprogrammable and, occasionally, are moved from one aircraft to another (presumably for operational or cost purposes), either by maintenance or by changing the appropriate entry in the aircraft's Flight management system.



There are 16,777,214 (224-2) unique ICAO 24-bit addresses (hex codes) available.[4][5] The ICAO 24-bit address can be represented in three digital formats: hexadecimal, octal, and binary. These addresses are used to provide a unique identity normally allocated to an individual aircraft or registration.




You can find the aircraft just be googling each mode s hex code, e.g. for the first one: https://www.radarbox24.com/data/mode-s/A00001#1068536488.



The N-code is the Aircraft Registration code. Again, from Wikipedia:




In the United States, the registration number is commonly referred to as an "N" number, because all aircraft registered there have a number starting with the letter N. An alphanumeric system is used because of the large numbers of aircraft registered in the United States. An N-number begins with a run of one or more numeric digits, may end with one or two alphabetic letters, may only consist of one to five characters in total, and must start with a digit other than zero.




I do not think there is any mathematical or logical relationship between the mode s hex and the aircraft registration code !!






share|improve this answer





















  • There is a formula used, but I have not been able to research it. Had hoped a formula guru might have an answer
    – Mr North
    Nov 23 at 15:38










  • @MrNorth Do you have a source that confirms that there is a formula? I would seriously doubt that such formula exists. The FAA assigns the registration codes in the USA and has to choose a mode s hex code from a block that is made available by the ICAO for the US. It is indeed so that the Gulfstream owned by the FAA with registration number N1 has also the first mode s hex code from the block. But if you look at N2, ,the ICAO address is completely different. registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=2
    – Peter K.
    Nov 23 at 15:53












  • i have attached a javascript file converted to text showing the formulas for a list of countries. and some form of formulas.
    – Mr North
    Nov 23 at 16:07










  • I only looked at the US, as the first 3 are registered with the FAA. The 4th I didn't find (if you say it starts with an N as well, there must be a typo because that registration number does not exist). So maybe other countries do it differently.
    – Peter K.
    Nov 23 at 16:31










  • I have just added the javascript code , there is the formula for n number in hte text
    – Mr North
    Nov 23 at 16:40











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436070%2fexcel-vba-to-find-correct-formula-to-get-result-from-hexcode%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














The mode s hex code is used in aerospace/navigation.

From wikipedia :




Mode S equipped aircraft are assigned a unique ICAO 24-bit address or (informally) Mode-S "hex code" upon national registration and this address becomes a part of the aircraft's Certificate of Registration. Normally, the address is never changed, however, the transponders are reprogrammable and, occasionally, are moved from one aircraft to another (presumably for operational or cost purposes), either by maintenance or by changing the appropriate entry in the aircraft's Flight management system.



There are 16,777,214 (224-2) unique ICAO 24-bit addresses (hex codes) available.[4][5] The ICAO 24-bit address can be represented in three digital formats: hexadecimal, octal, and binary. These addresses are used to provide a unique identity normally allocated to an individual aircraft or registration.




You can find the aircraft just be googling each mode s hex code, e.g. for the first one: https://www.radarbox24.com/data/mode-s/A00001#1068536488.



The N-code is the Aircraft Registration code. Again, from Wikipedia:




In the United States, the registration number is commonly referred to as an "N" number, because all aircraft registered there have a number starting with the letter N. An alphanumeric system is used because of the large numbers of aircraft registered in the United States. An N-number begins with a run of one or more numeric digits, may end with one or two alphabetic letters, may only consist of one to five characters in total, and must start with a digit other than zero.




I do not think there is any mathematical or logical relationship between the mode s hex and the aircraft registration code !!






share|improve this answer





















  • There is a formula used, but I have not been able to research it. Had hoped a formula guru might have an answer
    – Mr North
    Nov 23 at 15:38










  • @MrNorth Do you have a source that confirms that there is a formula? I would seriously doubt that such formula exists. The FAA assigns the registration codes in the USA and has to choose a mode s hex code from a block that is made available by the ICAO for the US. It is indeed so that the Gulfstream owned by the FAA with registration number N1 has also the first mode s hex code from the block. But if you look at N2, ,the ICAO address is completely different. registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=2
    – Peter K.
    Nov 23 at 15:53












  • i have attached a javascript file converted to text showing the formulas for a list of countries. and some form of formulas.
    – Mr North
    Nov 23 at 16:07










  • I only looked at the US, as the first 3 are registered with the FAA. The 4th I didn't find (if you say it starts with an N as well, there must be a typo because that registration number does not exist). So maybe other countries do it differently.
    – Peter K.
    Nov 23 at 16:31










  • I have just added the javascript code , there is the formula for n number in hte text
    – Mr North
    Nov 23 at 16:40
















1














The mode s hex code is used in aerospace/navigation.

From wikipedia :




Mode S equipped aircraft are assigned a unique ICAO 24-bit address or (informally) Mode-S "hex code" upon national registration and this address becomes a part of the aircraft's Certificate of Registration. Normally, the address is never changed, however, the transponders are reprogrammable and, occasionally, are moved from one aircraft to another (presumably for operational or cost purposes), either by maintenance or by changing the appropriate entry in the aircraft's Flight management system.



There are 16,777,214 (224-2) unique ICAO 24-bit addresses (hex codes) available.[4][5] The ICAO 24-bit address can be represented in three digital formats: hexadecimal, octal, and binary. These addresses are used to provide a unique identity normally allocated to an individual aircraft or registration.




You can find the aircraft just be googling each mode s hex code, e.g. for the first one: https://www.radarbox24.com/data/mode-s/A00001#1068536488.



The N-code is the Aircraft Registration code. Again, from Wikipedia:




In the United States, the registration number is commonly referred to as an "N" number, because all aircraft registered there have a number starting with the letter N. An alphanumeric system is used because of the large numbers of aircraft registered in the United States. An N-number begins with a run of one or more numeric digits, may end with one or two alphabetic letters, may only consist of one to five characters in total, and must start with a digit other than zero.




I do not think there is any mathematical or logical relationship between the mode s hex and the aircraft registration code !!






share|improve this answer





















  • There is a formula used, but I have not been able to research it. Had hoped a formula guru might have an answer
    – Mr North
    Nov 23 at 15:38










  • @MrNorth Do you have a source that confirms that there is a formula? I would seriously doubt that such formula exists. The FAA assigns the registration codes in the USA and has to choose a mode s hex code from a block that is made available by the ICAO for the US. It is indeed so that the Gulfstream owned by the FAA with registration number N1 has also the first mode s hex code from the block. But if you look at N2, ,the ICAO address is completely different. registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=2
    – Peter K.
    Nov 23 at 15:53












  • i have attached a javascript file converted to text showing the formulas for a list of countries. and some form of formulas.
    – Mr North
    Nov 23 at 16:07










  • I only looked at the US, as the first 3 are registered with the FAA. The 4th I didn't find (if you say it starts with an N as well, there must be a typo because that registration number does not exist). So maybe other countries do it differently.
    – Peter K.
    Nov 23 at 16:31










  • I have just added the javascript code , there is the formula for n number in hte text
    – Mr North
    Nov 23 at 16:40














1












1








1






The mode s hex code is used in aerospace/navigation.

From wikipedia :




Mode S equipped aircraft are assigned a unique ICAO 24-bit address or (informally) Mode-S "hex code" upon national registration and this address becomes a part of the aircraft's Certificate of Registration. Normally, the address is never changed, however, the transponders are reprogrammable and, occasionally, are moved from one aircraft to another (presumably for operational or cost purposes), either by maintenance or by changing the appropriate entry in the aircraft's Flight management system.



There are 16,777,214 (224-2) unique ICAO 24-bit addresses (hex codes) available.[4][5] The ICAO 24-bit address can be represented in three digital formats: hexadecimal, octal, and binary. These addresses are used to provide a unique identity normally allocated to an individual aircraft or registration.




You can find the aircraft just be googling each mode s hex code, e.g. for the first one: https://www.radarbox24.com/data/mode-s/A00001#1068536488.



The N-code is the Aircraft Registration code. Again, from Wikipedia:




In the United States, the registration number is commonly referred to as an "N" number, because all aircraft registered there have a number starting with the letter N. An alphanumeric system is used because of the large numbers of aircraft registered in the United States. An N-number begins with a run of one or more numeric digits, may end with one or two alphabetic letters, may only consist of one to five characters in total, and must start with a digit other than zero.




I do not think there is any mathematical or logical relationship between the mode s hex and the aircraft registration code !!






share|improve this answer












The mode s hex code is used in aerospace/navigation.

From wikipedia :




Mode S equipped aircraft are assigned a unique ICAO 24-bit address or (informally) Mode-S "hex code" upon national registration and this address becomes a part of the aircraft's Certificate of Registration. Normally, the address is never changed, however, the transponders are reprogrammable and, occasionally, are moved from one aircraft to another (presumably for operational or cost purposes), either by maintenance or by changing the appropriate entry in the aircraft's Flight management system.



There are 16,777,214 (224-2) unique ICAO 24-bit addresses (hex codes) available.[4][5] The ICAO 24-bit address can be represented in three digital formats: hexadecimal, octal, and binary. These addresses are used to provide a unique identity normally allocated to an individual aircraft or registration.




You can find the aircraft just be googling each mode s hex code, e.g. for the first one: https://www.radarbox24.com/data/mode-s/A00001#1068536488.



The N-code is the Aircraft Registration code. Again, from Wikipedia:




In the United States, the registration number is commonly referred to as an "N" number, because all aircraft registered there have a number starting with the letter N. An alphanumeric system is used because of the large numbers of aircraft registered in the United States. An N-number begins with a run of one or more numeric digits, may end with one or two alphabetic letters, may only consist of one to five characters in total, and must start with a digit other than zero.




I do not think there is any mathematical or logical relationship between the mode s hex and the aircraft registration code !!







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 23 at 15:11









Peter K.

739112




739112












  • There is a formula used, but I have not been able to research it. Had hoped a formula guru might have an answer
    – Mr North
    Nov 23 at 15:38










  • @MrNorth Do you have a source that confirms that there is a formula? I would seriously doubt that such formula exists. The FAA assigns the registration codes in the USA and has to choose a mode s hex code from a block that is made available by the ICAO for the US. It is indeed so that the Gulfstream owned by the FAA with registration number N1 has also the first mode s hex code from the block. But if you look at N2, ,the ICAO address is completely different. registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=2
    – Peter K.
    Nov 23 at 15:53












  • i have attached a javascript file converted to text showing the formulas for a list of countries. and some form of formulas.
    – Mr North
    Nov 23 at 16:07










  • I only looked at the US, as the first 3 are registered with the FAA. The 4th I didn't find (if you say it starts with an N as well, there must be a typo because that registration number does not exist). So maybe other countries do it differently.
    – Peter K.
    Nov 23 at 16:31










  • I have just added the javascript code , there is the formula for n number in hte text
    – Mr North
    Nov 23 at 16:40


















  • There is a formula used, but I have not been able to research it. Had hoped a formula guru might have an answer
    – Mr North
    Nov 23 at 15:38










  • @MrNorth Do you have a source that confirms that there is a formula? I would seriously doubt that such formula exists. The FAA assigns the registration codes in the USA and has to choose a mode s hex code from a block that is made available by the ICAO for the US. It is indeed so that the Gulfstream owned by the FAA with registration number N1 has also the first mode s hex code from the block. But if you look at N2, ,the ICAO address is completely different. registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=2
    – Peter K.
    Nov 23 at 15:53












  • i have attached a javascript file converted to text showing the formulas for a list of countries. and some form of formulas.
    – Mr North
    Nov 23 at 16:07










  • I only looked at the US, as the first 3 are registered with the FAA. The 4th I didn't find (if you say it starts with an N as well, there must be a typo because that registration number does not exist). So maybe other countries do it differently.
    – Peter K.
    Nov 23 at 16:31










  • I have just added the javascript code , there is the formula for n number in hte text
    – Mr North
    Nov 23 at 16:40
















There is a formula used, but I have not been able to research it. Had hoped a formula guru might have an answer
– Mr North
Nov 23 at 15:38




There is a formula used, but I have not been able to research it. Had hoped a formula guru might have an answer
– Mr North
Nov 23 at 15:38












@MrNorth Do you have a source that confirms that there is a formula? I would seriously doubt that such formula exists. The FAA assigns the registration codes in the USA and has to choose a mode s hex code from a block that is made available by the ICAO for the US. It is indeed so that the Gulfstream owned by the FAA with registration number N1 has also the first mode s hex code from the block. But if you look at N2, ,the ICAO address is completely different. registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=2
– Peter K.
Nov 23 at 15:53






@MrNorth Do you have a source that confirms that there is a formula? I would seriously doubt that such formula exists. The FAA assigns the registration codes in the USA and has to choose a mode s hex code from a block that is made available by the ICAO for the US. It is indeed so that the Gulfstream owned by the FAA with registration number N1 has also the first mode s hex code from the block. But if you look at N2, ,the ICAO address is completely different. registry.faa.gov/aircraftinquiry/NNum_Results.aspx?NNumbertxt=2
– Peter K.
Nov 23 at 15:53














i have attached a javascript file converted to text showing the formulas for a list of countries. and some form of formulas.
– Mr North
Nov 23 at 16:07




i have attached a javascript file converted to text showing the formulas for a list of countries. and some form of formulas.
– Mr North
Nov 23 at 16:07












I only looked at the US, as the first 3 are registered with the FAA. The 4th I didn't find (if you say it starts with an N as well, there must be a typo because that registration number does not exist). So maybe other countries do it differently.
– Peter K.
Nov 23 at 16:31




I only looked at the US, as the first 3 are registered with the FAA. The 4th I didn't find (if you say it starts with an N as well, there must be a typo because that registration number does not exist). So maybe other countries do it differently.
– Peter K.
Nov 23 at 16:31












I have just added the javascript code , there is the formula for n number in hte text
– Mr North
Nov 23 at 16:40




I have just added the javascript code , there is the formula for n number in hte text
– Mr North
Nov 23 at 16:40


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436070%2fexcel-vba-to-find-correct-formula-to-get-result-from-hexcode%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Contact image not getting when fetch all contact list from iPhone by CNContact

count number of partitions of a set with n elements into k subsets

A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks