Using Hexadecimal Numbers in Google Spreadsheets

Google Doc's does spreadsheets. I was mucking around with one today, trying to sort out the SFR's (Special Function Registers) in a PIC32 microcontroller. Some of these registers have contiguous addresses, but in some places there are gaps. Some are big gaps, some are small gaps. I wanted to find out just how big these gaps are.

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:

=dec2hex(hex2dec(mid(B166,6,5))-hex2dec(mid(B164,6,5)))