Excel VBA中的十六进制到ASCII转换错误 [英] Hex to ascii conversion error in Excel VBA
问题描述
我发现以下所述的将十六进制转换为文本的功能.它对于大多数十六进制转换为文本的功能都很好,但是给出异常结果.
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.
例如,十六进制值为:
050003d40201414c4552542d42656c6f772038353435206966204e462054726164657320666f722031352d3230206d696e75746573202c77617463682050414e49432050414e4943207570746f20353439332d2d383437360d0a0d0a53656c6c204549434845522061742032343931302e2e2e73746f706c6f7373732032353
我使用hex2text函数得到的结果= |
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........
(注意:我使用了hex2ascii转换网站 http://www .rapidtables.com/convert/number/hex-to-ascii.htm 以获得正确的结果)
(Note: I used hex2ascii conversion website http://www.rapidtables.com/convert/number/hex-to-ascii.htm to obtain correct results)
我的Hex2text函数是:
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
推荐答案
如Alex所述,前6个字节是导致此问题的原因.
As Alex has stated, the first 6 bytes are causing the issue.
您可以通过更改ForNext循环从第13个字符开始:
You could either start at the 13th character by changing the ForNext loop:
For i = 13 To Len(text) Step 2
或者您可以过滤任何ASCII码小于32的字符.
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
这篇关于Excel VBA中的十六进制到ASCII转换错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!