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 1s (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).

    enter image description here

  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:


    enter image description here


    • $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:


Ex: enter image description here

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.