Forum Discussion

a_heb2275's avatar
a_heb2275
Copper Contributor
Oct 14, 2021
Solved

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...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 15, 2021

a_heb2275 

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?

 

Resources