excelstringexcel-formulaasciilibreoffice-calc

How can I convert text in a cell to a sequence of ascii code values with a separator in Excel 2016 or LibreOffice?


Given a cell with text, I need a formula that will output the sequence of ascii codes for each char in that text string.

Examples (with space as separator: input -> output

The closest code examples I could find was here :

In both cases above, I was unable to adapt the examples to use CONCATENATE to join the pieces into an Ascii code sequence.


Solution

  • For Excel 2016, a formula-based solution will only be feasible for strings containing no more than 5 characters, viz:

    =TRIM(SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT(10^(15-3*(ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))-1))*CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))),REPT(" 000",6))," 00"," ")," 0"," "))

    Otherwise, you'll require VBA.