# How can I get the hex value of an 8 row column with depending on background color?

I have a spreadsheet that I'm using to help visualize a character bit map for each ASCII character. For a 5x7 font, I use 5 cells wide (columns) and 7 cells tall (rows). I then marked the background black for each cell in a 8 row column, to represent the bits of a byte, where each bit is a pixel, and a black background means a pixel that is `ON`. Then, I create a byte array which will be my character representation. I have to do this for 95 characters (ASCII values), for each font size I want.

The problem is that I have to stare at each column of the character, and determine its binary value and then figure out the hex value. So trying to figure out 495 bytes for a 5-wide character table becomes tedious work. I want to know if there is a worksheet function I could write that would inspect the background color of a cell, if black is would set the nth bit of a variable, and then create the hex value of that column below.

TL;DR I don't have function writing experience in Excel. I'm working with a LCD screen. I have a spreadsheet with all my characters on it--where each pixel that is `ON` would have the background color of black. 1 cell = 1 bit of a byte. I need a function that will give me a hex value of a column depending if the background cell is black or not. Proceed to illustrations.

Here is an example of a 5x7 character 'A' in my spreadsheet. A *** represents that a cells background color is black, otherwise white. (It wouldn't be a problem to add something like a cell value of 1 to each cell that has a background color of black if needed, to make this an easier function):

``````   ____________________
B0|   |***|***|***|   | (row 0)
|-------------------|
B1|***|   |   |   |***| (row 1)
|-------------------|
B2|***|   |   |   |***| (row 2)
|-------------------|
B3|***|***|***|***|***| (row 3)
|-------------------|
B4|***|   |   |   |***| (row 4)
|-------------------|
B5|***|   |   |   |***| (row 5)
|-------------------|
B6|***|   |   |   |***| (row 6)
|-------------------|
B7|___|___|___|___|___| (row 7)
7E  09  09  09  7E   (row 8)
``````

The byte array would then be:

``````byte A[] = { 0x7E, 0x09, 0x09, 0x09, 0x7E };
``````

Pseudo-code version (for one column):

``````char hex = 0x00;
for(int bI = 0; bI < 8; bI++)
{
if(cell[bI].BackgroundColor == Black)
{
hex |= (0x01 << bI;
}
}
row8.Text = hex.ToString("X2");
``````
• I don’t understand what you have and what you need. Are you starting with a concept and a magnifying glass? So are you looking for a solution that involves reading the Windows font files? (You might want to consider displaying all the characters (e.g., in Notepad or Word), taking a screen snapshot, pasting into Paint, saving the image, and then analyzing the image file.) Or do you already have the { 0x7E, 0x09, 0x09, 0x09, 0x7E } data, and you just need help displaying it? – Scott Mar 22 '13 at 20:52
• being that the tags have all to do with excel, I figured the question was rather straightforward--I needed a worksheet function. the marked answer solves my question. – Kcvin Apr 2 '13 at 19:52

You can add a column to indicate the bit positions & add the number of `1`s (bits). For my example below, what I did was:

1. Create a conditional format for my 5x7 range, where if I enter `1` into a cell, it turns black (I used orange so I can see the 1's). 2. Add a column to the left of the character to mark the bit positions in decimal `1`, `2`, `4`, `8`, `16`, `32`, `64` (see column B).

3. To get the hex values, use this formula across bottom:

`=DEC2HEX(SUMPRODUCT(\$B\$2:\$B\$8,D2:D8),2)` Where:

• \$B\$2:\$B\$8 is the range that contains the bit positions in decimal
• D2:D8 is the first column in the 5x7 range
• 2 is the number of digits you want to display

If you don't wish to create an extra column, incorporate the bit positions into the formula, like so:

``````=DEC2HEX(SUMPRODUCT({1;2;4;8;16;32;64},G1:G7),2)
``````

Ex: ref http://www.computerhope.com/binhex.htm