Tách số ra khỏi chuỗi tên.
Link: Onedrive
 |
| Tách số ra khỏi chuỗi |
Công thức:
1.=LOOKUP(9^6,--LEFT(TRIM(MID(SUBSTITUTE("x"&MID(LOWER($A2),7,99),"x",REPT(" ",20)),COLUMN(A1)*20,20)),ROW($1:$20)))
2.=LOOKUP(10^3,--LEFT(INDEX(TRIM(MID(SUBSTITUTE(MID(LOWER($A2),7,100),"x",REPT(" ",20)),{0;1;2}*20+1,20)),COLUMN(A1)),ROW($1:$10)))
3.=AGGREGATE(14,6,--MID(TRIM(MID(SUBSTITUTE(MID(LOWER($A2),7,100),"x",REPT(" ",20)),COLUMN(A:A)*20-20+1,20)),ROW($1:$1),COLUMN($A:$G)),1)
4.=DROP(REDUCE(0,A2:A10,LAMBDA(x,v,VSTACK(x,LET(a,MID(v,ROW(1:50),1),--TEXTSPLIT(CONCAT(IF((a>="0")*(a<="9")+(a="."),a," "))," ",,1))))),1)
5.=DROP(REDUCE(0,A2:A10,LAMBDA(x,v,VSTACK(x,--REGEXEXTRACT(v,"\d+\.?\d*",1)))),1)