3

Currently working on an Excel spreadsheet that I need to automate conversion from 16 charter octal numbers to its 48 bit character equivalent. I am trying to automate data that is about 27,000+ rows in size.

Example.

6401034316154000   = 110100000001000011100011001110001101100000000000‬
6401034651710000   = 110100000001000011100110101001111001000000000000
6401034646734000   = 110100000001000011100110100110111011100000000000
6401034312554000   = 110100000001000011100011001010101101100000000000

Thank you in advance‬

4
  • What have you tried? 48 digits (binary) exceed the capability of Excel.
    – Ramhound
    Dec 10, 2019 at 7:15
  • You will have to split your 16 octal number to 3x5 + 1 substrings in and then catenate the results. Otherwise, the numbers will be too large to use Oct2Bin
    – Ramhound
    Dec 10, 2019 at 7:22
  • Does you want to do this using formula or macros?
    – Akina
    Dec 10, 2019 at 7:23
  • 2
    This has been asked before
    – Ramhound
    Dec 10, 2019 at 7:23

2 Answers 2

2
B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","000"),"1","001"),"2","010"),"3","011"),"4","100"),"5","101"),"6","110"),"7","111")

A1 must be a string type value.

1
  1. First prepare a table for binary numbers Up to 8 as under in 2 columns so as to substitute the binary value of octal numbers as given below

  2. 0, 000---; 1, 001---;2,010---; 3, 011---;4, 100---;5,101---;6, 110---;7,111. Suppose the Table is in the Cells V4:W11.

  3. For every bit of your 16 digits; use vertical look up formula to get value of the bit place it in a cell. Copy the formula for all 16 digits using absolute reference

  4. Complete all 16 bits value search from the Table of Step 2.

  5. For eg. for your 16 Digits given number the vertical look up value of first digit 6 = 110, ( =Vlookup(digit,$V$4:$W$11,2)..(Value is in Second Column of the Table) ) Second digit 4 = 100 Third Digit 0=000, 4th Digit 1 is 001 and so on for all 16 Characters.

  6. Now you combine all 48 digits found from 16 digits by combining them using concatenate Formula eg. Digit1&Digit2&Digit3&.......&Digit16.

  7. This will give you the desired string of length 48 now in binary.

  8. Copy formula down the rows to get the desired results.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .