Follow-up: Converting binary code to text in Excel

This exercise was originally posted at: http://maritimeorca0.blogspot.com/2016/04/converting-binary-code-to-text-in-excel.html

The Task

0100001101101111011011100111011001100101011100100111010000100000011101000110100001101001011100110010000001100010011010010110111001100001011100100111100100100000011000110110111101100100011001010010000001101001011011100111010001101111001000000111010001100101011110000111010000100000011101010111001101101001011011100110011100100000011100110111000001110010011001010110000101100100011100110110100001100101011001010111010000100000011100110110111101100110011101000111011101100001011100100110010100100000011100110111010101100011011010000010000001100001011100110010000001001101011010010110001101110010011011110111001101101111011001100111010000100000010001010111100001100011011001010110110000101110

Copy the binary code above into Excel and create formulas to convert it into readable text.

My Solution

My spreadsheet with my solution can be found at: https://onedrive.live.com/redir?resid=2E6EFB80915ED5BD%2111552

I started by pasting the binary code into cell A1. The first task was to divide the 8-digit segments. I used the COLUMN function so I would be able to fill the formula and be able to calculate the location of the 8-digit segment. This would mean that the first character will be calculated in column A with a 1 to calculate the location of its 8-digit segment, the second character will be calculated in column B with a 2 to locate the location of its 8-digit segment, and so on. Alternatively, values can be placed in one row as an intermediate step by filling to the right (1, 2, 3, etc), and we can reference the values in that row. I have included this alternative solution in my spreadsheet below my primary solution.

From here, we need to calculate where each value actually starts. The first two 8-digit segments will be the first through eighth characters and the ninth through sixteenth. Multiplying the column by eight would provide the end instead of the start. Since all segments are 8 digits long, you can subtract 7 to go from the 8th to the 1st digit. This could read as (COLUMN(A1))*8-7.

Now, we can use the MID function. What we want to do is tell Excel to look at the code in A1, take the text that starts at the position we just calculated, and take eight of the digits. This will appear as MID($A$1,(COLUMN(A1))*8-7,8)

Now that we have the binary value isolated, we need to convert to a decimal value. If you know Excel functions should have no problems with this. Just feed the last calculation into BIN2DEC like this: BIN2DEC(MID($A$1,(COLUMN(A1))*8-7,8))

Again, we have an easy step. This latest calculation can be fed directly into the CHAR function to generate each character of the code. My final formula is: =CHAR(BIN2DEC(MID($A$1,(COLUMN(A1))*8-7,8)))

From here, I fill to the right. By copying directly from Excel, the converted text shows:

C o n v e r t t h i s b i n a r y c o d e i n t o t e x t u s i n g s p r e a d s h e e t s o f t w a r e s u c h a s M i c r o s o f t E x c e l .

Okay. We might have some problems. When pasting as text, Excel usually inserts tabs to divide cells. Either concatenate in Excel will work, or we can copy into Word and replace the tab character with nothing. That would give us:
Convert this binary code into text using spreadsheet software such as Microsoft Excel.

Notes

This challenge might be revisited in the future to generate the code from the text.

Excel has added new concatenation formulas CONCAT() and TEXTJOIN() since this exercise was originally developed.

Feel Free to Comment

Now that this exercise has concluded, feel free to share any thoughts that you had regarding results, your approach, and any other ideas that popped in your head regarding this exercise.I do not provide storage to those who visit my blog. If you would like to share any files, you are going to have to find your own online storage. At that point, you can provide links with your comments. I would like to point out that macros can create security risks. If possible, save all documents as a standard workbook (not macro-enabled) unless macros are vital to your solution. If you are going to download any solutions, make sure that you are not loading macros by default. If a spreadsheet includes macros, check the code before running them.

Upon completion of this exercise, please fill out the following form:
https://docs.google.com/forms/d/1ztISBIIxe5wqiJ3Rludt92gwcVrelEcT6ACM57bc8ec/viewform?usp=send_form
Responses to the above form are collected and stored in a spreadsheet that can be viewed at:
https://docs.google.com/spreadsheets/d/1XDDpk3gQxY-tAluexoqwSkkRGkli94SzdRDbUscwHIc/pubhtml