Array reversal in excel-CodePudding

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.

CodePudding user response：

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

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().

CodePudding user response：

=MID(SUBSTITUTE(A1, " ", ""), SEQUENCE(1, LEN(SUBSTITUTE(A1, " ", ""))/2, LEN(SUBSTITUTE(A1, " ", ""))-1, -2), 2)