{"id":5387,"date":"2024-12-16T02:15:58","date_gmt":"2024-12-16T02:15:58","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/16\/cach-su-dung-regular-expression-trong-vba\/"},"modified":"2024-12-16T02:15:58","modified_gmt":"2024-12-16T02:15:58","slug":"cach-su-dung-regular-expression-trong-vba","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/16\/cach-su-dung-regular-expression-trong-vba\/","title":{"rendered":"C\u00e1ch s\u1eed d\u1ee5ng Regular Expression trong VBA"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\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-675f8d5e14cc9\" 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\/cach-su-dung-regular-expression-trong-vba.html#1_Khai_niem_ve_Regular_Expression\" title=\"1. Kh\u00e1i ni\u1ec7m v\u1ec1 Regular\u00a0Expression\">1. Kh\u00e1i ni\u1ec7m v\u1ec1 Regular\u00a0Expression<\/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\/cach-su-dung-regular-expression-trong-vba.html#2_Cach_su_dung_Regular_Expression\" title=\"2. C\u00e1ch s\u1eed d\u1ee5ng Regular\u00a0Expression\">2. C\u00e1ch s\u1eed d\u1ee5ng Regular\u00a0Expression<\/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-3\" href=\"https:\/\/blog.hocexcel.online\/cach-su-dung-regular-expression-trong-vba.html#Buoc_1_Mo_cua_so_Visual_Basic_Application_VBA\" title=\"B\u01b0\u1edbc 1: M\u1edf c\u1eeda s\u1ed5 Visual Basic Application (VBA)\">B\u01b0\u1edbc 1: M\u1edf c\u1eeda s\u1ed5 Visual Basic Application (VBA)<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/cach-su-dung-regular-expression-trong-vba.html#Buoc_2_Tao_moi_1_Module\" title=\"B\u01b0\u1edbc 2: T\u1ea1o m\u1edbi 1 Module\">B\u01b0\u1edbc 2: T\u1ea1o m\u1edbi 1 Module<\/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\/cach-su-dung-regular-expression-trong-vba.html#Buoc_3_Mo_cua_so_Locals_trong_VBA_de_giup_viec_hieu_ve_cach_su_dung_Regular_Expression_de_hon\" title=\"B\u01b0\u1edbc 3: M\u1edf c\u1eeda s\u1ed5 Locals trong VBA\u00a0\u0111\u1ec3 gi\u00fap vi\u1ec7c\u00a0 hi\u1ec3u v\u1ec1 c\u00e1ch s\u1eed d\u1ee5ng Regular\u00a0Expression d\u1ec5 h\u01a1n\">B\u01b0\u1edbc 3: M\u1edf c\u1eeda s\u1ed5 Locals trong VBA\u00a0\u0111\u1ec3 gi\u00fap vi\u1ec7c\u00a0 hi\u1ec3u v\u1ec1 c\u00e1ch s\u1eed d\u1ee5ng Regular\u00a0Expression d\u1ec5 h\u01a1n<\/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\/cach-su-dung-regular-expression-trong-vba.html#Buoc_4_Tao_1_sub_Test_va_thuc_hien_nhu_sau\" title=\"B\u01b0\u1edbc 4: T\u1ea1o 1 sub Test v\u00e0 th\u1ef1c hi\u1ec7n nh\u01b0 sau\">B\u01b0\u1edbc 4: T\u1ea1o 1 sub Test v\u00e0 th\u1ef1c hi\u1ec7n nh\u01b0 sau<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"1_Khai_niem_ve_Regular_Expression\"><\/span>1. Kh\u00e1i ni\u1ec7m v\u1ec1 Regular\u00a0Expression<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u0110\u00e2y l\u00e0 m\u1ed9t thu\u1eadt ng\u1eef th\u01b0\u1eddng d\u00f9ng trong l\u1eadp tr\u00ecnh, gi\u00fap t\u00ecm ra nh\u1eefng d\u1eef li\u1ec7u\u00a0\u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo 1 quy lu\u1eadt nh\u1ea5t\u00a0\u0111\u1ecbnh trong 1 chu\u1ed7i v\u0103n b\u1ea3n.<\/p>\n<p>V\u00ed d\u1ee5:<\/p>\n<p>Nh\u1eefng con s\u1ed1 n\u1eb1m trong 1\u00a0\u0111o\u1ea1n text: S\u1ed1\u00a0\u0111i\u1ec7n tho\u1ea1i, s\u1ed1 nh\u00e0<\/p>\n<p>Th\u00f4ng tin Email n\u1eb1m trong 1\u00a0\u0111o\u1ea1n text<\/p>\n<p>Khi s\u1eed d\u1ee5ng Regular\u00a0Expression s\u1ebd gi\u00fap ch\u00fang ta d\u1ec5 d\u00e0ng t\u00e1ch\u00a0\u0111\u01b0\u1ee3c nh\u1eefng d\u1eef li\u1ec7u c\u00f3 t\u00ednh quy lu\u1eadt ra kh\u1ecfi 1\u00a0\u0111o\u1ea1n text =&gt;\u00a0\u0110\u01b0\u1ee3c\u00a0\u1ee9ng d\u1ee5ng nhi\u1ec1u trong vi\u1ec7c x\u1eed l\u00fd d\u1eef li\u1ec7u, tr\u00edch l\u1ecdc d\u1eef li\u1ec7u,\u00a0\u0111\u1eb7c bi\u1ec7t trong nh\u1eefng b\u1ea3ng d\u1eef li\u1ec7u l\u1edbn, ph\u1ee9c t\u1ea1p m\u00e0 h\u1ea7u h\u1ebft\u00a0\u0111\u1ec1u ph\u1ea3i l\u00e0m b\u1eb1ng tay n\u1ebfu kh\u00f4ng s\u1eed d\u1ee5ng VBA trong\u00a0excel.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"2_Cach_su_dung_Regular_Expression\"><\/span>2. C\u00e1ch s\u1eed d\u1ee5ng Regular\u00a0Expression<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Ch\u00fang ta c\u00f9ng t\u00ecm hi\u1ec3u v\u1ec1 c\u00e1ch s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng n\u00e0y th\u00f4ng qua v\u00ed d\u1ee5 sau:<\/p>\n<p>Cho\u00a0\u0111o\u1ea1n text t\u1ea1i\u00a0\u00f4 A6 nh\u01b0 sau:<\/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-188.gif\" title=\"vba101_92019-nho-188\"><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-465.gif\" title=\"ex101_92019-nho-465\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>\u00a0<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14462\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-regexp-03.png\" width=\"850\" height=\"236\" title=\"heo-regexp-03\"><\/p>\n<p>Tr\u00edch l\u1ecdc nh\u1eefng n\u1ed9i dung th\u1ecfa m\u00e3n\u00a0\u0111i\u1ec1u ki\u1ec7n:<\/p>\n<p>1 k\u00fd t\u1ef1 b\u1ea5t k\u1ef3A, B, C, D\u2026 k\u1ebft h\u1ee3p v\u1edbi\u00a0\u0111o\u1ea1n text \u201c-hoc\u00a0excel online\u201d<\/p>\n<p>V\u00ed d\u1ee5:<\/p>\n<p>A-hoc\u00a0excel online<\/p>\n<p>B-hoc\u00a0excel online<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_1_Mo_cua_so_Visual_Basic_Application_VBA\"><\/span>B\u01b0\u1edbc 1: M\u1edf c\u1eeda s\u1ed5 Visual Basic Application (VBA)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>C\u00f3 2 c\u00e1ch\u00a0\u0111\u1ec3 m\u1edf c\u1eeda s\u1ed5 VBA:<\/p>\n<ul>\n<li>C\u00e1ch 1: B\u1ea5m t\u1ed5 h\u1ee3p ph\u00edm t\u1eaft Alt + F11<\/li>\n<li>C\u00e1ch 2: Trong tab Developer, b\u1ea5m ch\u1ecdn Visual Basic<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_2_Tao_moi_1_Module\"><\/span>B\u01b0\u1edbc 2: T\u1ea1o m\u1edbi 1 Module<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>B\u1ea5m chu\u1ed9t ph\u1ea3i v\u00e0o m\u1ee5c Project \u2013 VBAProject r\u1ed3i ch\u1ecdn\u00a0Insert \/ Module<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14446\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-vba-eval-01.png\" width=\"480\" height=\"598\" title=\"heo-vba-eval-01\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_3_Mo_cua_so_Locals_trong_VBA_de_giup_viec_hieu_ve_cach_su_dung_Regular_Expression_de_hon\"><\/span>B\u01b0\u1edbc 3: M\u1edf c\u1eeda s\u1ed5 Locals trong VBA\u00a0\u0111\u1ec3 gi\u00fap vi\u1ec7c\u00a0 hi\u1ec3u v\u1ec1 c\u00e1ch s\u1eed d\u1ee5ng Regular\u00a0Expression d\u1ec5 h\u01a1n<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong tab <strong>View<\/strong>\u00a0 &gt; Ch\u1ecdn m\u1ee5c <strong>Locals\u00a0Window<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14461\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-regexp-02.png\" width=\"381\" height=\"522\" title=\"heo-regexp-02\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_4_Tao_1_sub_Test_va_thuc_hien_nhu_sau\"><\/span>B\u01b0\u1edbc 4: T\u1ea1o 1 sub Test v\u00e0 th\u1ef1c hi\u1ec7n nh\u01b0 sau<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/p>\n<div class=\"oembed-gist\">View the code on <a href=\"https:\/\/gist.github.com\/DuongAQ\/387aa9bb3e677db56830a3f4843d26cc\">Gist<\/a>.<\/div>\n<p><strong>* L\u01b0u\u00a0\u00fd:<\/strong><\/p>\n<p>N\u1ebfu chu\u1ed7i strPattern c\u00f3 s\u1eed d\u1ee5ng ti\u1ebfng vi\u1ec7t c\u00f3 d\u1ea5u th\u00ec ch\u00fang ta c\u00f3 th\u1ec3\u00a0\u0111\u1eb7t chu\u1ed7i\u00a0\u0111\u00f3 v\u00e0o 1 v\u1ecb tr\u00ed x\u00e1c\u00a0\u0111\u1ecbnh trong file\u00a0excel r\u1ed3i tham chi\u1ebfu t\u1edbi v\u1ecb tr\u00ed\u00a0\u0111\u00f3 (v\u00ec trong VBA kh\u00f4ng vi\u1ebft tr\u1ef1c ti\u1ebfp\u00a0\u0111\u01b0\u1ee3c ti\u1ebfng vi\u1ec7t c\u00f3 d\u1ea5u n\u00ean c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng gi\u00e1n ti\u1ebfp)<\/p>\n<p>strPattern = \u201c[ABCD]-\u201d &amp; sheet1.Cells(7,1).value<\/p>\n<p><strong>* Ki\u1ec3m tra k\u1ebft qu\u1ea3:<\/strong><\/p>\n<p>\u0110\u1eb7t <strong>Break Point<\/strong> t\u1ea1i <strong>End Sub<\/strong> r\u1ed3i th\u1ef1c\u00a0 hi\u1ec7n <strong>Run Macro<\/strong> n\u00e0y. K\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c t\u1ea1i m\u1ee5c <strong>Local<\/strong> nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14464\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-regexp-04.png\" width=\"1000\" height=\"504\" title=\"heo-regexp-04\"><\/p>\n<p>Nh\u01b0 v\u1eady 3 k\u1ebft qu\u1ea3 c\u1ee7a <strong>matches<\/strong> \u0111\u00e3 \u0111\u00fang nh\u01b0 ch\u00fang ta mong mu\u1ed1n.<\/p>\n<p>V\u1edbi vi\u1ec7c s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng Regular\u00a0Expression ch\u00fang ta c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n vi\u1ec7c tr\u00edch l\u1ecdc d\u1eef li\u1ec7u\u00a0theo quy lu\u1eadt trong 1 chu\u1ed7i l\u1edbn d\u1ec5 d\u00e0ng h\u01a1n nhi\u1ec1u r\u1ed3i ph\u1ea3i kh\u00f4ng n\u00e0o.<\/p>\n<p><strong>* Video h\u01b0\u1edbng d\u1eabn:<\/strong><\/p>\n<p>Ngo\u00e0i ra b\u1ea1n c\u00f3 th\u1ec3 xem th\u00eam video h\u01b0\u1edbng d\u1eabn\u00a0\u0111\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n c\u00e1ch l\u00e0m nh\u00e9:<\/p>\n<iframe loading=\"lazy\" title=\"Ho\u0323c Regular Expression trong VBA 01 | Ca\u0301ch su\u031b\u0309 du\u0323ng Regular Expression trong VBA\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/uqJYaT8SGxs?feature=oembed\" frameborder allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe>\n<p>Ch\u00fac c\u00e1c b\u1ea1n h\u1ecdc t\u1ed1t c\u00f9ng H\u1ecdc\u00a0Excel Online!<\/p>\n<p>\u00a0<\/p>\n<p>\u00a0<\/p>\n<p>\u00a0<\/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-188.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5388,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5387","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\/5387","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=5387"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5387\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5388"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5387"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5387"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5387"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}