Convert letters to different letters in a string

[a_heb2275]

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.

[#2 · Accepted solution]

@a_heb2275 

See the attached sample workbook. It uses a custom VBA function, so you'll have to allow macros.

[#3]

Fantastic, thank you very much for your help.

[#4]

@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?