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 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 Google Sheets)
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.
CodePudding user response：
If you have Office 365:
=TEXTJOIN(" ",,CODE(MID(A1,SEQUENCE(LEN(A1)),1)))
or
= ARRAYTOTEXT(CODE(MID(A1,SEQUENCE(LEN(A1)),1)),0)
CodePudding user response：
For Excel 2016, a formula-based solution will only be feasible for strings containing no more than 3 characters, viz:
=TRIM(SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT(10^(6-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)))," 000 000 000")," 00"," ")," 0"," "))
Otherwise, you'll require VBA.