Oct 14 2021 03:52 PM
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.
Oct 15 2021 02:35 AM
SolutionSee the attached sample workbook. It uses a custom VBA function, so you'll have to allow macros.
Oct 15 2021 02:37 AM
Oct 15 2021 04:51 AM
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?
Oct 15 2021 02:35 AM
SolutionSee the attached sample workbook. It uses a custom VBA function, so you'll have to allow macros.