D4:H9{=-LOOKUP(9,-MID($C4,SMALL(IF(ISERR(-(0&MID($C4,ROW($1:$98),1)))*ISNUMBER(-(0&MID($C4,ROW($2:$99),1))),ROW($2:$99)),COLUMN(A1)),ROW($1:$9)))
另解{=-LOOKUP(9,-MID(SUBSTITUTE($C4,LOOKUP(COLUMN(A1),{1,2,3},{"K","N","C"}),REPT(" ",99)),IF(COLUMN(A1)<4,1,COLUMN(A1)-2)*99-ROW($1:$99),9))
另解{=-LOOKUP(9,-MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C4,"K","C"),"N","C"),"C",REPT(" ",99)),COLUMN(A1)*99-ROW($1:$99),ROW($1:$99)))
留言
張貼留言