How to Convert Hex String into a Text String (HEX2TEXT)

Goal

Datameer has a TEXT2HEX function but there is no function to convert back. See how to convert a string "String" containing hexadecimal values of characters into text.

Learn

1. Create a source sheet with a column containing hexString and a string of "537472696e67". 

2. Create a sheet called StringToConvert containing StringID, Tokens, Hex2Dec, and Characters.

3. Use the following functions to convert a hexadecimal string into characters. Use every function within a single column:

GROUPBY(1)

REGEXTRACT(#hexString!hexString;'([a-zA-Z0-9]{2})')

HEX2DEC(#Tokens)

CHAR(#Hex2Dec)

4. Transpose the column into a row by adding a new sheet called ConvertedString with:

GROUPBY(#StringToConvert!StringID)

GROUPCONCAT(#StringToConvert!Characters)

GROUPBY(#StringToConvert!StringID)

GROUPCONCAT(#StringToConvert!Characters)

5. Transfer the character list back into a single string.

For quick reference find attached the JSON definition from a workbook, which you can upload via REST API call. 

Additional Information 

To get a better performance you can leverage our Plug-in SDK. There is a prototype plugin for the Function HEX2TEXT, which is easier to use and demand fewer resources. How to create the plug-in will be covered in a separate article.

Attachements