D2{=IFERROR(INDEX(MID(CLEAN(B$2:B$8),COLUMN(A:I)*4-3,4),SMALL(IF(LEN(CLEAN(B$2:B$8))/4>=COLUMN(A:I),ROW($1:$7)),ROW(A1)),RIGHT(SMALL(IF(LEN(CLEAN(B$2:B$8))/4>=COLUMN(A:I),ROW($1:$7)/1%+COLUMN(A:I)),ROW(A1)),2)),"")
另解{=IFERROR(MID(CLEAN(INDEX(B:B,SMALL(IF(LEN(CLEAN(B$2:B$8))/4>=COLUMN(A:J),ROW($2:$8)),ROW(A3)))),MOD(SMALL(IF(LEN(CLEAN(B$2:B$8))/4>=COLUMN(A:J),ROW($2:$8)/1%+COLUMN(A:J)),ROW(A3)),100)*4-3,4),"")
字首數字有誤=MID(CLEAN(PHONETIC(B$2:B$8)),ROW(A1)*4-3,4)
留言
張貼留言