Intel's Ronler Acres Plant

Silicon Forest
If the type is too small, Ctrl+ is your friend

Thursday, March 4, 2010

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)))


2 comments:

Ultimate Outsider said...

As an engineer who's used Google Sheets for as long as I have, I can't believe I'd never encountered the hex-handling limitations before today. Just tried to do some hex math on it and discovered your post when trying to figure out why it wasn't working! I was afraid I might have to do some hex2dec-to-dec2hex jive and your post appears to confirm my suspicion. Thanks!

Bakramandi said...

I think we can use Hexadecimal in spreed sheets.