{"id":541,"date":"2024-12-02T02:25:10","date_gmt":"2024-12-02T02:25:10","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-loc-lay-so-trong-o-lan-ca-so-va-chu-hoac-nguoc-lai\/"},"modified":"2024-12-02T02:25:10","modified_gmt":"2024-12-02T02:25:10","slug":"cach-loc-lay-so-trong-o-lan-ca-so-va-chu-hoac-nguoc-lai","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-loc-lay-so-trong-o-lan-ca-so-va-chu-hoac-nguoc-lai\/","title":{"rendered":"C\u00e1ch l\u1ecdc l\u1ea5y s\u1ed1 trong \u00f4 l\u1eabn c\u1ea3 s\u1ed1 v\u00e0 ch\u1eef ho\u1eb7c ng\u01b0\u1ee3c l\u1ea1i"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\n<p>Trong b\u00e0i vi\u1ebft n\u00e0y, m\u00ecnh s\u1ebd chia s\u1ebb v\u1edbi c\u00e1c b\u1ea1n c\u00e1ch ch\u00fang ta c\u00f3 th\u1ec3 l\u1ecdc l\u1ea5y s\u1ed1 ho\u1eb7c ch\u1eef trong m\u1ed9t \u00f4 Excel c\u00f3 l\u1eabn hai lo\u1ea1i d\u1eef li\u1ec7u n\u00e0y b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m Excel ho\u1eb7c h\u00e0m t\u1ef1 t\u1ea1o trong VBA.<\/p>\n<p>Trong m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p, khi b\u1ea1n nh\u1eadn l\u1ea1i \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u m\u00e0 c\u00f9ng trong m\u1ed9t \u00f4 c\u00f3 l\u1eabn c\u1ea3 s\u1ed1 v\u00e0 ch\u1eef, v\u00e0 nhi\u1ec7m v\u1ee5 c\u1ee7a b\u1ea1n l\u00e0 c\u1ea7n t\u00e1ch 2 lo\u1ea1i d\u1eef li\u1ec7u n\u00e0y ra th\u00e0nh c\u00e1c c\u1ed9t ri\u00eang bi\u1ec7t \u0111\u1ec3 ph\u1ee5c v\u1ee5 cho c\u00f4ng vi\u1ec7c c\u1ee7a m\u00ecnh.<\/p>\n<p>N\u1ebfu d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n c\u00f3 c\u1ea5u tr\u00fac, c\u00f3 quy lu\u1eadt r\u00f5 r\u00e0ng th\u00ec b\u1ea1n c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng gi\u1ea3i quy\u1ebft vi\u1ec7c n\u00e0y b\u1eb1ng c\u00e1c h\u00e0m Excel c\u00f3 s\u1eb5n nh\u01b0 h\u00e0m LEFT, h\u00e0m RIGHT, h\u00e0m MID v\u00e0 m\u1ed9t s\u1ed1 h\u00e0m x\u1eed l\u00fd chu\u1ed7i kh\u00e1c trong Excel d\u1ef1a v\u00e0o v\u1ecb tr\u00ed xu\u1ea5t hi\u1ec7n c\u1ee7a chu\u1ed7i trong \u00f4 d\u1eef li\u1ec7u \u0111\u00f3. \u0110\u00e2y l\u00e0 nh\u1eefng tr\u01b0\u1eddng h\u1ee3p l\u00fd t\u01b0\u1edfng, b\u1ea1n c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng gi\u1ea3i quy\u1ebft, trong b\u00e0i vi\u1ebft n\u00e0y, ch\u00fang ta h\u00e3y c\u00f9ng nhau \u0111i gi\u1ea3i quy\u1ebft nh\u1eefng tr\u01b0\u1eddng h\u1ee3p kh\u00f3 h\u01a1n trong th\u1ef1c t\u1ebf c\u00f4ng vi\u1ec7c: d\u1eef li\u1ec7u s\u1ed1 v\u00e0 ch\u1eef l\u1eabn l\u1ed9n kh\u00f4ng theo m\u1ed9t quy lu\u1eadt n\u00e0o.<\/p>\n<p>N\u1ebfu c\u00e1c b\u1ea1n mu\u1ed1n v\u1eeba l\u00e0m v\u1eeba th\u1ef1c h\u00e0nh, h\u00e3y theo d\u00f5i video sau \u0111\u00e2y:<\/p>\n<div id=\"erdyt-674d1a8523a95\" data-id=\"hcGMdiJDI0w\" class=\"erd-youtube-responsive\" style=\"position:relative;clear:both;width:100%;max-width:88%;margin-left:auto;margin-right:auto\">\n<div style=\"padding-bottom:56.25%\">\n<div class=\"erd-ytplay\" id=\"erdytp-hcGMdiJDI0w-674d1a8523a95\" data-vid=\"hcGMdiJDI0w\" data-src=\"https:\/\/www.youtube.com\/embed\/hcGMdiJDI0w?loop=1&amp;autoplay=1&amp;rel=0\" data-allowfullscreen=\"true\"><img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/hqdefault-6.jpg\" alt=\"YouTube video\" title=\"hqdefault-6\"><\/div>\n<\/div>\n<\/div>\n<p>\u00a0<\/p>\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-674d1a85280ab\" 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-loc-lay-so-trong-o-lan-ca-so-va-chu-hoac-nguoc-lai.html#Cach_loc_bo_chu_va_giu_lai_so_trong_o_Excel\" title=\"C\u00e1ch l\u1ecdc b\u1ecf ch\u1eef v\u00e0 gi\u1eef l\u1ea1i s\u1ed1 trong \u00f4 Excel\">C\u00e1ch l\u1ecdc b\u1ecf ch\u1eef v\u00e0 gi\u1eef l\u1ea1i s\u1ed1 trong \u00f4 Excel<\/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-loc-lay-so-trong-o-lan-ca-so-va-chu-hoac-nguoc-lai.html#Cach_dung_ham_tu_tao_trong_VBA_de_bo_chu_khoi_so\" title=\"C\u00e1ch d\u00f9ng h\u00e0m t\u1ef1 t\u1ea1o trong VBA \u0111\u1ec3 b\u1ecf ch\u1eef kh\u1ecfi s\u1ed1\">C\u00e1ch d\u00f9ng h\u00e0m t\u1ef1 t\u1ea1o trong VBA \u0111\u1ec3 b\u1ecf ch\u1eef kh\u1ecfi s\u1ed1<\/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\/cach-loc-lay-so-trong-o-lan-ca-so-va-chu-hoac-nguoc-lai.html#Cach_loc_bo_so_giu_lai_chu_trong_o_du_lieu_cua_Excel\" title=\"C\u00e1ch l\u1ecdc b\u1ecf s\u1ed1, gi\u1eef l\u1ea1i ch\u1eef trong \u00f4 d\u1eef li\u1ec7u c\u1ee7a Excel\">C\u00e1ch l\u1ecdc b\u1ecf s\u1ed1, gi\u1eef l\u1ea1i ch\u1eef trong \u00f4 d\u1eef li\u1ec7u c\u1ee7a Excel<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/cach-loc-lay-so-trong-o-lan-ca-so-va-chu-hoac-nguoc-lai.html#Cach_dung_ham_tu_tao_trong_VBA_de_bo_so_khoi_chu\" title=\"C\u00e1ch d\u00f9ng h\u00e0m t\u1ef1 t\u1ea1o trong VBA \u0111\u1ec3 b\u1ecf s\u1ed1 kh\u1ecfi ch\u1eef\">C\u00e1ch d\u00f9ng h\u00e0m t\u1ef1 t\u1ea1o trong VBA \u0111\u1ec3 b\u1ecf s\u1ed1 kh\u1ecfi ch\u1eef<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Cach_loc_bo_chu_va_giu_lai_so_trong_o_Excel\"><\/span>C\u00e1ch l\u1ecdc b\u1ecf ch\u1eef v\u00e0 gi\u1eef l\u1ea1i s\u1ed1 trong \u00f4 Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><em>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.<\/em><\/p>\n<p>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<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-3.gif\" title=\"vba101_92019-nho-3\"><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-25.gif\" title=\"ex101_92019-nho-25\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>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<pre><code class=\"language-xls\">=TEXTJOIN(\"\", TRUE, IFERROR(MID(A2, ROW(INDIRECT( \"1:\"&amp;LEN(A2))), 1) *1, \"\"))<\/code><\/pre>\n<p>Sau 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<p>\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<pre><code class=\"language-xls\">=TEXTJOIN(\"\", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, \"\"))<\/code><\/pre>\n<p>H\u00e3y c\u00f9ng theo d\u00f5i v\u00ed d\u1ee5 sau \u0111\u00e2y<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-36028\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-bo-chu-khoi-o-du-lieu.png\" alt=\"C\u00e1ch l\u1ecdc b\u1ecf ch\u1eef kh\u1ecfi \u00f4 d\u1eef li\u1ec7u trong Excel\" width=\"469\" height=\"221\" title=\"loc-bo-chu-khoi-o-du-lieu\"><\/p>\n<p><strong>C\u00e1ch c\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u1ed9ng nh\u01b0 sau<\/strong><\/p>\n<p>\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 <code class=\"excel-formula-inline\"><em>234 Summerset Avenue<\/em><\/code><\/p>\n<ol>\n<li>Ch\u00fang ta s\u1eed d\u1ee5ng c\u1ee5m <code class=\"excel-formula-inline\">ROW(INDIRECT(\"1:\"&amp;LEN(A2)))<\/code> ho\u1eb7c <code class=\"excel-formula-inline\">SEQUENCE(LEN(A2))<\/code> \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:<br \/>\n<code class=\"excel-formula-block\">{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}<\/code><\/li>\n<li>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<br \/>\n<code class=\"excel-formula-block\">MID(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<li>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<br \/>\n<code class=\"excel-formula-block\">{\"2\";\"3\";\"4\";\" \";\"S\";\"u\";\"m\";\"m\";\"e\";\"r\";\"s\";\"e\";\"t\";\" \";\"A\";\"v\";\"e\";\"n\";\"u\";\"e\"}<\/code><\/li>\n<li>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 <code class=\"excel-formula-inline\">#VALUE!<\/code><br \/>\n<code class=\"excel-formula-block\">{2;3;4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}<\/code><\/li>\n<li>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 <code class=\"excel-formula-inline\">#VALUE!<\/code> v\u1ec1 gi\u00e1 tr\u1ecb <code class=\"excel-formula-inline\">\"\"<\/code><br \/>\n<code class=\"excel-formula-block\">{2;3;4;\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\"}<\/code><\/li>\n<li>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 <code class=\"excel-formula-inline\">\"\"<\/code> v\u00e0 b\u1ecf qua nh\u1eefng gi\u00e1 tr\u1ecb r\u1ed7ng<br \/>\n<code class=\"excel-formula-block\">TEXTJOIN(\"\",true,{2;3;4;\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\"})<\/code><\/li>\n<\/ol>\n<h2><span class=\"ez-toc-section\" id=\"Cach_dung_ham_tu_tao_trong_VBA_de_bo_chu_khoi_so\"><\/span>C\u00e1ch d\u00f9ng h\u00e0m t\u1ef1 t\u1ea1o trong VBA \u0111\u1ec3 b\u1ecf ch\u1eef kh\u1ecfi s\u1ed1<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><em>C\u00e1ch l\u00e0m n\u00e0y s\u1ebd ho\u1ea1t \u0111\u1ed9ng \u0111\u01b0\u1ee3c tr\u00ean m\u1ecdi phi\u00ean b\u1ea3n Excel<\/em><\/p>\n<p>N\u1ebfu b\u1ea1n \u0111ang s\u1eed d\u1ee5ng m\u1ed9t phi\u00ean b\u1ea3n Excel c\u0169 h\u01a1n ho\u1eb7c c\u00e1c c\u00f4ng th\u1ee9c b\u00ean tr\u00ean qu\u00e1 kh\u00f3 nh\u1edb \u0111\u1ec3 \u00e1p d\u1ee5ng, v\u00e0 b\u1ea1n s\u1eb5n s\u00e0ng s\u1eed d\u1ee5ng VBA trong Excel, th\u00ec ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng ng\u00f4n ng\u1eef l\u1eadp tr\u00ecnh VBA trong Excel \u0111\u1ec3 c\u00f3 th\u1ec3 x\u1eed l\u00fd v\u1ea5n \u0111\u1ec1 t\u00e1ch ch\u1eef ra kh\u1ecfi s\u1ed1 n\u00e0y:<\/p>\n<pre><code class=\"language-vba\">Function RemoveText(str As String)\r\n\u00a0 \u00a0 Dim sRes As String\r\n\r\n\u00a0 \u00a0 sRes = \"\"\r\n\u00a0 \u00a0 For i = 1 To Len(str)\r\n\u00a0 \u00a0 \u00a0 \u00a0 If True = IsNumeric(Mid(str, i, 1)) Then\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 sRes = sRes &amp; Mid(str, i, 1)\r\n\u00a0 \u00a0 \u00a0 \u00a0 End If\r\n\u00a0 \u00a0 Next i\r\n\u00a0 \u00a0 RemoveText = sRes\r\nEnd Function<\/code><\/pre>\n<p>C\u00e1ch \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 copy \u0111o\u1ea1n code tr\u00ean v\u00e0o file Excel c\u1ee7a b\u1ea1n theo h\u01b0\u1edbng d\u1eabn \u1edf b\u00e0i vi\u1ebft sau \u0111\u00e2y c\u1ee7a Thanh:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/bat-dau-voi-excel-macros-va-lap-trinh-vba.html\" target=\"_blank\" rel=\"noopener\">B\u1eaft \u0111\u1ea7u v\u1edbi Excel VBA v\u00e0 Macro<\/a><\/p>\n<p>Ngo\u00e0i \u0111o\u1ea1n code ph\u00eda tr\u00ean s\u1eed d\u1ee5ng v\u00f2ng l\u1eb7p trong VBA, ch\u00fang ta c\u00f2n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng Regular Expression trong VBA \u0111\u1ec3 gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y nh\u01b0 sau. Logic l\u00e0: ch\u00fang ta s\u1ebd lo\u1ea1i b\u1ecf to\u00e0n b\u1ed9 c\u00e1c k\u00fd t\u1ef1 kh\u00f4ng ph\u1ea3i l\u00e0 c\u00e1c k\u00fd t\u1ef1 s\u1ed1 t\u1eeb 0 t\u1edbi 9.<\/p>\n<\/p>\n<div class=\"oembed-gist\">View the code on <a href=\"https:\/\/gist.github.com\/ndthanh\/a570ba4fbe5a1a5cd04306bd34e44957\">Gist<\/a>.<\/div>\n<p>S\u1ef1 kh\u00e1c bi\u1ec7t v\u1ec1 t\u1ed1c \u0111\u1ed9 gi\u1eefa 2 \u0111o\u1ea1n code n\u00e0y:<\/p>\n<ul style=\"list-style-type: square\">\n<li>Tr\u00ean nh\u1eefng Workbook Excel \u00edt d\u1eef li\u1ec7u, kh\u00f4ng c\u00f3 nhi\u1ec1u s\u1ef1 kh\u00e1c bi\u1ec7t<\/li>\n<li>Tr\u00ean nh\u1eefng Workbook Excel l\u1edbn h\u01a1n, \u0111o\u1ea1n code VBA th\u1ee9 hai s\u1eed d\u1ee5ng Regular Express s\u1ebd th\u1ef1c thi nhanh h\u01a1n<\/li>\n<\/ul>\n<p>D\u00f9 b\u1ea1n ch\u1ecdn c\u00e1ch n\u00e0o \u0111i n\u1eefa, th\u00ec c\u00e1ch s\u1eed d\u1ee5ng trong file Excel c\u0169ng ch\u1ec9 \u0111\u01a1n gi\u1ea3n nh\u01b0 sau:<\/p>\n<p><code>=RemoveText(A2)<\/code><\/p>\n<p>v\u1edbi A2 l\u00e0 \u00f4 ch\u1ee9a d\u1eef li\u1ec7u c\u1ea7n x\u1eed l\u00fd<\/p>\n<p><span class=\"note-label\">L\u01b0u \u00fd <\/span>c\u1ea3 c\u00e1ch d\u00f9ng h\u00e0m Excel v\u00e0 c\u00e1ch d\u00f9ng h\u00e0m t\u1ef1 t\u1ea1o trong VBA \u0111\u1ec1u cho k\u1ebft qu\u1ea3 l\u00e0 chu\u1ed7i \u201c234\u201d, \u0111\u1ec3 chuy\u1ec3n th\u00e0nh s\u1ed1, ch\u00fang ta c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n m\u1ed9t trong nh\u1eefng c\u00e1ch sau \u0111\u00e2y:<\/p>\n<ul style=\"list-style-type: square\">\n<li><code>=RemoveText(A2) + 0<\/code><\/li>\n<li><code>=RemoveText(A2) * 1<\/code><\/li>\n<li><code>=VALUE(RemoveText(A2))<\/code><\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Cach_loc_bo_so_giu_lai_chu_trong_o_du_lieu_cua_Excel\"><\/span>C\u00e1ch l\u1ecdc b\u1ecf s\u1ed1, gi\u1eef l\u1ea1i ch\u1eef trong \u00f4 d\u1eef li\u1ec7u c\u1ee7a Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><em>C\u00e1c c\u00f4ng th\u1ee9c sau \u0111\u00e2y ho\u1ea1t \u0111\u1ed9ng trong phi\u00ean b\u1ea3n Excel 365 v\u00e0 Excel 2019. 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.<\/em><\/p>\n<p>C\u00f4ng th\u1ee9c sau \u0111\u00e2y s\u1ebd lo\u1ea1i b\u1ecf s\u1ed1 trong m\u1ed9t \u00f4 d\u1eef li\u1ec7u c\u00f3 l\u1eabn c\u1ea3 ch\u1eef l\u1eabn s\u1ed1 trong Excel. V\u1ec1 logic \u0111\u1ec3 c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 hi\u1ec3u c\u00f4ng th\u1ee9c n\u00e0y th\u00ec c\u0169ng t\u01b0\u01a1ng t\u1ef1 nh\u01b0 c\u00e1ch gi\u1ea3i th\u00edch \u1edf ph\u1ea7n tr\u00ean.<\/p>\n<p>Gi\u1ea3 s\u1eed d\u1eef li\u1ec7u c\u1ea7n x\u1eed l\u00fd \u1edf \u00f4 A2, c\u00f4ng th\u1ee9c c\u1ee7a ch\u00fang ta s\u1ebd c\u00f3 d\u1ea1ng<\/p>\n<p><code class=\"excel-formula-block\">=TRIM(TEXTJOIN(\"\", TRUE, IF(ISERROR(MID(A2, ROW(INDIRECT( \"1:\"&amp;LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT(\"1:\"&amp;LEN(A2))), 1), \"\")))<\/code><\/p>\n<p>Trong Excel 2019, b\u1ea1n s\u1ebd c\u1ea7n nh\u1ea5n t\u1ed5 h\u1ee3p ph\u00edm Ctrl + Shift + Enter \u0111\u1ec3 nh\u1eadp c\u00f4ng th\u1ee9c m\u1ea3ng.<\/p>\n<p>Trong Excel 365 th\u00ec c\u00f4ng th\u1ee9c s\u1ebd nh\u01b0 sau:<\/p>\n<p><code class=\"excel-formula-block\">=TRIM(TEXTJOIN(\"\", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2) *1), MID(A2, SEQUENCE(LEN(A2)), 1), \"\")))<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-36031\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-loai-bo-so-ra-khoi-chu-trong-excel.png\" alt=\"C\u00e1ch lo\u1ea1i b\u1ecf s\u1ed1 ra kh\u1ecfi ch\u1eef trong \u00f4 Excel\" width=\"954\" height=\"276\" title=\"cach-loai-bo-so-ra-khoi-chu-trong-excel\"><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_dung_ham_tu_tao_trong_VBA_de_bo_so_khoi_chu\"><\/span>C\u00e1ch d\u00f9ng h\u00e0m t\u1ef1 t\u1ea1o trong VBA \u0111\u1ec3 b\u1ecf s\u1ed1 kh\u1ecfi ch\u1eef<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><em>C\u00e1ch l\u00e0m n\u00e0y s\u1ebd ho\u1ea1t \u0111\u1ed9ng \u0111\u01b0\u1ee3c tr\u00ean m\u1ecdi phi\u00ean b\u1ea3n Excel<\/em><\/p>\n<p>Sau \u0111\u00e2y l\u00e0 \u0111o\u1ea1n code \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n x\u1eed l\u00fd d\u1eef li\u1ec7u: lo\u1ea1i b\u1ecf s\u1ed1 ra kh\u1ecfi ch\u1eef:<\/p>\n<\/p>\n<div class=\"oembed-gist\">View the code on <a href=\"https:\/\/gist.github.com\/ndthanh\/3ad94dc79fc58309e9c9f758cdf36816\">Gist<\/a>.<\/div>\n<p>Ch\u00fang ta c\u0169ng c\u00f3 phi\u00ean b\u1ea3n th\u1ee9 hai, s\u1eed d\u1ee5ng Regular Expression trong Excel VBA<\/p>\n<\/p>\n<div class=\"oembed-gist\">View the code on <a href=\"https:\/\/gist.github.com\/ndthanh\/ff624ab7dec08efddb1b3faeb413244c\">Gist<\/a>.<\/div>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n c\u1ea7n x\u1eed l\u00fd nhi\u1ec1u d\u1eef li\u1ec7u, th\u00ec \u0111o\u1ea1n code s\u1eed d\u1ee5ng Regular Expression s\u1ebd ho\u1ea1t \u0111\u1ed9ng nhanh h\u01a1n. C\u00e1ch s\u1eed d\u1ee5ng trong m\u1ed9t Worksheet Excel nh\u01b0 sau:<\/p>\n<p><code class=\"excel-formula-block\">=TRIM(RemoveNumbers(A2))<\/code><\/p>\n<p>Nh\u01b0 v\u1eady, hi v\u1ecdng qua b\u00e0i chia s\u1ebb n\u00e0y, th\u00ec c\u00e1c b\u1ea1n \u0111\u00e3 bi\u1ebft th\u00eam m\u1ed9t k\u1ef9 thu\u1eadt r\u1ea5t h\u1eefu \u00edch d\u00f9ng \u0111\u1ec3 x\u1eed l\u00fd d\u1eef li\u1ec7u trong Excel VBA. \u0110\u1ec3 b\u1eaft \u0111\u1ea7u t\u1ef1 vi\u1ebft nh\u1eefng d\u00f2ng code VBA \u0111\u1ea7u ti\u00ean c\u1ee7a m\u00ecnh, h\u00e3y tham kh\u1ea3o kh\u00f3a h\u1ecdc VBA c\u00f9ng Thanh:<\/p>\n<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\" target=\"_blank\" rel=\"noopener\">VBA101 \u2013 T\u1ef1 \u0111\u1ed9ng ho\u00e1 Excel v\u1edbi l\u1eadp tr\u00ecnh VBA cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u<\/a><\/p>\n<p>v\u00e0 \u0111\u1eebng qu\u00ean tham kh\u1ea3o b\u00e0i vi\u1ebft Thanh \u0111\u00e3 s\u1eed d\u1ee5ng VBA \u0111\u1ec3 chinh ph\u1ee5c nh\u1eefng kh\u00e1ch h\u00e0ng kh\u00f3 t\u00ednh nh\u1ea5t b\u1eb1ng c\u00e1ch gi\u1ea3i quy\u1ebft nh\u1eefng v\u1ea5n \u0111\u1ec1 t\u1ed1n nhi\u1ec1u th\u1eddi gian nh\u1ea5t b\u1eb1ng c\u00e1ch t\u1ef1 \u0111\u1ed9ng ho\u00e0n to\u00e0n:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/vba-la-gi-toi-da-su-dung-vba-de-lam-nhung-cong-viec-gi.html\" target=\"_blank\" rel=\"noopener\">VBA l\u00e0 g\u00ec, t\u00f4i \u0111\u00e3 s\u1eed d\u1ee5ng VBA \u0111\u1ec3 l\u00e0m nh\u1eefng c\u00f4ng vi\u1ec7c g\u00ec?<\/a><\/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-3.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":542,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-541","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\/541","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=541"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/541\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/542"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=541"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=541"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=541"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}