Convert a hex string to base64 in an Excel function

Question

I have a lengthy string of hex values to convert to base64.

I'm looking for a simple format cell function such as =Hex2b64(Hexstring) that will accept any length of hex characters.

I have been using http://home.paulschou.net/tools/xlate/ to do my conversion manually. The conversion works and the data is received by all relevant databases and parsed appropriately.

The data I am receiving is hex represented binary, which has been converted in multiple blocks and concatenated into long hex strings in accordance with project documentation that I am not privy to.

A typical Input String would be:

Hex= 00014088F6650101393939393939392D30304646463238313030000343332353430342D35353FA10000002805900100002805

and the corresponding output would be:

B64 = AAFAiPZlAQE5OTk5OTk5LTAwRkZGMjgxMDAAA0MzI1NDA0LTU1P6EAAAAoBZABAAAoAF

Welcome to stack overflow. Please see this site FAQ on How to ask a good question. — RubberDuck
– RubberDuck, Commented Apr 28, 2014 at 18:11
Why would you convert a hex string to base64? Normally you would only use base64 to represent binary data or data which contains potentially bad characters for the container in which you intend to store it. If you really do want to convert it to base64, I can help, but think about this carefully. Converting data to base64 results in a 4/3 expansion in the size of your data. The only case in which I can see this being useful is if you need to interface with an existing website or web service which only accepts base64 encoded input. — Blackhawk
– Blackhawk, Commented Apr 28, 2014 at 18:14
...or do you perhaps mean that you have binary data which you are temporarily representing as Hex strings (perhaps to allow it to exist in Excel cells), which you wish to a) convert back to binary data and b) convert the binary data to base64? — Blackhawk
– Blackhawk, Commented Apr 28, 2014 at 18:16
Give us a simple example of a typical input string and what you expect as output. — Gary's Student
– Gary's Student, Commented Apr 28, 2014 at 18:24

omegastripes · Accepted Answer · 2019-04-16 11:49:11Z

5

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

edited Apr 16, 2019 at 11:49

answered Apr 28, 2014 at 18:28

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

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

2 Comments

WickedMongoose Over a year ago

I have been using home.paulschou.net/tools/xlate to do my conversions, but needed a way to automate the process. This code does not seem to provide the same answers as the website converter. For example, I'm inputting HEX = 00014088F66501013 and getting B64 = AAFAiPZlAQED but only get a #value from your block of code

Stupid_Intern Over a year ago

@omegastripes Hi how can I make it work for hex to HEX to Base58 ?

Collectives™ on Stack Overflow

Convert a hex string to base64 in an Excel function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related