Forum Discussion
Solved
Creating/generating simple codes (cyphers) for kids using Excel
Hi,
Is it possible to use Excel to create simple codes (cyphers) for kids?
I have three ‘codes’ in mind:
An alpha code (where the letters are transposed with the letter before) such as A = Z, D=C etc. so the word “FRIEND” would be “EQHDMC”
A numeric code where each letter is the number in the alphabet, (A=1, D = 4, K=11) so the word “FRIEND” would be “6-18-9-5-14-4”
A word puzzle (where the letters in a word are jumbled randomly) so the word “FRIEND” might be “DRNEIF” or RFNEID” and so on.
The words will be either 5,6,7, or 8 letters long.
What I want to do is type a word (5,6,7,or 8 letters) in cell A1, and the results of the alpha code shows in C1, the numeric code shows in D1 and the word puzzle shows in E1.
I’m sure this can be done in Excel (using LOOKUP?, or the RAND formula), but my skills are nowhere near good enough to work out how. Can anyone out there help please?
See the attached demo.
- Hi Hans,
You're a genius!! This is exactly what I needed and wanted.
I'm now going to study the formulas used to see if I can discover how they work.
I'm very grateful!!
Many regards- Thanks for your reply Mathetes, I like what you've done and I think I will be able to use it for something else I'm working on!
Some time ago, I came up with a method to generate those often needed 4-digit codes we might need for such things as bike locks. I'm sure now, with the advent of, and my better understanding of, some of the newer functions, I could make this work more elegantly. Unlike the cyphers you were looking for, this does not necessarily generate unique codes for any given word; that wasn't my goal, which was to just come up with a code based on a word I could associate with the application. BIKE, therefore, becomes 2-9-2-5, and if it's been a long time since I last used my bike, I can quickly reconstruct the code.
My approach is a bit less straight forward than your second one, so a bit less "crack-able." Essentially, I reduce every letter to a single digit. The number 7 represents G, the 7th letter, and Q, the 16th, and Y, the 25th. T, the 19th letter becomes 10, which becomes 1.
But I love what HansVogelaar did with your request. He's a superstar in these parts.
See the attached demo.
Thanks for your most helpful earlier replies. I have now thought of another sort of cypher which I believe is probably possible to create using formulae in Excel.
I want to be able to type in a word (usually six or seven letters) into cell A1, and using a formula to show in cell B1 the word (with all the consonants in the same order as in the original word) with all its vowels removed. So if A1 = FREEDOM, B1 will be FRDM, and if A1 = BREAKER then B1 will be BRKR. Can anyone create a formula which will do this please? It's certainly way beyond my Excel skills (such as they are!)
Regards,
Les King
For example:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"A",""),"E",""),"I",""),"O",""),"U","")
- Hi Hans, I have been trying to put the "-" delimiter into the other two results in cells C1 and E1 as that would work better for me. I have been trying to use the TEXTJOIN function with the two formulas you have provided, but I'm obviously doing something wrong and have given up after many tries. Can you help please - and when I see your formulas I can checkout why my efforts haven't worked. Thanks a lot.
In C1:
=TEXTJOIN("-",TRUE,CHAR(LET(c, CODE(MID(UPPER(A1),SEQUENCE(LEN(A1)),1))-1, IF(c=64,90,c))))
In E1:
=TEXTJOIN("-",TRUE,SORTBY(MID(UPPER(A1),SEQUENCE(LEN(A1)),1),RANDARRAY(LEN(A1))))
