Hex to ascii conversion error in Excel VBA

Question

I have found below stated function for conversion of hex to text.It works fin for most of hex to text conversions but give abnormal result.

For example, hex value is:

050003d40201414c4552542d42656c6f772038353435206966204e462054726164657320666f722031352d3230206d696e75746573202c77617463682050414e49432050414e4943207570746f20353439332d2d383437360d0a0d0a53656c6c204549434845522061742032343931302e2e2e73746f706c6f7373732032353

Result i get on using hex2text Function = |

Correct Result should have been something like:

ALERT-Below 8545 if NF Trades for 15-20 minutes ,watch PANIC PANIC upto 5493--8476........

(Note: I used hex2ascii conversion website http://www.rapidtables.com/convert/number/hex-to-ascii.htm to obtain correct results)

My Hex2text Function is:

Public Function HexToText(text As Range) As String
    Dim i As Integer
    Dim DummyStr As String

    For i = 1 To Len(text) Step 2
       DummyStr = DummyStr & Chr(Val("&H" & (Mid(text, i, 2))))
       DoEvents
    Next i

    HexToText = DummyStr
End Function

There are 6 bytes before ALERT, one of which is a NULL (00), Excel will not display anything beyond this in a cell so you see only the preceding (unprintable bytes). If there is always a 6 byte header, read from the 13th character onwards. — Alex K.
– Alex K., Commented Jan 23, 2017 at 15:02

CLR · Accepted Answer · 2017-01-23 15:21:45Z

1

As Alex has stated, the first 6 bytes are causing the issue.

You could either start at the 13th character by changing the ForNext loop:

 For i = 13 To Len(text) Step 2

Or you could filter any characters with ASCII code lower than 32 out..

If Val("&H" & (Mid(Text, i, 2))) > 31 Then DummyStr = DummyStr & Chr(Val("&H" & (Mid(Text, i, 2))))

Or replace them with (for instance) a space..

If Val("&H" & (Mid(Text, i, 2))) > 31 Then 
   DummyStr = DummyStr & Chr(Val("&H" & (Mid(Text, i, 2))))
Else
   DummyStr = DummyStr & " "
End If

answered Jan 23, 2017 at 15:21

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hex to ascii conversion error in Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related