Forum Discussion
Convert letters to different letters in a string
Hello, I was wondering if someone could help me and my achy brain?
I am looking to have an input and output column, the input being normal letters a-z (A2 to A28), and the output being letters in a different order (B2 to B28), so it will be used as a made-up language. I would like to write in cell D2 abcdefg and E2 to convert that to tuvwxyz (for example).
Thank you in advance.
See the attached sample workbook. It uses a custom VBA function, so you'll have to allow macros.
3 Replies
Just for play, this is a 365 version of the Caesar cypher (it cycles letters by a shift value)
= LET( split, MID(text, SEQUENCE(LEN(text)),1), noCaps, MOD(CODE(split),32), code, 97+MOD(noCaps+shift-1,26), CONCAT(CHAR(IF(noCaps, code,160))))Naturally, I wouldn't leave it there, so wrapping in within a Lambda function, 'ENCODEλ',
= LAMBDA(text,shift, LET( split, MID(text, SEQUENCE(LEN(text)),1), noCaps, MOD(CODE(split),32), code, 97+MOD(noCaps+shift-1,26), CONCAT(CHAR(IF(noCaps, code,160)))) )the worksheet function reduces to
= ENCODEλ(text,shift)and to decode to result
= ENCODEλ(encodedText,-shift)Sometimes I am sure people think I am exaggerating when claim that future Excel bears very little resemblance to traditional spreadsheets! What do you think?
See the attached sample workbook. It uses a custom VBA function, so you'll have to allow macros.
- Fantastic, thank you very much for your help.
