Search code examples
excelautofill

Excel Autofill nth cell value in left direction

These are the cells I have

T 1 2 3 H 1 2 3 A 1 2 3 N 1 2 3

I am trying to use autofill to fill in left direction order

N A H T

When I get K,1,2,3 in the first table, I just want to insert a cell in the very left side of the second table, drag, and automatically fill in the cell with K, and so on.

Any idea for nth cell autofill data in left direction order in Excel?

Solution

  • My results are here..

    Result

    =LET(a,B1:Q1,b,TEXTJOIN(,,IF(ISTEXT(a),a,"")),MID(b,SEQUENCE(,LEN(b),LEN(b),-1),1))

    =MID(TEXTJOIN(,,IF(ISTEXT(B1:Q1),B1:Q1,"")),SEQUENCE(,SUMPRODUCT(--ISTEXT(B1:Q1)),SUMPRODUCT(--ISTEXT(B1:Q1)),-1),1)