Excel
- This topic has 3 replies, 4 voices, and was last updated 4 days, 22 hours ago by millie.
-
AuthorPosts
-
-
12th November 2026 at 4:36 pm #166172justUrLocalSushiParticipant
How do I use excel to help with the transposition cipher? I’ve almost never used it before and I’m really confused about how to lay it out so the text writes down the columns.
-
14th November 2026 at 1:02 pm #166272giiiParticipant
Hello, I’m not sure how much I can reveal, but the idea is to input all the text in one of the cells and then take a specific letter in the sequence. I numbered off the rows and then the columns. There will only be a few columns though, as the key length will not be that long. As a result, the number of rows will go into the few-hundreds.
Once you’ve numbered them, you need to use a formula in each of the cells. I don’t want to give away the full formula I used, but to do what I said above you will need ‘=MID(…)’. Excel tells you what each part does. For each of the parameters, you can use the number of the row and column that your cell is in, to get the exact letter. It is crucial there are no spaces in the string.
I hope this isn’t too vague. This method generalises for either a block permutation cipher or a columnar transposition cipher.
N.B. You don’t have to type the formula into each cell, at the very least I’m sure you know Excel lets you drag formulas and values if you have a pattern. Good luck!
-
14th November 2026 at 5:04 pm #166285Cryptical_ThinkingParticipant
Hi there, justUrLocalSushi
I have used Excel a lot in previous years, and while I a trying to shift to using Python, I do sometimes revert to Excel for simplicity. Throughout my Excel endeavours, I have find one formula really useful for managing text:
=MID(A1,SEQUENCE(1,LEN(A1),1,1),1)
This takes the text in cell A1 and returns the text split into each character across a row of cells, with each character with its own cell. The two parameters in bold are the values use for the number of rows (1) and the number of columns (length of the text in A1). With a bit of configuration, you should be able to work out how to change the two values to make it output the text in a grid, and you’ll hopefully find this formula very useful.
However, the text is written by rows, not columns, so I would recommend further understanding the MID function, ad try doubling up this formula with another to first split the text into a number of blocks equal to the number of columns you want, and then underneath each block of text, you split it by letter with the rows being the length of the text block above, and the column being 1.
Hope this helps
PS – This is my first forum post so apologies if the formatting is a bit off
-
14th November 2026 at 8:50 pm #166293millieParticipant
you can lay out an array of numbers, then put you text in a separate cell. after this use the =mid() function to place a letter into each cell with a starting letter corresponding to one of the numbers in your array. something like =mid($A$1,C3,1) where ur block of text is in A1
-
-
AuthorPosts
- You must be logged in to reply to this topic.