title: Convert binary data to HEX in Google sheets
body: <p>I have a Google sheet document with a column containing binary data. The binary data is 4 32-bit numbers. This is an example ASCII hex representation of that binary data from one cell: <code>c0a80123000006150000000180004203</code>.</p>
  
  <p>Is there a way in Google sheets to convert this binary number to hex string. I'm looking for something like: <code>BIN2HEX(data[0]) = "0xc0"</code>. Regular <code>BIN2HEX</code> doesn't work because the data is larger than it can handle.</p>
  
  <p><a href="https://drive.google.com/open?id=1kwBseNotyHPI7_gfOXDaKtAV8F1Wl4SK" rel="nofollow noreferrer">Raw text sample file</a>. <a href="https://docs.google.com/spreadsheets/d/1ul62eCZmuuk3vhLFXQu133GpchWlleZnn5-emYmYsE0/edit?usp=sharing" rel="nofollow noreferrer">Converted google sheet</a>.</p>
  
  <hr>
  
  <p>Update:</p>
  
  <p>Based on one suggestion I've created a Google API script to process the binary data. However, the results come strange to say the least.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>function extractip(binary_data) {
    var blob = Utilities.newBlob(binary_data);
    var ip1 = blob.getBytes()[0];
    var ip2 = blob.getBytes()[1];
    var ip3 = blob.getBytes()[2];
    var ip4 = blob.getBytes()[3];
    
    return Utilities.formatString("%u.%u.%u.%u", ip1, ip2, ip3, ip4);
  }</code></pre>
  </div>
  </div>
  </p>
  
  <p>The first 4 bytes in the binary blob represent an IP address, in the form of 192.168.0.X in the attached example. However, the output comes back something like <code>-17.-65.-67.-17</code>.</p>