How to convert from 24 bit Hexadecimal data to Binary data

[nur2544]

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

A1=000000000000 200080000000
B1=000000000000 000080000000
C1=000000000000 010080023000
D1=000000000000 200080000000
E1=000000000400 000000000000
F1=00000000C000 000000000000

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

A1=200080000000
B1=80000000
C1=10080023000
D1=200080000000
E1=400000000000000
F1=C000000000000000

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

G1=1000000000000010000000000000000000000000000000
H1=10000000000000000000000000000000
I1=10000000010000000000000100011000000000000
J1=1000000000000010000000000000000000000000000000
K1=10000000000000000000000000000000000000000000000000000000000
L1=1100000000000000000000000000000000000000000000000000000000000000

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

[Andrew-R]

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:

Function Dec2LongBin(ByVal lDecimal) As String

Dim sTmp As String

sTmp = ""

While lDecimal > 0
  sTmp = (lDecimal - (Int(lDecimal / 2) * 2)) & sTmp
  lDecimal = Int(lDecimal / 2)
Wend

If sTmp = "" Then
  Dec2LongBin = "0"
Else
  Dec2LongBin = sTmp
End If

End Function

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?

[martindwilson]

here's one way with some helpers nur2.xls
edited some errors on sheet
ignore this still some errors

[Andrew-R]

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

[martindwilson]

changed now and its fixed
i used
=IF(COLUMN(A1)>LEN($A1),"",HEX2BIN(MID($A1,COLUMN(A1),1),4))
and then concatenated the results and removed leading 0

[nur2544]

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

[Andrew-R]

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:

Function Hex2LongBin(ByVal sHex As String, Optional lDigits As Long = -1) As String

Dim sTmp As String
Dim lLoop As Long

If Len(sHex) = 0 Then
  sTmp = "0"
Else
  sTmp = ""
  For lLoop = 1 To Len(sHex)
    sTmp = sTmp & WorksheetFunction.Hex2Bin(Mid(sHex, lLoop, 1), 4)
  Next lLoop
End If

If InStr(sTmp, "1") > 0 Then
  sTmp = Mid(sTmp, InStr(sTmp, "1"))
End If

If lDigits > 0 And Len(sTmp) < lDigits Then
  sTmp = String(lDigits - Len(sTmp), "0") & sTmp
End If

Hex2LongBin = sTmp

End Function

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

[nur2544]

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

[Andrew-R]

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.

[nur2544]

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

[Andrew-R]

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

Sub DoIt()

Dim lLastRow As Long

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(1, 7), Cells(lLastRow, 7))
  .Formula = "=Hex2LongBin(SUBSTITUTE(A1,"" "",""""))"
End With

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

Cells(1, 13).Copy Destination:=Range(Cells(2, 13), Cells(lLastRow, 13))

Range(Cells(1, 13), Cells(lLastRow, 13)).Copy Destination:=Range(Cells(1, 14), Cells(1, 23))

End Sub

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?

[nur2544]

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

[nur2544]

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