It is Memorial Day in the U.S. Since most people are out at a picnic instead of at work today, I tackle an incredibly obscure problem. How can we make sure that Hexadecimal numbers have leading zeroes? Episode 766 will take a look at the HEX2DEC, TEXT, and RIGHT functions.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Well, hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
It's Memorial Day in the United States so I figured most people aren't at work today.
I have a very, very obscure question that came in but I love this question because it takes me back to the days long, long ago when I was a Cobol programmer.
Back in the days of Cobol, they would store numbers in something called a packed format.
Basically, it was hexadecimal.
You could fit more numbers into a smaller amount of space back when disk space had a huge premium.
There's a great function in Excel that will convert decimal numbers back to hex.
So, =DEC, 2, the number 2, HEX, and the number.
[ =DEC2HEX(C3) ] And so I ran into someone in the seminar who said, look, we have a really old mainframe system.
We have to take data from Excel, we have to upload it back to the mainframe, and so we're required to convert these numbers back to hex, but, every once in a while, they said, the number doesn't show up on the mainframe, and what they finally figured out was it was all the low numbers, numbers 15 and below, where there was only a single digit.
The mainframe was expecting 2 digits and basically they said, hey, we need to force this to be 2 digits.
Well, my first reaction, and it seemed to work, was to use the TEXT function.
I want the text of that result in the 00 format, and, sure enough, that worked great for the 1, but I didn't think about the hexadecimal numbers from 10 to 15.
Those are the numbers that basically are a single letter, A,B,C,D, and, in this case, the double 0 format doesn't work when there's a D in there.
It's just simply going to fail.
[ =TEXT(D6,“00”) ] So, if we really have to do this, then the solution is to use =RIGHT, and I'm going to concatenate a 0, an &, and the result of my DEC2HEX formula, and said that I want the 2 most right characters.
So, what I get there is, for the single digit hexadecimal numbers, I'm now forcing a 0 to appear.
For the numbers that are already more than a single digit, so in other words 19, 0, 1, 9, if I ask for the rightmost two digits from that, I'm getting the 19.
[ =RIGHT(“0”&D3,2) ] So, interesting problem there, sending data back up to a mainframe.
Now, of course, for any Cobol programmers out there or people that used to program in Cobol, if there's a negative, there's actually an over punch on that last digit.
The folks that I talked to had gone through and actually figured out, with some IF statements, how to change this DEC2HEX to change the last digit if in fact the number was negative.
Very obscure question.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
It's Memorial Day in the United States so I figured most people aren't at work today.
I have a very, very obscure question that came in but I love this question because it takes me back to the days long, long ago when I was a Cobol programmer.
Back in the days of Cobol, they would store numbers in something called a packed format.
Basically, it was hexadecimal.
You could fit more numbers into a smaller amount of space back when disk space had a huge premium.
There's a great function in Excel that will convert decimal numbers back to hex.
So, =DEC, 2, the number 2, HEX, and the number.
[ =DEC2HEX(C3) ] And so I ran into someone in the seminar who said, look, we have a really old mainframe system.
We have to take data from Excel, we have to upload it back to the mainframe, and so we're required to convert these numbers back to hex, but, every once in a while, they said, the number doesn't show up on the mainframe, and what they finally figured out was it was all the low numbers, numbers 15 and below, where there was only a single digit.
The mainframe was expecting 2 digits and basically they said, hey, we need to force this to be 2 digits.
Well, my first reaction, and it seemed to work, was to use the TEXT function.
I want the text of that result in the 00 format, and, sure enough, that worked great for the 1, but I didn't think about the hexadecimal numbers from 10 to 15.
Those are the numbers that basically are a single letter, A,B,C,D, and, in this case, the double 0 format doesn't work when there's a D in there.
It's just simply going to fail.
[ =TEXT(D6,“00”) ] So, if we really have to do this, then the solution is to use =RIGHT, and I'm going to concatenate a 0, an &, and the result of my DEC2HEX formula, and said that I want the 2 most right characters.
So, what I get there is, for the single digit hexadecimal numbers, I'm now forcing a 0 to appear.
For the numbers that are already more than a single digit, so in other words 19, 0, 1, 9, if I ask for the rightmost two digits from that, I'm getting the 19.
[ =RIGHT(“0”&D3,2) ] So, interesting problem there, sending data back up to a mainframe.
Now, of course, for any Cobol programmers out there or people that used to program in Cobol, if there's a negative, there's actually an over punch on that last digit.
The folks that I talked to had gone through and actually figured out, with some IF statements, how to change this DEC2HEX to change the last digit if in fact the number was negative.
Very obscure question.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
