How to convert from 24 bit Hexadecimal data to Binary data

i have lots of Hexadecimal data till row 20000 , here i posted few of them for example,my query is like listed below

Please Login or Register  to view this content.

1st Step: i need to delete all zero & space from left side then it will be look like below

Please Login or Register  to view this content.

reff: for my project i already got partial update by below link

http://www.excelforum.com/excel-prog...57#post3107857

now i want to go forward with new step (actually i want to do my job step by step so that i can express my issue to all clearly)

2nd step: now i need to convert above data from Hexadecimal to Binary which are like following

Please Login or Register  to view this content.

actually i do it manually with the help of website as following but it is very bothering job for me!!

http://www.binaryhexconverter.com/he...nary-converter

3rd step: i will let you know later !!

OK.

Step 1. In cell B1 I put the formula:

=MID(SUBSTITUTE(A1," ",""),SMALL(IF(MID(SUBSTITUTE(A1," ",""),ROW(1:24),1)<>"0",ROW(1:24)),1),24)

This is an array formula, so must be entered with Ctrl-Shift-Enter, not just enter.

Step 1a.

In C1 I put the formula:

=SUM(HEX2DEC(MID(B1,ROW(INDIRECT("1:" & LEN(B1))),1))*16^(LEN(B1)-ROW(INDIRECT("1:" & LEN(B1)))))

This is also an array formula, so again must be entered with Ctrl-Shift-Enter, it converts the Hex number in B1 into a decimal value.

Step 2.

There's a standard DEC2BIN function in Excel (and, indeed, a HEX2DEC function), but they can't cope with numbers this size, so I wrote a user-defined function:

Please Login or Register  to view this content.

Normally this sort of function would use Mod, but I've been told there's a bug in the Mod function when handling large numbers, so I've avoided it.

Anyway, with this code in a VBA module I can now put in G1:

=dec2longbin(C1)

And it will convert the decimal number to a binary one, and it seems to match the examples that you've given.

Does that get you started?

Hmmm, I get an invalid attachment when I try to open nur2.

Have you gone with:

=IF(LEN($A1)>=COLUMN(A1),DEC2BIN(HEX2DEC(MID($A1,COLUMN(A1),1)),4),"")

Yes, it occurred to me after posting that there was a HEX2BIN, so I didn't need to go Hex -> Dec -> Bin.

Also, comparing your results with mine shows that my solution runs into problems with Excel's maximum precision when dealing with some of the numbers involved.

I'd still be tempted to wrap it all inside a UDF, to do it in a oner:

Please Login or Register  to view this content.

=Hex2LongBin(SUBSTITUTE(A1," ",""))

Hi Andrew-R
excellent job, i appreciate your macro logic

However my final destination is something like below

G1=1000000000000010000000000000000000000000000000

from right side of binary code, here i got 31 zero ("0") till first "1" so results in cell M1 will be 31
again i will be counting from right side of binary code till last left "1" no i am getting 45 (please notice that this time
i ignore middle "1", here i treat it as zero ("0"), so if i treat middle one ("1") is zero , then total zero is 45 so results
in the cell N1 will be 45)

now what will happened if the binary is like below

10000000010000000000000100011000000000000

1st step:CELL M1 will be 12 (as from right side we have 1000000000000 i.e 12 Zero till "1")
2nd step:CELL N1 will be 13 (for 2nd time from right side we have 11000000000000 i.e 13 in this time i treat "1" as zero "0" )
3rd step:CELL O1 will be 17 (100011000000000000 it will be 17 as middle both "1","1" i treat as "0","0", so total zero is 17)
4th step:CELL p1 will be 31 (10000000000000100011000000000000 so total zero is 31 as all middle "1" is zero , so total zero 31)
5th step:CELLQ1 will be 40 (10000000010000000000000100011000000000000 so total zero is 40 , as all middle "1" is "0" so total zero 40)

summery:
I1=10000000010000000000000100011000000000000

so from above binary i will be getting like below
M1=12
N1=13
O1=17
p1=31
Q1=40

last step: i will let you know later, it is some thing vlookup from other sheet as 12,13,17,31,or 40 etc having individual meaning !!!!

OK, so if your binary number is in G1 then in M1 put the formula:

=IFERROR(LEN($G1)-LARGE(IF(MID($G1,ROW(INDIRECT("1:" & LEN($G1))),1)="1",ROW(INDIRECT("1:" & LEN($G1)))),COLUMN(A1)),"")

Once again confirmed with Ctrl-Shift-Enter.

This can then be dragged across as far as required.

hi martindwilson
Thanks for response, it works but i am interested in macro as my data range are very large (till 65536 or more !!! ). i will be happy if get macro solution, thanks for
your time...

Hi Andrew-R
i appreciate macro if possible then try for me !!! other then i am going to the thread as solved...

Well if your data starts in row 1 then, using the Hex2LongBin function that I posted:

Please Login or Register  to view this content.

This will insert the Hex2LongBin formula all of the way down column G, and then the formula to return the position of the 1s in columns M-W

That do you?

Hi, Andrew-R
You did outstand job for me, this is it ! what I was looking for few weeks in the web. Still I have Some problem (don’t worry your job is 99.99% correct). Base on output results Now I am trying to find out is there any mismatch found or not. I will let you know if I get any mismatch. Then I will close the issue, again thanks for your valuable time for me!!!

Hi Andrew-R
I have tested with lots of data , & it works very fine... , thanks… for making my job easier ….