Apr 7, 2021

Need help with cipher translator that I built in Google Sheets

Hello all,

I created a fictional language for a story that I'm writing. This language is essentially a cipher. How the cipher works is as follows. 1. the letters in the word are all separated. 2. the individual letters are converted to their number equivalents (a=1, b=2, etc.). 3. the number corresponding to each letter is added to the corresponding digit of pi (i.e. if the word is 'abc', it would become '1 2 3', after the pi addition it would be 1+first digit of pi, 2+second digit of pi, 3+third digit of pi). 4. if the resulting number is above 26 (number of letters in the alphabet) it runs a modulo on the number. 5. it converts the number back to a letter. 6. it combines the letters into one cell. I'm sorry if I explained this poorly.... Because the first letter of the word must always be matched to the first digit of pi, the second letter must be matched with the second digit of pi, etc... I'm not sure what to do to allow the translator to translate multiple words at once.

 
Thanks for the help!

EDIT: the google sheet is now editable.
EDIT: Thank you Jon!
EDIT: link is now updated.
EDIT: Jon, you're a legend.
Locked
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Apr 8, 2021
Recommended Answer
Apr 8, 2021
Ugh. 
App doesn't like this sheet apparently.


I'll post the formulas and steps momentarily. 
I added a section in the event you want to catalog words in a more "permanent" manner.
Original Poster jasper1378 marked this as an answer
Helpful?
Recommended Answer
Apr 8, 2021
Hello, 

I think I have a working solution for translation from English. 

Your sheet is view only though so I cannot place it in your sheet. If you change the settings to allow editing I (or others) can put solutions directly in the sheet. Make a backup copy if necessary. 

A quick summary of my solution (I worked on a copy of your sheet) and for you to try if I'm unable to get in the sheet right away after you open for editing. 


In a new sheet I did the following (see screen for ease) 
Created a table in A-E to lookup necessary components. 
Pull the word(s) to translate
Separate if multiple words
Run the formula
Join it together. 

In F4
=TRANSPOSE(SPLIT(F2," "))

In G4 (and copied down the column)
=IFNA( ARRAYFORMULA( TEXTJOIN(,, VLOOKUP( MOD( VLOOKUP( TRANSPOSE( SPLIT( REGEXREPLACE(F4,"","|"),"|")), $A$1:$B$26,2,0)+ VLOOKUP( SEQUENCE( LEN(F4)), $B$1:$C$26,2,0),26), $D$1:$E$26, 2,0))), )




However I ran into an issue with Y so I'll need to tweak the formula to only run MOD when >26. Which I might just be able to duplicate the formula through an IF. 

PS
of course everything doesn't need to be one formula but makes it much tidier. Although the individual steps could likely still be done in a tidy manner. And that would make it easier for the MOD() portion. 
I'll take a look again tomorrow.
Original Poster jasper1378 marked this as an answer
Helpful?
All Replies (2)
Recommended Answer
Apr 8, 2021
Hello, 

I think I have a working solution for translation from English. 

Your sheet is view only though so I cannot place it in your sheet. If you change the settings to allow editing I (or others) can put solutions directly in the sheet. Make a backup copy if necessary. 

A quick summary of my solution (I worked on a copy of your sheet) and for you to try if I'm unable to get in the sheet right away after you open for editing. 


In a new sheet I did the following (see screen for ease) 
Created a table in A-E to lookup necessary components. 
Pull the word(s) to translate
Separate if multiple words
Run the formula
Join it together. 

In F4
=TRANSPOSE(SPLIT(F2," "))

In G4 (and copied down the column)
=IFNA( ARRAYFORMULA( TEXTJOIN(,, VLOOKUP( MOD( VLOOKUP( TRANSPOSE( SPLIT( REGEXREPLACE(F4,"","|"),"|")), $A$1:$B$26,2,0)+ VLOOKUP( SEQUENCE( LEN(F4)), $B$1:$C$26,2,0),26), $D$1:$E$26, 2,0))), )




However I ran into an issue with Y so I'll need to tweak the formula to only run MOD when >26. Which I might just be able to duplicate the formula through an IF. 

PS
of course everything doesn't need to be one formula but makes it much tidier. Although the individual steps could likely still be done in a tidy manner. And that would make it easier for the MOD() portion. 
I'll take a look again tomorrow.
Original Poster jasper1378 marked this as an answer
Recommended Answer
Apr 8, 2021
Ugh. 
App doesn't like this sheet apparently.


I'll post the formulas and steps momentarily. 
I added a section in the event you want to catalog words in a more "permanent" manner.
Original Poster jasper1378 marked this as an answer
false
972512378146509884
true
Search Help Center
true
true
true
true
true
35
Search
Clear search
Close search
Main menu
false
false