{"id":3461,"date":"2024-12-03T02:34:40","date_gmt":"2024-12-03T02:34:40","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh\/"},"modified":"2024-12-03T02:34:40","modified_gmt":"2024-12-03T02:34:40","slug":"lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh\/","title":{"rendered":"L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 t\u00e1ch chu\u1ed7i v\u0103n b\u1ea3n trong Excel theo d\u1ea5u ph\u1ea9y, kho\u1ea3ng tr\u1eafng, k\u00fd t\u1ef1 x\u00e1c \u0111\u1ecbnh"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<blockquote id=\"h\u00e0m-c\u1eaft-chu\u1ed7i-trong-excel\" cite=\"https:\/\/blog.hocexcel.online\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh.html?ham-cat-chuoi-trong-excel\">\n<p style=\"text-align: justify\">Trong b\u00e0i vi\u1ebft n\u00e0y, blog.hocexcel.online\u00a0s\u1ebd gi\u1ea3i th\u00edch c\u00e1ch t\u00e1ch c\u00e1c \u00f4 trong Excel b\u1eb1ng c\u00f4ng th\u1ee9c. B\u1ea1n s\u1ebd h\u1ecdc c\u00e1ch t\u00e1ch v\u0103n b\u1ea3n d\u1ef1a theo d\u1ea5u ph\u1ea9y, kho\u1ea3ng tr\u1eafng ho\u1eb7c b\u1ea5t k\u1ef3 d\u1ea5u ph\u00e2n c\u00e1ch n\u00e0o kh\u00e1c, v\u00e0 l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 ph\u00e2n chia chu\u1ed7i th\u00e0nh v\u0103n b\u1ea3n v\u00e0 s\u1ed1.<\/p>\n<\/blockquote>\n<p style=\"text-align: justify\">Xem th\u00eam:<\/p>\n<ul>\n<li style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/cach-tach-rieng-phan-ho-va-phan-ten-trong-excel.html\" target=\"_blank\" rel=\"noopener noreferrer\">H\u01b0\u1edbng d\u1eabn t\u00e1ch h\u1ecd t\u00ean b\u1eb1ng h\u00e0m trong excel\u00a0<\/a><\/li>\n<li style=\"text-align: justify\"><a href=\"http:\/\/excelcobandennangcao.hocexcel.online\/?utm_source=Blog&amp;utm_medium=BLOG_EX101_BACHNX_POST_01_05&amp;utm_campaign=EX101\" target=\"_blank\" rel=\"noopener noreferrer\">H\u1ecdc th\u00e0nh t\u1ea1o Excel trong 7 ng\u00e0y<\/a><\/li>\n<\/ul>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-674e6e3ee32ac\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh.html#Lam_the_nao_de_chia_van_ban_trong_Excel_bang_cach_su_dung_cong_thuc\" title=\"L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 chia v\u0103n b\u1ea3n trong Excel b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c:\">L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 chia v\u0103n b\u1ea3n trong Excel b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh.html#Lam_the_nao_de_phan_chia_chuoi_bang_cach_ngat_dong_trong_Excel\" title=\"L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 ph\u00e2n chia chu\u1ed7i b\u1eb1ng c\u00e1ch ng\u1eaft d\u00f2ng trong Excel:\">L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 ph\u00e2n chia chu\u1ed7i b\u1eb1ng c\u00e1ch ng\u1eaft d\u00f2ng trong Excel:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh.html#Lam_the_nao_de_phan_chia_van_ban_va_so_trong_Excel\" title=\"L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 ph\u00e2n chia v\u0103n b\u1ea3n v\u00e0 s\u1ed1 trong Excel:\">L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 ph\u00e2n chia v\u0103n b\u1ea3n v\u00e0 s\u1ed1 trong Excel:<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh.html#Vi_du_1_Chia_chuoi_cua_loai_%E2%80%98van_ban_so\" title=\"V\u00ed d\u1ee5 1. Chia chu\u1ed7i c\u1ee7a lo\u1ea1i \u2018v\u0103n b\u1ea3n + s\u1ed1\u2019\">V\u00ed d\u1ee5 1. Chia chu\u1ed7i c\u1ee7a lo\u1ea1i \u2018v\u0103n b\u1ea3n + s\u1ed1\u2019<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh.html#Vi_du_2_Chia_chuoi_cua_loai_%E2%80%98so_van_ban\" title=\"V\u00ed d\u1ee5 2. Chia chu\u1ed7i c\u1ee7a lo\u1ea1i \u2018s\u1ed1 + v\u0103n b\u1ea3n\u2019\">V\u00ed d\u1ee5 2. Chia chu\u1ed7i c\u1ee7a lo\u1ea1i \u2018s\u1ed1 + v\u0103n b\u1ea3n\u2019<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/lam-the-nao-de-tach-chuoi-van-ban-trong-excel-theo-dau-phay-khoang-trang-ky-tu-xac-dinh.html#Vi_du_3_Trich_xuat_chi_so_tu_chuoi_so_%E2%80%98so_van_ban\" title=\"V\u00ed d\u1ee5 3. Tr\u00edch xu\u1ea5t ch\u1ec9 s\u1ed1 t\u1eeb chu\u1ed7i s\u1ed1 \u2018s\u1ed1 v\u0103n b\u1ea3n\u2019\">V\u00ed d\u1ee5 3. Tr\u00edch xu\u1ea5t ch\u1ec9 s\u1ed1 t\u1eeb chu\u1ed7i s\u1ed1 \u2018s\u1ed1 v\u0103n b\u1ea3n\u2019<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Lam_the_nao_de_chia_van_ban_trong_Excel_bang_cach_su_dung_cong_thuc\"><\/span>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 chia v\u0103n b\u1ea3n trong Excel b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">\u0110\u1ec3 t\u00e1ch chu\u1ed7i trong Excel hay t\u00e1ch ch\u1eef v\u00e0 s\u1ed1 trong excel, b\u1ea1n th\u01b0\u1eddng s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/ham-left-va-cac-ung-dung-cuc-hay-cua-ham-left-trong-phan-tach-ho-ten.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m LEFT<\/a>, <a href=\"http:\/\/blog.ezworkapp.com\/ham-right-va-cac-ung-dung-tuyet-voi-cua-ham-right-trong-phan-xuat-ky-tu.html\" target=\"_blank\" rel=\"noopener noreferrer\">RIGHT<\/a> ho\u1eb7c <a href=\"http:\/\/blog.ezworkapp.com\/ham-mid-va-cach-su-dung-ham-mid-trong-excel.html\" target=\"_blank\" rel=\"noopener noreferrer\">MID<\/a> k\u1ebft h\u1ee3p v\u1edbi FIND ho\u1eb7c <a href=\"http:\/\/blog.ezworkapp.com\/ham-search-trong-excel-cach-dung-va-vi-du.html\" target=\"_blank\" rel=\"noopener noreferrer\">SEARCH<\/a>. L\u00fac \u0111\u1ea7u, m\u1ed9t s\u1ed1 c\u00f4ng th\u1ee9c c\u00f3 th\u1ec3 ph\u1ee9c t\u1ea1p, nh\u01b0ng th\u1ef1c t\u1ebf logic l\u00e0 kh\u00e1 \u0111\u01a1n gi\u1ea3n, v\u00e0 c\u00e1c v\u00ed d\u1ee5 sau \u0111\u00e2y s\u1ebd cung c\u1ea5p cho b\u1ea1n m\u1ed9t s\u1ed1 \u0111\u1ea7u m\u1ed1i.<\/p>\n<p style=\"text-align: justify\">T\u00e1ch chu\u1ed7i b\u1eb1ng d\u1ea5u ph\u1ea9y, d\u1ea5u hai ch\u1ea5m, d\u1ea5u g\u1ea1ch ch\u00e9o, d\u1ea5u g\u1ea1ch ngang ho\u1eb7c d\u1ea5u ph\u00e2n c\u00e1ch kh\u00e1c<\/p>\n<p style=\"text-align: justify\">Khi ph\u00e2n chia c\u00e1c \u00f4 trong Excel, vi\u1ec7c ch\u00ednh l\u00e0 x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u ph\u00e2n c\u00e1ch trong chu\u1ed7i v\u0103n b\u1ea3n. T\u00f9y thu\u1ed9c v\u00e0o c\u00f4ng vi\u1ec7c c\u1ee7a b\u1ea1n, \u0111i\u1ec1u n\u00e0y c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m Search kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng ho\u1eb7c h\u00e0m Find c\u00f3 ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng. M\u1ed9t khi b\u1ea1n c\u00f3 v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u ph\u00e2n c\u00e1ch, s\u1eed d\u1ee5ng h\u00e0m RIGHT, LEFT ho\u1eb7c MID \u0111\u1ec3 tr\u00edch xu\u1ea5t ph\u1ea7n t\u01b0\u01a1ng \u1ee9ng c\u1ee7a chu\u1ed7i v\u0103n b\u1ea3n.<\/p>\n<blockquote>\n<p style=\"text-align: justify\">Xem th\u00eam:\u00a0<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\">L\u1edbp h\u1ecdc word excel \u1edf H\u00e0 N\u1ed9i<\/a> uy t\u00edn \u2013 ch\u1ea5t l\u01b0\u1ee3ng cao<\/p>\n<\/blockquote>\n<p style=\"text-align: justify\"><strong>\u0110\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n, h\u00e3y xem x\u00e9t v\u00ed d\u1ee5 sau \u0111\u00e2y:<\/strong><\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch c\u00e1c SKU c\u1ee7a m\u1eabu Lo\u1ea1i-M\u00e0u-K\u00edch th\u01b0\u1edbc, v\u00e0 b\u1ea1n mu\u1ed1n chia t\u00e1ch c\u1ed9t th\u00e0nh 3 c\u1ed9t ri\u00eang bi\u1ec7t:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/split-string-excel.png\" alt=\"Splitting the source text strings into 3 columns\" title=\"split-string-excel\"><\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-115.gif\" title=\"vba101_92019-nho-115\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-267.gif\" title=\"ex101_92019-nho-267\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ol style=\"text-align: justify\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t t\u00ean m\u1ee5c (t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 tr\u01b0\u1edbc d\u1ea5u n\u1ed1i \u0111\u1ea7u ti\u00ean), ch\u00e8n c\u00f4ng th\u1ee9c sau trong B2, v\u00e0 sau \u0111\u00f3 sao ch\u00e9p n\u00f3 xu\u1ed1ng c\u1ed9t:<\/li>\n<\/ol>\n<p style=\"text-align: justify\">= LEFT (A2, search (\u201c-\u201c, A2,1) -1)<\/p>\n<p style=\"text-align: justify\">Trong c\u00f4ng th\u1ee9c n\u00e0y, h\u00e0m Search x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u n\u1ed1i \u0111\u1ea7u ti\u00ean (\u201c-\u201c) trong chu\u1ed7i v\u00e0 ch\u1ee9c n\u0103ng LEFT s\u1ebd chi\u1ebft t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 c\u00f2n l\u1ea1i (b\u1ea1n tr\u1eeb 1 t\u1eeb v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u n\u1ed1i b\u1edfi v\u00ec b\u1ea1n kh\u00f4ng mu\u1ed1n c\u00f3 d\u1ea5u n\u1ed1i).<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/extract-text-left.png\" alt=\"Use the LEFT function to extract the characters before the first hyphen.\" title=\"extract-text-left\"><\/p>\n<ol style=\"text-align: justify\" start=\"2\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t m\u00e0u s\u1eafc (t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 gi\u1eefa c\u00e1c d\u1ea5u g\u1ea1ch n\u1ed1i th\u1ee9 2 v\u00e0 th\u1ee9 3), h\u00e3y nh\u1eadp c\u00f4ng th\u1ee9c sau trong C2, v\u00e0 sau \u0111\u00f3 sao ch\u00e9p n\u00f3 xu\u1ed1ng c\u00e1c \u00f4 kh\u00e1c:<\/li>\n<\/ol>\n<p>=MID(A2, SEARCH(\u201c-\u201c, A2)+1, SEARCH(\u201c-\u201c, A2, SEARCH(\u201c-\u201c,A2)+1)-SEARCH(\u201c-\u201c,A2)-1<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/extract-text-mid.png\" alt=\"Use the MID function to extract the characters between the 2&lt;sup&gt;nd&lt;\/sup&gt; and 3&lt;sup&gt;rd&lt;\/sup&gt; hyphens.\" title=\"extract-text-mid\"><\/p>\n<p style=\"text-align: justify\">Nh\u01b0 b\u1ea1n c\u00f3 th\u1ec3 bi\u1ebft, h\u00e0m MID c\u00f3 c\u00fa ph\u00e1p sau:<\/p>\n<p style=\"text-align: justify\"><strong>MID (v\u0103n b\u1ea3n, start_num, num_chars)<\/strong><\/p>\n<p style=\"text-align: justify\">N\u01a1i:<\/p>\n<ul style=\"text-align: justify\">\n<li>V\u0103n b\u1ea3n \u2013 n\u01a1i \u0111\u1ec3 tr\u00edch xu\u1ea5t v\u0103n b\u1ea3n t\u1eeb.<\/li>\n<li>Start_num \u2013 v\u1ecb tr\u00ed c\u1ee7a k\u00ed t\u1ef1 \u0111\u1ea7u ti\u00ean \u0111\u1ec3 tr\u00edch xu\u1ea5t.<\/li>\n<li>Num_chars \u2013 s\u1ed1 k\u00fd t\u1ef1 \u0111\u1ec3 tr\u00edch xu\u1ea5t.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Trong c\u00f4ng th\u1ee9c tr\u00ean, v\u0103n b\u1ea3n \u0111\u01b0\u1ee3c tr\u00edch ra t\u1eeb \u00f4 A2, v\u00e0 2 \u0111\u1ed1i s\u1ed1 kh\u00e1c \u0111\u01b0\u1ee3c t\u00ednh b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng 4 h\u00e0m SEARCH kh\u00e1c:<\/p>\n<ul style=\"text-align: justify\">\n<li>S\u1ed1 b\u1eaft \u0111\u1ea7u (start_num) l\u00e0 v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u n\u1ed1i \u0111\u1ea7u ti\u00ean +1:<\/li>\n<\/ul>\n<p style=\"text-align: justify\">SEARCH (\u201c-\u201c, A2) + 1<\/p>\n<ul style=\"text-align: justify\">\n<li>S\u1ed1 k\u00fd t\u1ef1 \u0111\u1ec3 tr\u00edch xu\u1ea5t (num_chars): s\u1ef1 kh\u00e1c bi\u1ec7t gi\u1eefa v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u n\u1ed1i th\u1ee9 hai v\u00e0 d\u1ea5u n\u1ed1i \u0111\u1ea7u ti\u00ean, tr\u1eeb \u0111i 1:<\/li>\n<\/ul>\n<p style=\"text-align: justify\">SEARCH (\u201c-\u201c, A2, SEARCH (\u201c-\u201c, A2) +1) \u2013 SEARCH (\u201c-\u201c, A2) -1<\/p>\n<ol style=\"text-align: justify\" start=\"3\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t k\u00edch th\u01b0\u1edbc (t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 sau d\u1ea5u n\u1ed1i th\u1ee9 3), h\u00e3y nh\u1eadp c\u00f4ng th\u1ee9c sau trong D2:<\/li>\n<\/ol>\n<p style=\"text-align: justify\">= RIGHT (A2, LEN (A2) \u2013 SEARCH (\u201c-\u201c, A2, SEARCH (\u201c-\u201c, A2) + 1))<\/p>\n<p style=\"text-align: justify\">Trong c\u00f4ng th\u1ee9c n\u00e0y, h\u00e0m LEN tr\u1ea3 v\u1ec1 t\u1ed5ng chi\u1ec1u d\u00e0i c\u1ee7a chu\u1ed7i, t\u1eeb \u0111\u00f3 b\u1ea1n tr\u1eeb \u0111i v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u n\u1ed1i th\u1ee9 hai. S\u1ef1 kh\u00e1c bi\u1ec7t l\u00e0 s\u1ed1 k\u00fd t\u1ef1 sau d\u1ea5u n\u1ed1i th\u1ee9 hai v\u00e0 h\u00e0m RIGHT chi\u1ebft xu\u1ea5t ch\u00fang.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/extract-text-right.png\" alt=\"(Use the RIGHT function to extract the characters after the 3&lt;sup&gt;rd&lt;\/sup&gt; hyphen.\" title=\"extract-text-right\"><br \/>\nTrong m\u1ed9t c\u00e1ch t\u01b0\u01a1ng t\u1ef1, b\u1ea1n c\u00f3 th\u1ec3 ph\u00e2n chia c\u1ed9t b\u1edfi b\u1ea5t k\u1ef3 k\u00ed t\u1ef1 n\u00e0o kh\u00e1c. T\u1ea5t c\u1ea3 b\u1ea1n ph\u1ea3i l\u00e0m l\u00e0 thay th\u1ebf \u201c-\u201d b\u1eb1ng k\u00fd t\u1ef1 ph\u00e2n c\u00e1ch b\u1eaft bu\u1ed9c, v\u00ed d\u1ee5 nh\u01b0 d\u1ea5u c\u00e1ch (\u201c\u201d), d\u1ea5u g\u1ea1ch ch\u00e9o (\u201c\/\u201d), d\u1ea5u hai ch\u1ea5m (\u201c;\u201d), d\u1ea5u ch\u1ea5m ph\u1ea9y (\u201c;\u201d) v\u00e0 v\u00e2n v\u00e2n.<\/p>\n<p style=\"text-align: justify\"><strong>M\u1eb9o<\/strong>. Trong c\u00e1c c\u00f4ng th\u1ee9c tr\u00ean, +1 v\u00e0 -1 t\u01b0\u01a1ng \u1ee9ng v\u1edbi s\u1ed1 k\u00fd t\u1ef1 trong d\u1ea5u ph\u00e2n c\u00e1ch. Trong v\u00ed d\u1ee5 n\u00e0y, n\u00f3 l\u00e0 m\u1ed9t d\u1ea5u n\u1ed1i (1 k\u00fd t\u1ef1). N\u1ebfu d\u1ea5u ph\u00e2n c\u00e1ch c\u1ee7a b\u1ea1n bao g\u1ed3m 2 k\u00fd t\u1ef1, v\u00ed d\u1ee5: D\u1ea5u ph\u1ea9y v\u00e0 kho\u1ea3ng tr\u1eafng, sau \u0111\u00f3 ch\u1ec9 cung c\u1ea5p d\u1ea5u ph\u1ea9y (\u201c,\u201d) cho h\u00e0m SEARCH, v\u00e0 s\u1eed d\u1ee5ng +2 v\u00e0 -2 thay v\u00ec +1 v\u00e0 -1.<\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Lam_the_nao_de_phan_chia_chuoi_bang_cach_ngat_dong_trong_Excel\"><\/span>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 ph\u00e2n chia chu\u1ed7i b\u1eb1ng c\u00e1ch ng\u1eaft d\u00f2ng trong Excel:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">\u0110\u1ec3 chia v\u0103n b\u1ea3n b\u1eb1ng kho\u1ea3ng tr\u1eafng, h\u00e3y s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng th\u1ee9c t\u01b0\u01a1ng t\u1ef1 nh\u01b0 c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c minh h\u1ecda trong v\u00ed d\u1ee5 tr\u01b0\u1edbc. S\u1ef1 kh\u00e1c bi\u1ec7t duy nh\u1ea5t l\u00e0 b\u1ea1n c\u1ea7n ch\u1ee9c n\u0103ng CHAR \u0111\u1ec3 cung c\u1ea5p cho k\u00fd t\u1ef1 ng\u1eaft d\u00f2ng v\u00ec b\u1ea1n kh\u00f4ng th\u1ec3 g\u00f5 tr\u1ef1c ti\u1ebfp v\u00e0o c\u00f4ng th\u1ee9c. Gi\u1ea3 s\u1eed, c\u00e1c \u00f4 m\u00e0 b\u1ea1n mu\u1ed1n chia nh\u1ecf tr\u00f4ng t\u01b0\u01a1ng t\u1ef1 nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-split-cells.png\" alt=\"The cells to be split by spaces.\" title=\"excel-split-cells\"><br \/>\nL\u1ea5y c\u00f4ng th\u1ee9c t\u1eeb v\u00ed d\u1ee5 tr\u01b0\u1edbc v\u00e0 thay d\u1ea5u g\u1ea1ch n\u1ed1i (\u201c-\u201c) b\u1eb1ng CHAR (10) trong \u0111\u00f3 10 l\u00e0 m\u00e3 ASCII cho d\u00f2ng c\u1ea5p d\u1eef li\u1ec7u.<\/p>\n<ul style=\"text-align: justify\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t t\u00ean m\u1eb7t h\u00e0ng:<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)<\/strong><\/p>\n<ul style=\"text-align: justify\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t m\u00e0u s\u1eafc:<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>=MID(A2, SEARCH(CHAR(10),A2) + 1, SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) \u2013 SEARCH(CHAR(10),A2) \u2013 1)<\/strong><\/p>\n<ul style=\"text-align: justify\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t k\u00edch th\u01b0\u1edbc:<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>=RIGHT(A2,LEN(A2) \u2013 SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))<\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00e2y l\u00e0 k\u1ebft qu\u1ea3:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/split-text-line-break.png\" alt=\"Splitting strings by the space character.\" title=\"split-text-line-break\"><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Lam_the_nao_de_phan_chia_van_ban_va_so_trong_Excel\"><\/span>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 ph\u00e2n chia v\u0103n b\u1ea3n v\u00e0 s\u1ed1 trong Excel:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">\u0110\u1ec3 b\u1eaft \u0111\u1ea7u, kh\u00f4ng c\u00f3 gi\u1ea3i ph\u00e1p t\u1ed5ng qu\u00e1t cho t\u1ea5t c\u1ea3 c\u00e1c chu\u1ed7i ch\u1eef s\u1ed1. C\u00f4ng th\u1ee9c n\u00e0o \u0111\u1ec3 s\u1eed d\u1ee5ng ph\u1ee5 thu\u1ed9c v\u00e0o m\u1eabu chu\u1ed7i c\u1ee5 th\u1ec3. D\u01b0\u1edbi \u0111\u00e2y b\u1ea1n s\u1ebd t\u00ecm th\u1ea5y c\u00f4ng th\u1ee9c cho 3 k\u1ecbch b\u1ea3n th\u01b0\u1eddng g\u1eb7p nh\u1ea5t.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_1_Chia_chuoi_cua_loai_%E2%80%98van_ban_so\"><\/span>V\u00ed d\u1ee5 1. Chia chu\u1ed7i c\u1ee7a lo\u1ea1i \u2018v\u0103n b\u1ea3n + s\u1ed1\u2019<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t c\u1ed9t c\u00e1c chu\u1ed7i v\u1edbi v\u0103n b\u1ea3n v\u00e0 s\u1ed1 k\u1ebft h\u1ee3p, trong \u0111\u00f3 m\u1ed9t s\u1ed1 lu\u00f4n lu\u00f4n theo sau v\u0103n b\u1ea3n. B\u1ea1n mu\u1ed1n ph\u00e1 v\u1ee1 c\u00e1c chu\u1ed7i ban \u0111\u1ea7u \u0111\u1ec3 v\u0103n b\u1ea3n v\u00e0 s\u1ed1 xu\u1ea5t hi\u1ec7n trong c\u00e1c \u00f4 ri\u00eang bi\u1ec7t, nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/split-text-numbers.png\" alt=\"Splitting the source string to text and number.\" title=\"split-text-numbers\"><\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 tr\u00edch xu\u1ea5t c\u00e1c s\u1ed1, s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c m\u1ea3ng sau \u0111\u00e2y, \u0111\u01b0\u1ee3c ho\u00e0n th\u00e0nh b\u1eb1ng c\u00e1ch nh\u1ea5n Ctrl + Shift + Enter:<\/p>\n<p style=\"text-align: justify\">= RIGHT (A2, SUM (LEN (A2) \u2013 LEN (SUBSTITUTE (A2, \u201c0\u201d, \u201c1\u201d, \u201c2\u201d, \u201c3\u201d, \u201c4\u201d, \u201c5\u201d, \u201c6\u201d, \u201c7\u201d , \u201c8\u201d, \u201c9\u201d}, \u201c\u201d))))<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 tr\u00edch xu\u1ea5t v\u0103n b\u1ea3n, s\u1eed d\u1ee5ng:<\/p>\n<p style=\"text-align: justify\">= LEFT (A2, LEN (A2) -LEN (C2))<\/p>\n<p style=\"text-align: justify\">Tr\u01b0\u1eddng h\u1ee3p A2 l\u00e0 chu\u1ed7i ban \u0111\u1ea7u, v\u00e0 C2 l\u00e0 s\u1ed1 tr\u00edch xu\u1ea5t, nh\u01b0 th\u1ec3 hi\u1ec7n trong h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6395\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-dialog-box-launcher-1-178.png\" width=\"600\" height=\"226\" title=\"excel-dialog-box-launcher-1-178\"><br \/>\n<strong>C\u00f4ng th\u1ee9c ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o:<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>C\u00f4ng th\u1ee9c \u0111\u1ec3 tr\u00edch xu\u1ea5t s\u1ed1 (<\/strong><strong>h\u00e0m<\/strong><strong> RIGHT)<\/strong>. V\u1ec1 c\u01a1 b\u1ea3n, c\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm m\u1ecdi s\u1ed1 c\u00f3 th\u1ec3 t\u1eeb 0 \u0111\u1ebfn 9 trong chu\u1ed7i ngu\u1ed3n, t\u00ednh s\u1ed1 l\u01b0\u1ee3ng v\u00e0 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u00fd t\u1ef1 t\u1eeb k\u00fd t\u1ef1 cu\u1ed1i chu\u1ed7i ban \u0111\u1ea7u.<\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c chi ti\u1ebft ph\u00e2n r\u00e3:<\/p>\n<ul style=\"text-align: justify\">\n<li>Tr\u01b0\u1edbc ti\u00ean, b\u1ea1n s\u1eed d\u1ee5ng c\u00e1c h\u00e0m LEN v\u00e0 SUBSTITUTE \u0111\u1ec3 t\u00ecm ra s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n m\u1ed9t s\u1ed1 n\u00e0o \u0111\u00f3 trong chu\u1ed7i g\u1ed1c \u2013 thay th\u1ebf s\u1ed1 b\u1eb1ng m\u1ed9t chu\u1ed7i r\u1ed7ng (\u201c\u201d), v\u00e0 sau \u0111\u00f3 tr\u1eeb \u0111i chi\u1ec1u d\u00e0i c\u1ee7a chu\u1ed7i m\u00e0 kh\u00f4ng c\u00f3 s\u1ed1 \u0111\u00f3 t\u1eeb t\u1ed5ng s\u1ed1 Chi\u1ec1u d\u00e0i c\u1ee7a chu\u1ed7i ban \u0111\u1ea7u. B\u1edfi v\u00ec \u0111\u00f3 l\u00e0 m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng, thao t\u00e1c n\u00e0y \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n tr\u00ean m\u1ed7i s\u1ed1 trong h\u1eb1ng m\u1ea3ng:<\/li>\n<\/ul>\n<p style=\"text-align: justify\">LEN (A2) -LEN (SUBSTITUTE (A2, \u201c0\u201d, \u201c1\u201d, \u201c2\u201d, \u201c3\u201d, \u201c4\u201d, \u201c5\u201d, \u201c6\u201d, \u201c7\u201d, \u201c8\u201d, \u201c9 \u201c},\u201d \u201c)<\/p>\n<ul style=\"text-align: justify\">\n<li>Ti\u1ebfp theo, h\u00e0m SUM th\u00eam t\u1ea5t c\u1ea3 c\u00e1c l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a t\u1ea5t c\u1ea3 c\u00e1c ch\u1eef s\u1ed1 trong chu\u1ed7i ngu\u1ed3n.<\/li>\n<li>Cu\u1ed1i c\u00f9ng, h\u00e0m RIGHT tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u00fd t\u1ef1 t\u1eeb ph\u00eda b\u00ean ph\u1ea3i c\u1ee7a chu\u1ed7i.<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>C\u00f4ng th\u1ee9c \u0111\u1ec3 tr\u00edch xu\u1ea5t v\u0103n b\u1ea3n (<\/strong><strong>h\u00e0m <\/strong><strong>LEFT)<\/strong>. B\u1ea1n t\u00ednh to\u00e1n bao nhi\u00eau k\u00fd t\u1ef1 v\u0103n b\u1ea3n chu\u1ed7i ch\u1ee9a b\u1eb1ng c\u00e1ch tr\u1eeb s\u1ed1 ch\u1eef s\u1ed1 chi\u1ebft xu\u1ea5t (C2) t\u1eeb chi\u1ec1u d\u00e0i c\u1ee7a chu\u1ed7i g\u1ed1c (A2). Sau \u0111\u00f3, b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m LEFT \u0111\u1ec3 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u00fd t\u1ef1 t\u1eeb \u0111\u1ea7u chu\u1ed7i.<\/p>\n<p style=\"text-align: justify\">M\u1ed9t gi\u1ea3i ph\u00e1p kh\u00e1c (c\u00f4ng th\u1ee9c kh\u00f4ng c\u00f3 m\u1ea3ng)<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3i ph\u00e1p thay th\u1ebf s\u1ebd s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed c\u1ee7a s\u1ed1 \u0111\u1ea7u ti\u00ean trong chu\u1ed7i: = MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A2 &amp; \u201c0123456789\u201d))<\/p>\n<p style=\"text-align: justify\">M\u1eb7c d\u00f9 c\u00f4ng th\u1ee9c c\u0169ng ch\u1ee9a m\u1ed9t h\u1eb1ng s\u1ed1 m\u1ea3ng, \u0111\u00f3 l\u00e0 m\u1ed9t c\u00f4ng th\u1ee9c b\u00ecnh th\u01b0\u1eddng \u0111\u01b0\u1ee3c ho\u00e0n th\u00e0nh theo c\u00e1ch th\u00f4ng th\u01b0\u1eddng b\u1eb1ng c\u00e1ch nh\u1ea5n ph\u00edm Enter.<\/p>\n<p style=\"text-align: justify\">Khi v\u1ecb tr\u00ed c\u1ee7a s\u1ed1 \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y, b\u1ea1n c\u00f3 th\u1ec3 t\u00e1ch v\u0103n b\u1ea3n v\u00e0 s\u1ed1 b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng th\u1ee9c LEFT v\u00e0 RIGHT r\u1ea5t \u0111\u01a1n gi\u1ea3n (nh\u1edb r\u1eb1ng m\u1ed9t s\u1ed1 lu\u00f4n xu\u1ea5t hi\u1ec7n sau v\u0103n b\u1ea3n):<\/p>\n<ul style=\"text-align: justify\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t v\u0103n b\u1ea3n:<\/li>\n<\/ul>\n<p style=\"text-align: justify\">= LEFT (A2, B2-1)<\/p>\n<ul style=\"text-align: justify\">\n<li>\u0110\u1ec3 tr\u00edch xu\u1ea5t s\u1ed1:<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>=RIGHT(B2, LEN(A1)-B2+1)<\/strong><\/p>\n<p style=\"text-align: justify\">Tr\u01b0\u1eddng h\u1ee3p A2 l\u00e0 chu\u1ed7i ban \u0111\u1ea7u, v\u00e0 B2 l\u00e0 v\u1ecb tr\u00ed c\u1ee7a s\u1ed1 \u0111\u1ea7u ti\u00ean, nh\u01b0 th\u1ec3 hi\u1ec7n trong h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/split-text-number-formula1.png\" alt=\"Non-array formula to split text and numbers\" title=\"split-text-number-formula1\"><br \/>\n\u0110\u1ec3 lo\u1ea1i b\u1ecf c\u1ed9t helper gi\u1eef v\u1ecb tr\u00ed s\u1ed1 b\u1eaft \u0111\u1ea7u, b\u1ea1n c\u00f3 th\u1ec3 nh\u00fang h\u00e0m MIN v\u00e0o c\u00e1c h\u00e0m LEFT v\u00e0 RIGHT:<\/p>\n<ul style=\"text-align: justify\">\n<li>C\u00f4ng th\u1ee9c tr\u00edch xu\u1ea5t v\u0103n b\u1ea3n:<\/li>\n<\/ul>\n<p style=\"text-align: justify\">= LEFT (A2, MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A2 &amp; \u201c0123456789\u201d)) \u2013 1)<\/p>\n<ul style=\"text-align: justify\">\n<li>C\u00f4ng th\u1ee9c tr\u00edch xu\u1ea5t c\u00e1c s\u1ed1:<\/li>\n<\/ul>\n<p style=\"text-align: justify\">= RIGHT (A2, LEN (A2) -MIN (SEARCH({0,1,2,3,4,5,6,7,8,9}, A2 &amp; \u201c0123456789\u201d)) + 1)<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c t\u00ednh to\u00e1n v\u1ecb tr\u00ed c\u1ee7a s\u1ed1 th\u1ee9 nh\u1ea5t<\/p>\n<p style=\"text-align: justify\">B\u1ea1n cung c\u1ea5p h\u1eb1ng s\u1ed1 m\u1ea3ng {0,1,2,3,4,5,6,7,8,9} trong \u0111\u1ed1i s\u1ed1 find_text c\u1ee7a h\u00e0m SEARCH, l\u00e0m cho n\u00f3 t\u00ecm t\u1eebng s\u1ed1 trong h\u1eb1ng s\u1ed1 m\u1ea3ng b\u00ean trong b\u1ea3n g\u1ed1c, v\u00e0 tr\u1ea3 l\u1ea1i v\u1ecb tr\u00ed c\u1ee7a ch\u00fang. B\u1edfi v\u00ec h\u1eb1ng s\u1ed1 m\u1ea3ng ch\u1ee9a 10 ch\u1eef s\u1ed1, m\u1ea3ng k\u1ebft qu\u1ea3 c\u0169ng ch\u1ee9a 10 m\u1ee5c.<\/p>\n<p style=\"text-align: justify\">H\u00e0m MIN l\u1ea5y m\u1ea3ng k\u1ebft qu\u1ea3 v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t, t\u01b0\u01a1ng \u1ee9ng v\u1edbi v\u1ecb tr\u00ed c\u1ee7a s\u1ed1 \u0111\u1ea7u ti\u00ean trong chu\u1ed7i ban \u0111\u1ea7u.<\/p>\n<p style=\"text-align: justify\">Ngo\u00e0i ra, ch\u00fang t\u00f4i s\u1eed d\u1ee5ng m\u1ed9t c\u1ea5u tr\u00fac \u0111\u1eb7c bi\u1ec7t (A2 &amp; \u201c0123456789\u201d) \u0111\u1ec3 gh\u00e9p m\u1ed7i s\u1ed1 c\u00f3 th\u1ec3 v\u1edbi chu\u1ed7i ban \u0111\u1ea7u. C\u00e1ch n\u00e0y th\u1ef1c hi\u1ec7n vai tr\u00f2 c\u1ee7a IFERROR v\u00e0 cho ph\u00e9p ch\u00fang t\u00f4i tr\u00e1nh l\u1ed7i khi m\u1ed9t s\u1ed1 nh\u1ea5t \u0111\u1ecbnh trong h\u1eb1ng s\u1ed1 m\u1ea3ng kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y trong chu\u1ed7i ngu\u1ed3n. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, c\u00f4ng th\u1ee9c tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed \u201cgi\u1ea3 m\u1ea1o\u201d b\u1eb1ng chu\u1ed7i chi\u1ec1u d\u00e0i t\u1eeb 1 k\u00fd t\u1ef1 tr\u1edf l\u00ean. \u0110i\u1ec1u n\u00e0y cho ph\u00e9p h\u00e0m LEFT tr\u00edch xu\u1ea5t v\u0103n b\u1ea3n v\u00e0 h\u00e0m RIGHT tr\u1ea3 v\u1ec1 m\u1ed9t chu\u1ed7i r\u1ed7ng n\u1ebfu chu\u1ed7i g\u1ed1c kh\u00f4ng ch\u1ee9a b\u1ea5t k\u1ef3 s\u1ed1 n\u00e0o, nh\u01b0 trong d\u00f2ng 7 h\u00ecnh \u1edf tr\u00ean.<\/p>\n<blockquote>\n<p style=\"text-align: justify\">Xem th\u00eam: <a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-lap-trinh-vba-trong-excel\">H\u1ecdc VBA \u1edf \u0111\u00e2u<\/a> t\u1ea1i H\u00e0 N\u1ed9i<\/p>\n<\/blockquote>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5: \u0111\u1ed1i v\u1edbi chu\u1ed7i \u201cDress 05\u201d trong A2, m\u1ea3ng k\u1ebft qu\u1ea3 l\u00e0 {7,10,11,12,13,8,15,16,17,18}. V\u00e0 \u0111\u00e2y l\u00e0 c\u00e1ch ch\u00fang t\u00f4i c\u00f3:<\/p>\n<ul style=\"text-align: justify\">\n<li>5 l\u00e0 k\u00fd t\u1ef1 th\u1ee9 8 trong chu\u1ed7i g\u1ed1c, v\u00e0 0 l\u00e0 k\u00fd t\u1ef1 th\u1ee9 7, \u0111\u00f3 l\u00e0 l\u00fd do t\u1ea1i sao m\u1ee5c \u0111\u1ea7u ti\u00ean c\u1ee7a m\u1ea3ng k\u1ebft qu\u1ea3 l\u00e0 \u201c7\u201d, v\u00e0 th\u1ee9 s\u00e1u l\u00e0 \u201c8\u201d.<\/li>\n<li>Kh\u00f4ng c\u00f3 m\u1ee5c n\u00e0o kh\u00e1c c\u1ee7a h\u1eb1ng s\u1ed1 m\u1ea3ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y trong A2, v\u00e0 do \u0111\u00f3 8 ph\u1ea7n kh\u00e1c c\u1ee7a m\u1ea3ng k\u1ebft qu\u1ea3 \u0111\u1ea1i di\u1ec7n cho c\u00e1c v\u1ecb tr\u00ed c\u1ee7a c\u00e1c ch\u1eef s\u1ed1 t\u01b0\u01a1ng \u1ee9ng trong chu\u1ed7i n\u1ed1i (A2 &amp; \u201c0123456789\u201d).<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/arrays.png\" alt=\"Finding the position of the first number in the string\" title=\"arrays\">V\u00e0 b\u1edfi v\u00ec 7 l\u00e0 gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t trong m\u1ea3ng k\u1ebft qu\u1ea3, do h\u00e0m MIN tr\u1ea3 v\u1ec1, v\u00e0 ch\u00fang ta nh\u1eadn \u0111\u01b0\u1ee3c v\u1ecb tr\u00ed c\u1ee7a s\u1ed1 \u0111\u1ea7u ti\u00ean (0) trong chu\u1ed7i v\u0103n b\u1ea3n ban \u0111\u1ea7u.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_2_Chia_chuoi_cua_loai_%E2%80%98so_van_ban\"><\/span>V\u00ed d\u1ee5 2. Chia chu\u1ed7i c\u1ee7a lo\u1ea1i \u2018s\u1ed1 + v\u0103n b\u1ea3n\u2019<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n \u0111ang t\u00e1ch c\u00e1c \u00f4 n\u01a1i v\u0103n b\u1ea3n xu\u1ea5t hi\u1ec7n sau m\u1ed9t s\u1ed1, b\u1ea1n c\u00f3 th\u1ec3 tr\u00edch xu\u1ea5t c\u00e1c s\u1ed1 v\u1edbi c\u00f4ng th\u1ee9c m\u1ea3ng n\u00e0y (ho\u00e0n th\u00e0nh b\u1eb1ng c\u00e1ch nh\u1ea5n Ctrl + Shift + Enter):<\/p>\n<p style=\"text-align: justify\">= LEFT (A2, SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {\u201c0\u201d, \u201c1\u201d, \u201c2\u201d, \u201c3\u201d, \u201c4\u201d, \u201c5\u201d, \u201c6\u201d, \u201c7\u201d , \u201c8\u201d, \u201c9\u201d}, \u201c\u201d))))<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c t\u01b0\u01a1ng t\u1ef1 nh\u01b0 c\u00f4ng th\u1ee9c m\u1ea3ng t\u1eeb v\u00ed d\u1ee5 tr\u01b0\u1edbc, ngo\u1ea1i tr\u1eeb b\u1ea1n s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/ham-left-va-cac-ung-dung-cuc-hay-cua-ham-left-trong-phan-tach-ho-ten.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m LEFT<\/a> thay v\u00ec RIGHT, b\u1edfi v\u00ec trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y s\u1ed1 lu\u00f4n xu\u1ea5t hi\u1ec7n \u1edf ph\u00eda b\u00ean tr\u00e1i c\u1ee7a chu\u1ed7i. M\u1ed9t khi b\u1ea1n \u0111\u00e3 c\u00f3 c\u00e1c con s\u1ed1, tr\u00edch xu\u1ea5t v\u0103n b\u1ea3n b\u1eb1ng c\u00e1ch tr\u1eeb s\u1ed1 ch\u1eef s\u1ed1 t\u1eeb t\u1ed5ng chi\u1ec1u d\u00e0i c\u1ee7a chu\u1ed7i g\u1ed1c:<\/p>\n<p style=\"text-align: justify\">= RIGHT(A2, LEN (A2) -LEN (B2))<\/p>\n<p style=\"text-align: justify\">Trong c\u00e1c c\u00f4ng th\u1ee9c tr\u00ean, A2 l\u00e0 chu\u1ed7i ban \u0111\u1ea7u v\u00e0 B2 l\u00e0 s\u1ed1 tr\u00edch xu\u1ea5t, nh\u01b0 th\u1ec3 hi\u1ec7n trong h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y:<br \/>\n<img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/split-string-number-text.png\" alt=\"Splitting a column of strings where numbers appear before text\" title=\"split-string-number-text\"><\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_3_Trich_xuat_chi_so_tu_chuoi_so_%E2%80%98so_van_ban\"><\/span>V\u00ed d\u1ee5 3. Tr\u00edch xu\u1ea5t ch\u1ec9 s\u1ed1 t\u1eeb chu\u1ed7i s\u1ed1 \u2018s\u1ed1 v\u0103n b\u1ea3n\u2019<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">N\u1ebfu c\u00f4ng vi\u1ec7c c\u1ee7a b\u1ea1n \u0111\u00f2i h\u1ecfi ph\u1ea3i tr\u00edch xu\u1ea5t t\u1ea5t c\u1ea3 c\u00e1c s\u1ed1 t\u1eeb m\u1ed9t chu\u1ed7i trong \u0111\u1ecbnh d\u1ea1ng \u2018number-text-number\u2019, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau \u0111\u00e2y \u0111\u01b0\u1ee3c g\u1ee3i \u00fd b\u1edfi m\u1ed9t trong nh\u1eefng chuy\u00ean gia c\u1ee7a MrExcel:<\/p>\n<p style=\"text-align: justify\">= SUMPRODUCT (MID (0 &amp; A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (\u201c1:\u201d &amp; LEN (A2))), 1)) * ROW (TR\u1ef0C TI\u1ebeP (\u201c1:\u201d &amp; LEN (A2) (1: \u201c&amp; LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (\u201d 1: \u201c&amp; LEN (A2))) \/ 10)<\/p>\n<p style=\"text-align: justify\">Tr\u01b0\u1eddng h\u1ee3p A2 l\u00e0 chu\u1ed7i v\u0103n b\u1ea3n ban \u0111\u1ea7u.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6396 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-dialog-box-launcher-1-179.png\" width=\"600\" height=\"176\" title=\"excel-dialog-box-launcher-1-179\"><\/p>\n<p>\u00a0<\/p>\n<p>Nh\u1eefng ki\u1ebfn th\u1ee9c b\u1ea1n \u0111ang xem thu\u1ed9c kh\u00f3a h\u1ecdc\u00a0<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\">Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi n\u00e2ng cao c\u1ee7a H\u1ecdc Excel Online<\/a>. Kh\u00f3a h\u1ecdc n\u00e0y cung c\u1ea5p cho b\u1ea1n ki\u1ebfn th\u1ee9c m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7 v\u00e0 c\u00f3 h\u1ec7 th\u1ed1ng v\u1ec1 c\u00e1c h\u00e0m, c\u00e1c c\u00f4ng c\u1ee5 trong excel, \u1ee9ng d\u1ee5ng excel trong c\u00f4ng vi\u1ec7c\u2026 Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 nhi\u1ec1u \u01b0u \u0111\u00e3i khi b\u1ea1n \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc n\u00e0y. H\u00e3y tham gia ngay t\u1ea1i \u0111\u1ecba ch\u1ec9: <a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\">H\u1ecdc Excel Online<\/a><\/p>\n<p><strong>Xem th\u00eam: <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-loc-bo-ky-tu-o-dau-o-giua-o-cuoi-trong-doan-van-ban.html\">C\u00e1ch l\u1ea5y k\u00fd t\u1ef1 trong excel<\/a> \u0111\u01a1n gi\u1ea3n, d\u1ec5 th\u1ef1c hi\u1ec7n<\/strong><\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-115.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":3462,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3461","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3461","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=3461"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3461\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/3462"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=3461"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=3461"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=3461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}