Clever Geek Handbook

Convert hex string to base64 in Excel function

I have a long string of hex values ​​to convert to base64.

I am looking for a simple format cell function such as =Hex2b64(Hexstring)

that will accept any length of hex characters.

I used http://home.paulschou.net/tools/xlate/ to do my conversion manually. The transformation works and the data goes into all the relevant databases and is parsed accordingly.

The data I receive is binary data in hexadecimal format that has been converted to multiple blocks and concatenated into long hexadecimal strings according to the design documentation, which I have nothing to do with.

A typical input line would be:

Hex= 00014088F6650101393939393939392D30304646463238313030000343332353430342D35353FA10000002805900100002805

and the corresponding output would be:

B64 = AAFAiPZlAQE5OTk5OTk5LTAwRkZGMjgxMDAAA0MzI1NDA0LTU1P6EAAAAoBZABAAAoAF
+2


source to share


1 answer 


Function Hex2Base64(ByVal sHex)

    Static oNode As Object
    Dim a() As Byte

    If Len(sHex) Mod 2 <> 0 Then
        sHex = Left(sHex, Len(sHex) - 1) & "0" & Right(sHex, 1)
    End If
    If oNode Is Nothing Then
        Set oNode = CreateObject("MSXML2.DOMDocument").createElement("Node")
    End If
    With oNode
        .text = ""
        .dataType = "bin.hex"
        .text = sHex
        a = .nodeTypedValue
        .dataType = "bin.base64"
        .nodeTypedValue = a
        Hex2Base64 = .text
    End With

End Function


+5


source






More articles:


All Articles