regexlibreoffice-calccaesar-cipher

Character shift with spreadsheet

I want to basically do a Ceasar Cipher with LibreOffice Calc (same as Excel). I don't want to use macros and I'm trying to use only formulas. First I'm trying to get the ASCII code of each character of a string.

I tried with REGEX and I can't manage to get what I want. First I want to get the UNICODE of each character. I managed to get :

=REGEX(K15,"([a-zA-Z])",UNICODE("$1"),"g")

With K15 being where the text is which is "test".

I get as a result "36363636". I only get the code character of the dollar sign 4 times. What am I doing wrong ?

Thanks

Solution

  • Since you didn't mention any requirement about limiting to a single formula, we can do this with helper columns. To start off, I added the shift distance in K16, using 7 as an example.

    Now in L15, add the following formula to extract the first letter. Fill down to L18 by dragging. (The ROW() functions are for convenience — you could simply change the values to 1, 2, 3, and 4 instead).

    =MID(K$15;1+ROW()-ROW(K$15);1)

    Next comes the big formula in M15 that does most of the work. Fill down to M18.

    =IF(CODE(L15)+K$16>CODE("z");CHAR(CODE(L15)+K$16-CODE("z")+CODE("a")-1);CHAR(CODE(L15)+K$16))

    The formula converts to code point and then adds the shift distance. If it goes past the code point of z, then wrap around to the code point of a. Finally, convert the code point back to the character.

    When that is done, simply add =TEXTJOIN(;;M15:M18) to get the result, which yields alza. I put it in K17.

    result "alza"