Search code examples
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.

  • the input string can be assumed to have chars with ascii values within the 0-255 range
  • the input string is typically 4 characters or less but may be up to 10
  • the output should include a separator, e.g. space (" ")
  • ideally, the output should not end with the separator
  • the formula must work in Excel 2016 or LibreOffice (my online access is limited)

Examples (with space as separator: input -> output

  • abc -> 97 98 99
  • YZ[ -> 89 90 91
  • T -> 84

The closest code examples I could find was here :

  • =SUM(CODE(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1))) which uses Array Formulas
  • =SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) which avoids Array Formulas

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.