I had the addresses in hexadecimal using 'C' language notation in one column. Turns out Google Spreadsheet doesn't understand that notation, matter of fact it doesn't really understand hexadecimal at all. To do anything with a hex number, you have to convert it to decimal. Further, you best not be using eight digits. That's more than Spreadsheet can handle. So we use:
- mid(cell address, start position, number of characters) to extract a portion of the number(for example 0xbf80f440). In this case the strings are all ten characters long (counting the leading 0x), the first three digits are all the same, so we only need the last five. So our function call looks like this: mid(cell, 6, 5)
- hex2dec(number) to covert the hex number to a decimal number,
- subtraction to calculate the difference between the two numbers, and lastly
- dec2hex(number) to convert the number back to hexadecimal format.
Put it all together and the contents of a sample cell looks like this: