Forum Discussion
Creating/generating simple codes (cyphers) for kids using Excel
See the attached demo.
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","")
- Thank you so much Hans I've now discovered another function I didn't know about - and by following your formula, I have some idea of how to use it, Thanks again - you're a hero!
Regards,
Les King
- 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))))
- Thanks again, Hans. I feel like a total idiot - I didn't remove CONCAT when I added TEXTJOIN! It seems so obvious now.
Thanks again.
