K\u1ec3 t\u1eeb phi\u00ean b\u1ea3n Excel 365, sau \u0111\u00f3 l\u00e0 phi\u00ean b\u1ea3n Excel 2019, Microsoft \u0111\u00e3 n\u00e2ng c\u1ea5p th\u00eam cho Excel m\u1ed9t s\u1ed1 h\u00e0m h\u1ebft s\u1ee9c c\u00f3 \u00edch, trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, \u0111\u00f3 l\u00e0 h\u00e0m TEXTJOIN s\u1ebd gi\u00fap ch\u00fang ta x\u1eed l\u00fd v\u1ea5n \u0111\u1ec1 \u0111ang g\u1eb7p ph\u1ea3i.<\/p>\n
M\u1ed9t c\u00f4ng th\u1ee9c chung cho c\u00e1c tr\u01b0\u1eddng h\u1ee3p ch\u00fang ta c\u1ea7n x\u1eed l\u00fd l\u00e0<\/p>\n
=TEXTJOIN<\/span>(\"\"<\/span>, TRUE<\/span>, IFERROR<\/span>(MID<\/span>(A2<\/span>, ROW<\/span>(INDIRECT<\/span>( \"1:\"<\/span>&LEN<\/span>(A2<\/span>))), 1<\/span>) *1<\/span>, \"\"<\/span>))<\/code><\/pre>\nSau khi nh\u1eadp c\u00f4ng th\u1ee9c tr\u00ean, b\u1ea1n c\u1ea7n b\u1ea5m t\u1ed5 h\u1ee3p ph\u00edm CTRL + SHIFT + Enter \u0111\u1ec3 c\u00f3 k\u1ebft qu\u1ea3<\/p>\n
\u0110\u1ed1i v\u1edbi phi\u00ean b\u1ea3n Excel 365, ch\u00fang ta con c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau \u0111\u00e2y<\/p>\n
=TEXTJOIN<\/span>(\"\"<\/span>, TRUE<\/span>, IFERROR<\/span>(MID<\/span>(A2<\/span>, SEQUENCE(LEN<\/span>(A2<\/span>)), 1<\/span>) *1<\/span>, \"\"<\/span>))<\/code><\/pre>\nH\u00e3y c\u00f9ng theo d\u00f5i v\u00ed d\u1ee5 sau \u0111\u00e2y<\/p>\n
![\"C\u00e1ch](\"https:\/\/blog.hocexcel.online\/wp-content\/uploads\/2021\/07\/loc-bo-chu-khoi-o-du-lieu.png\")
<\/p>\n
C\u00e1ch c\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u1ed9ng nh\u01b0 sau<\/strong><\/p>\n\u0110\u1ed1i v\u1edbi b\u1ea5t k\u00ec c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p n\u00e0o, \u0111\u1ec3 hi\u1ec3u \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c, c\u00e1c b\u1ea1n h\u00e3y b\u1eaft \u0111\u1ea7u t\u1eeb l\u1edbp trong c\u00f9ng c\u1ee7a c\u00f4ng th\u1ee9c. V\u1edbi 2 c\u00f4ng th\u1ee9c \u1edf tr\u00ean, \u0111i\u1ec1u n\u00e0y c\u0169ng kh\u00f4ng ph\u1ea3i ngo\u1ea1i l\u1ec7. Gi\u1ea3 s\u1eed trong \u00f4 A2 ch\u00fang ta c\u00f3 n\u1ed9i dung l\u00e0\u00a0234 Summerset Avenue<\/em><\/code><\/p>\n\n- Ch\u00fang ta s\u1eed d\u1ee5ng c\u1ee5m\u00a0
ROW(INDIRECT(\"1:\"&LEN(A2)))<\/code>\u00a0ho\u1eb7c\u00a0SEQUENCE(LEN(A2))<\/code>\u00a0\u0111\u1ec3 t\u1ea1o ra m\u1ed9t chu\u1ed7i s\u1ed1 th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n b\u1eaft \u0111\u1ea7u t\u1eeb 1 \u2013 t\u01b0\u01a1ng \u1ee9ng v\u1edbi \u0111\u1ed9 d\u00e0i c\u1ee7a d\u1eef li\u1ec7u trong \u00f4 A2:
\n{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}<\/code><\/li>\n- Sau \u0111\u00f3 k\u1ebft qu\u1ea3 c\u1ee7a c\u1ee5m c\u00f4ng th\u1ee9c n\u00e0y, ch\u00fang ta s\u1ebd d\u00f9ng l\u00e0m tham s\u1ed1 start_num c\u1ee7a h\u00e0m MID nh\u01b0 sau
\nMID(A2, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}, 1)<\/code><\/li>\n- K\u1ebft qu\u1ea3 c\u1ee7a vi\u1ec7c \u00e1p d\u1ee5ng h\u00e0m MID: t\u00e1ch ri\u00eang t\u1eebng k\u00fd t\u1ef1 trong \u00f4 d\u1eef li\u1ec7u A2 nh\u01b0 sau
\n{\"2\";\"3\";\"4\";\" \";\"S\";\"u\";\"m\";\"m\";\"e\";\"r\";\"s\";\"e\";\"t\";\" \";\"A\";\"v\";\"e\";\"n\";\"u\";\"e\"}<\/code><\/li>\n- Sau \u0111\u00f3, ch\u00fang ta s\u1ebd l\u1ea5y m\u1ea3ng d\u1eef li\u1ec7u n\u00e0y v\u00e0 nh\u00e2n v\u1edbi 1. Nh\u1eefng gi\u00e1 tr\u1ecb l\u00e0 s\u1ed1 \u1edf trong m\u1ea3ng s\u1ebd v\u1eabn l\u00e0 ch\u00ednh n\u00f3, c\u00f2n nh\u1eefng gi\u00e1 tr\u1ecb kh\u00f4ng ph\u1ea3i l\u00e0 s\u1ed1 s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0\u00a0
#VALUE!<\/code>
\n{2;3;4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}<\/code><\/li>\n- Khi k\u1ebft h\u1ee3p v\u1edbi h\u00e0m IFERROR th\u00ec ch\u00fang ta s\u1ebd chuy\u1ec3n \u0111\u01b0\u1ee3c nh\u1eefng gi\u00e1 tr\u1ecb l\u1ed7i\u00a0
#VALUE!<\/code>\u00a0v\u1ec1 gi\u00e1 tr\u1ecb\u00a0\"\"<\/code>
\n{2;3;4;\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\"}<\/code><\/li>\n- Sau \u0111\u00f3, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m TEXTJOIN \u0111\u1ec3 c\u00f3 th\u1ec3 n\u1ed1i nh\u1eefng th\u00e0nh ph\u1ea7n c\u1ee7a m\u1ea3ng tr\u00ean l\u1ea1i v\u1edbi nhau b\u1eb1ng\u00a0
\"\"<\/code>\u00a0v\u00e0 b\u1ecf qua nh\u1eefng gi\u00e1 tr\u1ecb r\u1ed7ng
\nTEXTJOIN(\"\",true,{2;3;4;\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\"})<\/code><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"C\u00e1ch l\u1ecdc b\u1ecf ch\u1eef v\u00e0 gi\u1eef l\u1ea1i s\u1ed1 trong \u00f4 Excel C\u00e1c c\u00f4ng th\u1ee9c sau \u0111\u00e2y ho\u1ea1t \u0111\u1ed9ng trong phi\u00ean b\u1ea3n Excel 365 v\u00e0 Excel 2019.\u00a0 N\u1ebfu b\u1ea1n c\u1ea7n m\u1ed9t gi\u1ea3i ph\u00e1p ho\u1ea1t \u0111\u1ed9ng cho c\u00e1c phi\u00ean b\u1ea3n Excel, h\u00e3y theo d\u00f5i ti\u1ebfp b\u00e0i vi\u1ebft \u1edf d\u01b0\u1edbi. K\u1ec3 t\u1eeb phi\u00ean b\u1ea3n Excel 365, sau \u0111\u00f3 […]<\/p>\n","protected":false},"author":3,"featured_media":9097,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-9096","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tin-tuc"],"_links":{"self":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/9096","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/comments?post=9096"}],"version-history":[{"count":0,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/9096\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media\/9097"}],"wp:attachment":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media?parent=9096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/categories?post=9096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/tags?post=9096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}