How to play with very long columns with a text editor and a spreadsheet

[F6exb]

How to split a cipher text into columns for transposition with “electronic paper and pencil”. (M$Office or LibreOffice or…).
Note: I am using french version of softwares, so maybe names of menus, formulas are not exactly as yours.
In Word or Writer:
• Clean the text with find and replace to erase spaces and punctuation.
• With “statistics” in revision menu, (or “word number” in tools menu) write the number of characters.
• If you have for example 5 columns, place a “Return” at about a fifth of the text length. Adjust with “statistics”. You do that for each column along the text. With “returns” in the middle of the text, you can easily see where is each column.
Now, in Excel or Calc:
• Copy and paste the first column from the text editor into A1 in the spreadsheet. (You can write 32767 characters in a cell), the second column in A2, and so on.
• For example in A10, write number 1.
• In A11, write number 2.
• Select the 2 cells and extend them down for equal or greater length than that of a column.
• In B10 write the formula: “=STXT($A$1;A10;1)” and extend it down.
• Select the column , copy, and do a special paste with values only, on the right, for example D10.
• Do the same for other columns of cipher text and paste to E10, F10, …
• Now you can play with the columns and move them according to the key.
• When you are satisfied, you can concatenate all columns with ” =ConcatPlage(E10: …….. ;””)
• (in french ConcatPlage is a macro I have found on the net). If you don’t knows how to program, this is another way:
• Instead of this macros, it is possible to use something very ugly but which does the job:
On the right of the first row, for example
J10: ” =CONCATENER(F10;G10;H10) (in french)
J11: ” =CONCATENER(J10;F11;G11;H11) and now you extend this cell down to the end of columns. The text gets bigger and bigger and in the last cell there is the cipher “de-transposed”.

[Author]

Posts