excelexcel-formula

Array reversal in excel

I got 8 bytes Hex values in a cell as below which is in little endian format 00 00 08 04 22 00 40 00

With text split I could get individual hex values in an array. = TEXTSPLIT(A1, , " ")

00 00 08 04 22 00 40 00

Is there an excel formula that I can use to grab the values in reverse order from an array to do below?

00 40 00 22 04 08 00 00

I don't want to use LEFT or MID or RIGHT extractors as I want to create generic formula that works on all data types.

Solution

  • For this very specific case you could use =TRIM(CONCAT(MID(" "&A1,SEQUENCE(8,,22,-3),3))) but to be more generic, try:

    enter image description here

    Formula in A2:

    =TEXTJOIN(" ",,SORTBY(TEXTSPLIT(A1,," "),ROW(1:8),-1))

    I suppose you can make this even more generic for any string you split on space:

     =LET(r,TEXTSPLIT(A1,," "),TEXTJOIN(" ",,SORTBY(r,SEQUENCE(ROWS(r)),-1)))

    Note this is almost an exact copy of this question where you could also use the technique shown by @ScottCraner using INDEX().