{"id":7715,"date":"2024-12-21T18:19:58","date_gmt":"2024-12-21T18:19:58","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du\/"},"modified":"2024-12-21T18:19:58","modified_gmt":"2024-12-21T18:19:58","slug":"su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du\/","title":{"rendered":"S\u1eed d\u1ee5ng h\u00e0m REPLACE v\u00e0 SUBSTITUTE trong Excel \u2013 c\u00f4ng th\u1ee9c v\u00ed d\u1ee5"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p style=\"text-align: justify\">Trong b\u00e0i vi\u1ebft n\u00e0y, <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd gi\u1ea3i th\u00edch h\u00e0m <em>REPLACE<\/em> v\u00e0<em> SUBSTITUTE<\/em> trong Excel v\u1edbi c\u00e1c v\u00ed d\u1ee5. C\u00e1ch d\u00f9ng h\u00e0m <strong><em>REPLACE<\/em><\/strong> v\u1edbi chu\u1ed7i v\u0103n b\u1ea3n, s\u1ed1 v\u00e0 ng\u00e0y, v\u00e0 l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 l\u1eadp m\u1ed9t c\u00f4ng th\u1ee9c v\u1edbi nhi\u1ec1u h\u00e0m <strong><em>REPLACE<\/em><\/strong> ho\u1eb7c<strong> <em>SUBSTITUTE<\/em><\/strong>.<\/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-676706cd9f281\" 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\/su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du.html#Ham_REPLACE_trong_Excel\" title=\"H\u00e0m REPLACE trong Excel:\">H\u00e0m REPLACE trong 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\/su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du.html#Su_dung_ham_REPLACE_voi_ngay_thang\" title=\"S\u1eed d\u1ee5ng h\u00e0m REPLACE v\u1edbi ng\u00e0y th\u00e1ng\">S\u1eed d\u1ee5ng h\u00e0m REPLACE v\u1edbi ng\u00e0y th\u00e1ng<\/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\/su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du.html#Cac_ham_REPLACE_long_nhau_de_thuc_hien_nhieu_thay_the_trong_mot_o\" title=\"C\u00e1c h\u00e0m REPLACE l\u1ed3ng nhau \u0111\u1ec3 th\u1ef1c hi\u1ec7n nhi\u1ec1u thay th\u1ebf trong m\u1ed9t \u00f4:\">C\u00e1c h\u00e0m REPLACE l\u1ed3ng nhau \u0111\u1ec3 th\u1ef1c hi\u1ec7n nhi\u1ec1u thay th\u1ebf trong m\u1ed9t \u00f4:<\/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\/su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du.html#Thay_the_mot_chuoi_xuat_hien_o_vi_tri_khac_nhau_trong_moi_o\" title=\"Thay th\u1ebf m\u1ed9t chu\u1ed7i xu\u1ea5t hi\u1ec7n \u1edf v\u1ecb tr\u00ed kh\u00e1c nhau trong m\u1ed7i \u00f4:\">Thay th\u1ebf m\u1ed9t chu\u1ed7i xu\u1ea5t hi\u1ec7n \u1edf v\u1ecb tr\u00ed kh\u00e1c nhau trong m\u1ed7i \u00f4:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/su-dung-ham-replace-va-substitute-trong-excel-cong-thuc-vi-du.html#Thay_the_cac_ky_tu_khac_nhau_bang_mot_cong_thuc_long_cac_ham_SUBSTITUTE\" title=\"Thay th\u1ebf c\u00e1c k\u00fd t\u1ef1 kh\u00e1c nhau b\u1eb1ng m\u1ed9t c\u00f4ng th\u1ee9c (l\u1ed3ng c\u00e1c h\u00e0m SUBSTITUTE)\">Thay th\u1ebf c\u00e1c k\u00fd t\u1ef1 kh\u00e1c nhau b\u1eb1ng m\u1ed9t c\u00f4ng th\u1ee9c (l\u1ed3ng c\u00e1c h\u00e0m SUBSTITUTE)<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Ham_REPLACE_trong_Excel\"><\/span><strong>H\u00e0m <em>REPLACE<\/em> trong Excel:<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/cach-su-dung-ham-replace-trong-excel.html\"><em><strong>H\u00e0m REPLACE<\/strong><\/em><\/a> trong Excel cho ph\u00e9p b\u1ea1n thay \u0111\u1ed5i m\u1ed9t ho\u1eb7c nhi\u1ec1u k\u00fd t\u1ef1 trong m\u1ed9t chu\u1ed7i v\u0103n b\u1ea3n v\u1edbi m\u1ed9t k\u00fd t\u1ef1 kh\u00e1c ho\u1eb7c m\u1ed9t chu\u1ed7i c\u00e1c k\u00fd t\u1ef1.<\/p>\n<p style=\"text-align: justify\"><strong><em>REPLACE (old_text, start_num, num_chars, new_text)<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Nh\u01b0 b\u1ea1n th\u1ea5y, h\u00e0m <strong><em>REPLACE<\/em><\/strong> c\u00f3 4 \u0111\u1ed1i s\u1ed1, t\u1ea5t c\u1ea3 \u0111\u1ec1u \u0111\u01b0\u1ee3c y\u00eau c\u1ea7u.<\/p>\n<ul style=\"text-align: justify\">\n<li><strong><em>Old_text<\/em><\/strong> \u2013 v\u0103n b\u1ea3n g\u1ed1c (ho\u1eb7c tham chi\u1ebfu \u0111\u1ebfn \u00f4 c\u00f3 v\u0103n b\u1ea3n g\u1ed1c) m\u00e0 b\u1ea1n mu\u1ed1n thay th\u1ebf m\u1ed9t s\u1ed1 k\u00fd t\u1ef1.<\/li>\n<li><strong><em>Start_num<\/em><\/strong> \u2013 v\u1ecb tr\u00ed c\u1ee7a k\u00fd t\u1ef1 \u0111\u1ea7u ti\u00ean trong old_text m\u00e0 b\u1ea1n mu\u1ed1n thay th\u1ebf.<\/li>\n<li><strong><em>Num_chars<\/em><\/strong> \u2013 s\u1ed1 k\u00fd t\u1ef1 m\u00e0 b\u1ea1n mu\u1ed1n thay th\u1ebf.<\/li>\n<li><strong><em>New_text<\/em><\/strong> \u2013 v\u0103n b\u1ea3n thay th\u1ebf.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5: \u0111\u1ec3 thay \u0111\u1ed5i t\u1eeb \u201csun\u201d th\u00e0nh \u201cson\u201d, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (\u201csun\u201d, 2, 1, \u201co\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 n\u1ebfu b\u1ea1n \u0111\u1eb7t t\u1eeb mu\u1ed1n thay trong m\u1ed9t \u00f4, v\u00ed nh\u01b0 \u00f4 A2, b\u1ea1n c\u00f3 th\u1ec3 cung c\u1ea5p \u00f4 tham chi\u1ebfu t\u01b0\u01a1ng \u1ee9ng ch\u1ee9a <em>old_text<\/em>:<\/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-313.gif\" title=\"vba101_92019-nho-313\"><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-693.gif\" title=\"ex101_92019-nho-693\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p style=\"text-align: justify\"><strong>= REPLACE (A2, 2, 1, \u201co\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>\u00a0<img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-replace-function.png\" alt=\"Excel REPLACE function\" title=\"excel-replace-function\"><\/strong><\/p>\n<p style=\"text-align: justify\">Ch\u00fa \u00fd: N\u1ebfu \u0111\u1ed1i s\u1ed1 <em>start_num<\/em> ho\u1eb7c <em>num_chars<\/em> l\u00e0 s\u1ed1 \u00e2m ho\u1eb7c kh\u00f4ng ph\u1ea3i s\u1ed1, h\u00e0m <strong><em>Replace<\/em><\/strong> tr\u1ea3 k\u1ebft qu\u1ea3 l\u1ed7i<strong> <em>#VALUE!<\/em><\/strong>.<\/p>\n<p style=\"text-align: justify\"><strong>S\u1eed d\u1ee5ng h\u00e0m <em>REPLACE<\/em> v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb s\u1ed1<\/strong><\/p>\n<p style=\"text-align: justify\">H\u00e0m <strong><em>REPLACE<\/em><\/strong> \u0111\u01b0\u1ee3c thi\u1ebft k\u1ebf \u0111\u1ec3 l\u00e0m vi\u1ec7c v\u1edbi chu\u1ed7i v\u0103n b\u1ea3n. T\u1ea5t nhi\u00ean, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng n\u00f3 \u0111\u1ec3 thay th\u1ebf c\u00e1c k\u00fd t\u1ef1 s\u1ed1 l\u00e0 m\u1ed9t ph\u1ea7n c\u1ee7a m\u1ed9t chu\u1ed7i v\u0103n b\u1ea3n, v\u00ed d\u1ee5:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (A2, 7, 4, \u201c2016\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-replace-text-string.png\" alt=\"Replacing numeric characters that are part of a text string\" title=\"excel-replace-text-string\"><\/p>\n<p style=\"text-align: justify\">L\u01b0u \u00fd r\u1eb1ng ch\u00fang t\u00f4i \u0111\u1eb7t \u201c2016\u201d v\u00e0o d\u1ea5u nh\u00e1y k\u00e9p v\u00ec n\u00f3 l\u00e0 gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n.<\/p>\n<p style=\"text-align: justify\">Theo c\u00e1ch t\u01b0\u01a1ng t\u1ef1, b\u1ea1n c\u00f3 th\u1ec3 thay th\u1ebf m\u1ed9t ho\u1eb7c nhi\u1ec1u ch\u1eef s\u1ed1 trong m\u1ed9t s\u1ed1. V\u00ed d\u1ee5:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (A4, 4, 4, \u201c6\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 m\u1ed9t l\u1ea7n n\u1eefa, b\u1ea1n ph\u1ea3i \u0111\u1eb7t gi\u00e1 tr\u1ecb thay th\u1ebf trong d\u1ea5u nh\u00e1y k\u00e9p (\u201c6\u201d).<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-replace-numbers.png\" alt=\"Be sure to enclose the replacement value in double quotes.\" title=\"excel-replace-numbers\"><\/p>\n<p style=\"text-align: justify\"><strong>Ch\u00fa \u00fd.<\/strong> H\u00e0m <strong><em>REPLACE<\/em><\/strong> lu\u00f4n tr\u1ea3 k\u1ebft qu\u1ea3 l\u00e0 chu\u1ed7i v\u0103n b\u1ea3n ch\u1ee9 kh\u00f4ng ph\u1ea3i s\u1ed1. Trong h\u00ecnh tr\u00ean, ch\u00fa \u00fd \u0111\u1ebfn vi\u1ec7c c\u0103n l\u1ec1 b\u00ean tr\u00e1i c\u1ee7a gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n tr\u1ea3 v\u1ec1 trong B2, v\u00e0 so s\u00e1nh n\u00f3 v\u1edbi s\u1ed1 c\u0103n l\u1ec1 ph\u1ea3i trong A2. B\u1edfi v\u00ec \u0111\u00f3 l\u00e0 m\u1ed9t gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n n\u00ean kh\u00f4ng th\u1ec3 s\u1eed d\u1ee5ng trong c\u00e1c ph\u00e9p t\u00ednh tr\u1eeb khi b\u1ea1n chuy\u1ec3n n\u00f3 tr\u1edf l\u1ea1i s\u1ed1, v\u00ed d\u1ee5 nh\u00e2n v\u1edbi 1 ho\u1eb7c b\u1eb1ng nh\u1eefng ph\u01b0\u01a1ng ph\u00e1p kh\u00e1c \u0111\u01b0\u1ee3c m\u00f4 t\u1ea3 trong C\u00e1ch chuy\u1ec3n \u0111\u1ed5i v\u0103n b\u1ea3n sang s\u1ed1.<\/p>\n<p style=\"text-align: justify\"><strong>Xem th\u00eam<\/strong>: T\u1ed5ng h\u1ee3p c\u00e1c <a href=\"http:\/\/blog.ezworkapp.com\/thu-thuat-excel\">th\u1ee7 thu\u1eadt Excel hay<\/a><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Su_dung_ham_REPLACE_voi_ngay_thang\"><\/span><strong>S\u1eed d\u1ee5ng h\u00e0m <em>REPLACE<\/em> v\u1edbi ng\u00e0y th\u00e1ng<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">Nh\u01b0 b\u1ea1n \u0111\u00e3 th\u1ea5y, <a href=\"http:\/\/blog.ezworkapp.com\/ham-substitute-trong-excel-huong-dan-su-dung-va-co-vi-du-minh-hoa-cu-the.html\">h\u00e0m REPLACE<\/a> ho\u1ea1t \u0111\u1ed9ng t\u1ed1t v\u1edbi c\u00e1c con s\u1ed1, ngo\u1ea1i tr\u1eeb vi\u1ec7c n\u00f3 tr\u1ea3 k\u1ebft qu\u1ea3 l\u00e0 chu\u1ed7i v\u0103n b\u1ea3n \ud83d\ude42 Trong h\u1ec7 th\u1ed1ng Excel, c\u00e1c ng\u00e0y \u0111\u01b0\u1ee3c l\u01b0u d\u01b0\u1edbi d\u1ea1ng s\u1ed1, b\u1ea1n c\u00f3 th\u1ec3 th\u1eed s\u1eed d\u1ee5ng h\u00e0m <strong><em>Replace<\/em><\/strong> v\u1edbi ng\u00e0y th\u00e1ng. Nh\u01b0ng k\u1ebft qu\u1ea3 s\u1ebd kh\u00f4ng nh\u01b0 mong \u0111\u1ee3i.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, b\u1ea1n c\u00f3 m\u1ed9t ng\u00e0y trong A2, l\u00e0 1-Oct-14, v\u00e0 b\u1ea1n mu\u1ed1n thay \u0111\u1ed5i \u201cOct\u201d th\u00e0nh \u201cNov\u201d. V\u00ec v\u1eady, b\u1ea1n vi\u1ebft c\u00f4ng th\u1ee9c REPLACE (A2, 4, 3, \u201cNov\u201d) \u0111\u1ec3 n\u00f3i Excel thay th\u1ebf 3 k\u00fd t\u1ef1 trong \u00f4 A2, b\u1eaft \u0111\u1ea7u v\u1edbi k\u00ed t\u1ef1 th\u1ee9 4 \u2026 v\u00e0 c\u00f3 k\u1ebft qu\u1ea3 sau \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-replace-dates-wrong.png\" alt=\"A wrong way to use the REPLACE function on dates\" title=\"excel-replace-dates-wrong\"><\/p>\n<p style=\"text-align: justify\">T\u1ea1i sao v\u1eady? B\u1edfi v\u00ec \u201c01-Oct-14\u201d ch\u1ec9 l\u00e0 m\u1ed9t h\u00ecnh \u1ea3nh \u0111\u1ea1i di\u1ec7n c\u1ee7a con s\u1ed1 (41913) \u0111\u1ea1i di\u1ec7n cho ng\u00e0y th\u00e1ng. V\u00ec v\u1eady, h\u00e0m <strong>Replace<\/strong> thay \u0111\u1ed5i 3 s\u1ed1 cu\u1ed1i c\u00f9ng th\u00e0nh \u201cNov\u201d v\u00e0 tr\u1ea3 v\u1ec1 chu\u1ed7i v\u0103n b\u1ea3n \u201c419Nov\u201d.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 h\u00e0m <em>REPLACE<\/em> l\u00e0m vi\u1ec7c \u0111\u00fang v\u1edbi ng\u00e0y, \u0111\u1ea7u ti\u00ean b\u1ea1n c\u00f3 th\u1ec3 chuy\u1ec3n c\u00e1c ng\u00e0y th\u00e0nh chu\u1ed7i v\u0103n b\u1ea3n b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m <strong><em>TEXT<\/em><\/strong> ho\u1eb7c b\u1ea5t k\u1ef3 ph\u01b0\u01a1ng ph\u00e1p n\u00e0o \u0111\u01b0\u1ee3c tr\u00ecnh b\u00e0y trong <strong>C\u00e1ch chuy\u1ec3n \u0111\u1ed5i ng\u00e0y sang v\u0103n b\u1ea3n<\/strong>. Ngo\u00e0i ra, b\u1ea1n c\u00f3 th\u1ec3 \u0111\u01b0a h\u00e0m <em>TEXT <\/em>tr\u1ef1c ti\u1ebfp v\u00e0o <em>old_text <\/em>c\u1ee7a h\u00e0m <em>REPLACE<\/em>:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (TEXT (A2, \u201cdd-mmm-yy\u201d), 4, 3, \u201cNov\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-replace-dates-right.png\" alt=\"The right way to use the REPLACE function on dates\" title=\"excel-replace-dates-right\"><\/p>\n<p style=\"text-align: justify\">H\u00e3y nh\u1edb r\u1eb1ng k\u1ebft qu\u1ea3 c\u1ee7a c\u00f4ng th\u1ee9c tr\u00ean l\u00e0 m\u1ed9t chu\u1ed7i v\u0103n b\u1ea3n, v\u00ec v\u1eady ch\u1ec9 d\u00f9ng \u0111\u01b0\u1ee3c gi\u1ea3i ph\u00e1p n\u00e0y n\u1ebfu b\u1ea1n kh\u00f4ng s\u1eed d\u1ee5ng c\u00e1c ng\u00e0y \u0111\u00e3 s\u1eeda \u0111\u1ed5i \u0111\u1ec3 t\u00ednh to\u00e1n. N\u1ebfu b\u1ea1n c\u1ea7n ng\u00e0y ch\u1ee9 kh\u00f4ng ph\u1ea3i l\u00e0 chu\u1ed7i v\u0103n b\u1ea3n, s\u1eed d\u1ee5ng h\u00e0m <strong><em>DATEVALUE<\/em><\/strong> \u0111\u1ec3 \u0111\u01b0a c\u00e1c gi\u00e1 tr\u1ecb k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m <strong><em>REPLACE<\/em><\/strong> tr\u1edf l\u1ea1i d\u1ea1ng ng\u00e0y:<\/p>\n<p style=\"text-align: justify\"><strong>= DATEVALUE (REPLACE (TEXT (A2, \u201cdd-mmm-yy\u201d), 4, 3, \u201cNov\u201d))<\/strong><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Cac_ham_REPLACE_long_nhau_de_thuc_hien_nhieu_thay_the_trong_mot_o\"><\/span><strong>C\u00e1c h\u00e0m <em>REPLACE<\/em> l\u1ed3ng nhau \u0111\u1ec3 th\u1ef1c hi\u1ec7n nhi\u1ec1u thay th\u1ebf trong m\u1ed9t \u00f4:<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">B\u1ea1n th\u01b0\u1eddng ph\u1ea3i thay th\u1ebf nhi\u1ec1u \u0111o\u1ea1n trong c\u00f9ng m\u1ed9t \u00f4. B\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00e1c ch\u1ee9c n\u0103ng <strong><em>REPLACE<\/em><\/strong> l\u1ed3ng nhau cho ph\u00e9p b\u1ea1n th\u1ef1c hi\u1ec7n nhi\u1ec1u thay th\u1ebf ch\u1ec9 b\u1eb1ng m\u1ed9t c\u00f4ng th\u1ee9c. Trong ng\u1eef c\u1ea3nh n\u00e0y, \u201cl\u1ed3ng\u201d c\u00f3 ngh\u0129a l\u00e0 \u0111\u1eb7t m\u1ed9t h\u00e0m b\u00ean trong m\u1ed9t h\u00e0m kh\u00e1c.<\/p>\n<p style=\"text-align: justify\">Xem x\u00e9t v\u00ed d\u1ee5 sau: Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch c\u00e1c s\u1ed1 \u0111i\u1ec7n tho\u1ea1i trong c\u1ed9t A \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng l\u00e0 \u201c123456789\u201d v\u00e0 b\u1ea1n mu\u1ed1n l\u00e0m cho ch\u00fang tr\u00f4ng gi\u1ed1ng nh\u01b0 s\u1ed1 \u0111i\u1ec7n tho\u1ea1i b\u1eb1ng c\u00e1ch th\u00eam c\u00e1c d\u1ea5u n\u1ed1i. N\u00f3i c\u00e1ch kh\u00e1c, m\u1ee5c ti\u00eau c\u1ee7a b\u1ea1n l\u00e0 chuy\u1ec3n \u201c123456789\u201d th\u00e0nh \u201c123-456-789\u201d.<\/p>\n<p style=\"text-align: justify\">Ch\u00e8n d\u1ea5u n\u1ed1i \u0111\u1ea7u ti\u00ean l\u00e0 d\u1ec5 d\u00e0ng. B\u1ea1n vi\u1ebft m\u1ed9t c\u00f4ng th\u1ee9c <strong><em>Replace<\/em><\/strong> th\u00f4ng th\u01b0\u1eddng \u0111\u1ec3 thay th\u1ebf <strong>kh\u00f4ng k\u00fd t\u1ef1 n\u00e0o<\/strong> \u1edf v\u1ecb tr\u00ed th\u1ee9 4 b\u1eb1ng d\u1ea5u g\u1ea1ch ngang, t\u1ee9c l\u00e0 th\u00eam m\u1ed9t d\u1ea5u g\u1ea1ch n\u1ed1i v\u00e0o v\u1ecb tr\u00ed th\u1ee9 4 trong \u00f4:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (A2,4,0, \u201c-\u201c)<\/strong><\/p>\n<p style=\"text-align: justify\">K\u1ebft qu\u1ea3 c\u1ee7a c\u00f4ng th\u1ee9c Replace \u1edf tr\u00ean l\u00e0 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\/replace-first-hyphen.png\" alt=\"The REPLACE formula to add a hyphen in the 4&lt;sup&gt;th&lt;\/sup&gt; position in a cell\" title=\"replace-first-hyphen\"><\/p>\n<p style=\"text-align: justify\">\u0110\u01b0\u1ee3c r\u1ed3i, v\u00e0 b\u00e2y gi\u1edd ch\u00fang ta c\u1ea7n ch\u00e8n th\u00eam m\u1ed9t d\u1ea5u n\u1ed1i v\u00e0o v\u1ecb tr\u00ed th\u1ee9 8. \u0110\u1ec3 l\u00e0m \u0111i\u1ec1u n\u00e0y, b\u1ea1n \u0111\u1eb7t c\u00f4ng th\u1ee9c tr\u00ean trong m\u1ed9t h\u00e0m <em>REPLACE<\/em> kh\u00e1c. C\u1ee5 th\u1ec3 h\u01a1n, b\u1ea1n g\u1eafn n\u00f3 v\u00e0o \u0111\u1ed1i s\u1ed1 <em>old_text <\/em>c\u1ee7a h\u00e0m kh\u00e1c, \u0111\u1ec3 h\u00e0m <em>REPLACE<\/em> th\u1ee9 hai x\u1eed l\u00fd gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1 b\u1edfi <em>REPLACE<\/em> \u0111\u1ea7u ti\u00ean, m\u00e0 kh\u00f4ng ph\u1ea3i l\u00e0 gi\u00e1 tr\u1ecb trong \u00f4 A2:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (REPLACE (A2,4,0, \u201c-\u201c), 8,0, \u201c-\u201c)<\/strong><\/p>\n<p style=\"text-align: justify\">K\u1ebft qu\u1ea3 l\u00e0, b\u1ea1n s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c s\u1ed1 \u0111i\u1ec7n tho\u1ea1i \u1edf \u0111\u1ecbnh d\u1ea1ng mong mu\u1ed1n:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/nested-replace-functions.png\" alt=\"Using nested REPLACE functions in Excel\" title=\"nested-replace-functions\"><\/p>\n<p style=\"text-align: justify\">Theo c\u00e1ch t\u01b0\u01a1ng t\u1ef1, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00e1c h\u00e0m <strong><em>REPLACE<\/em> <\/strong>l\u1ed3ng v\u00e0o nhau \u0111\u1ec3 t\u1ea1o chu\u1ed7i v\u0103n b\u1ea3n tr\u00f4ng gi\u1ed1ng nh\u01b0 ng\u00e0y th\u00e1ng b\u1eb1ng c\u00e1ch th\u00eam d\u1ea5u g\u1ea1ch ch\u00e9o (\/) n\u1ebfu th\u00edch h\u1ee3p:<\/p>\n<p style=\"text-align: justify\"><strong>= (REPLACE (REPLACE (A2,3,0, \u201c\/\u201d), 6,0, \u201c\/\u201d))<\/strong><\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/nested-replace-functions2.png\" alt=\"A nested REPLACE formula to make text strings look like dates\" title=\"nested-replace-functions2\"><\/p>\n<p style=\"text-align: justify\">H\u01a1n n\u1eefa, b\u1ea1n c\u00f3 th\u1ec3 chuy\u1ec3n \u0111\u1ed5i chu\u1ed7i v\u0103n b\u1ea3n th\u00e0nh ng\u00e0y th\u1ef1c b\u1eb1ng c\u00e1ch l\u1ed3ng c\u00f4ng th\u1ee9c <strong><em>REPLACE<\/em><\/strong> \u1edf tr\u00ean v\u1edbi h\u00e0m <strong><em>DATEVALUE<\/em><\/strong>:<\/p>\n<p style=\"text-align: justify\"><strong>= DATEVALUE (REPLACE (REPLACE (A2,3,0, \u201c\/\u201d), 6,0, \u201c\/\u201d))<\/strong><\/p>\n<p style=\"text-align: justify\">B\u1ea1n kh\u00f4ng gi\u1edbi h\u1ea1n s\u1ed1 l\u01b0\u1ee3ng h\u00e0m m\u00e0 b\u1ea1n c\u00f3 th\u1ec3 l\u1ed3ng trong m\u1ed9t c\u00f4ng th\u1ee9c (c\u00e1c phi\u00ean b\u1ea3n hi\u1ec7n \u0111\u1ea1i c\u1ee7a Excel 2010, 2013 v\u00e0 2016 cho ph\u00e9p t\u1ed1i \u0111a 8192 k\u00fd t\u1ef1 v\u00e0 64 ch\u1ee9c n\u0103ng l\u1ed3ng v\u00e0o nhau trong m\u1ed9t c\u00f4ng th\u1ee9c).<\/p>\n<blockquote>\n<p style=\"text-align: justify\">\u0110\u0103ng k\u00fd ngay: <a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-co-ban\">Kh\u00f3a h\u1ecdc Word Excel c\u01a1 b\u1ea3n<\/a><\/p>\n<\/blockquote>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng 3 h\u00e0m <strong><em>REPLACE<\/em><\/strong> l\u1ed3ng nhau \u0111\u1ec3 c\u00f3 m\u1ed9t s\u1ed1 trong A2 xu\u1ea5t hi\u1ec7n nh\u01b0 ng\u00e0y th\u00e1ng v\u00e0 th\u1eddi gian:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (REPLACE (REPLACE (REPLACE (A2,3,0, \u201c\/\u201d), 6,0, \u201c\/\u201d), 9,0, \u201c\u201d), 12,0, \u201c:\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/nested-replace-functions3.png\" alt=\"The nested REPLACE functions make a number look like date and time\" title=\"nested-replace-functions3\"><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Thay_the_mot_chuoi_xuat_hien_o_vi_tri_khac_nhau_trong_moi_o\"><\/span><strong>Thay th\u1ebf m\u1ed9t chu\u1ed7i xu\u1ea5t hi\u1ec7n \u1edf v\u1ecb tr\u00ed kh\u00e1c nhau trong m\u1ed7i \u00f4:<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">Cho \u0111\u1ebfn nay, trong t\u1ea5t c\u1ea3 c\u00e1c v\u00ed d\u1ee5 ch\u00fang ta \u0111\u00e3 l\u00e0m vi\u1ec7c v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb c\u00f3 t\u00ednh ch\u1ea5t gi\u1ed1ng nhau v\u00e0 \u0111\u00e3 thay th\u1ebf \u1edf c\u00f9ng m\u1ed9t v\u1ecb tr\u00ed trong m\u1ed7i \u00f4. Nh\u01b0ng c\u00f4ng vi\u1ec7c th\u1ef1c t\u1ebf th\u01b0\u1eddng ph\u1ee9c t\u1ea1p h\u01a1n th\u1ebf. Trong b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n, c\u00e1c k\u00fd t\u1ef1 \u0111\u01b0\u1ee3c thay th\u1ebf c\u00f3 th\u1ec3 kh\u00f4ng nh\u1ea5t thi\u1ebft xu\u1ea5t hi\u1ec7n \u1edf c\u00f9ng m\u1ed9t n\u01a1i trong m\u1ed7i \u00f4 v\u00e0 do \u0111\u00f3 b\u1ea1n s\u1ebd ph\u1ea3i t\u00ecm v\u1ecb tr\u00ed c\u1ee7a k\u00fd t\u1ef1 \u0111\u1ea7u ti\u00ean c\u1ea7n \u0111\u01b0\u1ee3c thay th\u1ebf. V\u00ed d\u1ee5 sau \u0111\u00e2y s\u1ebd ch\u1ee9ng minh nh\u1eefng \u0111i\u1ec1u \u0111\u00f3.<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch \u0111\u1ecba ch\u1ec9 email trong c\u1ed9t A. V\u00e0 t\u00ean mi\u1ec1n c\u1ee7a m\u1ed9t c\u00f4ng ty \u0111\u00e3 thay \u0111\u1ed5i t\u1eeb \u201cABC\u201d sang, n\u00f3i \u201cBCA\u201d. V\u00ec v\u1eady, b\u1ea1n ph\u1ea3i c\u1eadp nh\u1eadt t\u1ea5t c\u1ea3 \u0111\u1ecba ch\u1ec9 email c\u1ee7a kh\u00e1ch h\u00e0ng theo.<\/p>\n<p style=\"text-align: justify\">Nh\u01b0ng v\u1ea5n \u0111\u1ec1 l\u00e0 t\u00ean kh\u00e1ch h\u00e0ng c\u00f3 chi\u1ec1u d\u00e0i kh\u00e1c nhau, v\u00e0 \u0111\u00f3 l\u00e0 l\u00fd do t\u1ea1i sao b\u1ea1n kh\u00f4ng th\u1ec3 x\u00e1c \u0111\u1ecbnh ch\u00ednh x\u00e1c v\u1ecb tr\u00ed b\u1eaft \u0111\u1ea7u c\u1ee7a t\u00ean c\u00f4ng ty. N\u00f3i c\u00e1ch kh\u00e1c, b\u1ea1n kh\u00f4ng bi\u1ebft gi\u00e1 tr\u1ecb cung c\u1ea5p trong \u0111\u1ed1i s\u1ed1 <strong>start_num <\/strong>c\u1ee7a h\u00e0m <strong><em>REPLACE<\/em><\/strong>. \u0110\u1ec3 t\u00ecm ra n\u00f3, s\u1eed d\u1ee5ng h\u00e0m <strong><em>FIND<\/em><\/strong> \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed c\u1ee7a k\u00ed t\u1ef1 \u0111\u1ea7u ti\u00ean trong chu\u1ed7i \u201c@abc\u201d:<\/p>\n<p style=\"text-align: justify\"><strong>= FIND(\u201c@ abc\u201d, A2)<\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 sau \u0111\u00f3, cung c\u1ea5p h\u00e0m <strong><em>FIND<\/em><\/strong> \u1edf tr\u00ean trong \u0111\u1ed1i s\u1ed1 <em>start_num <\/em>c\u1ee7a c\u00f4ng th\u1ee9c <strong><em>REPLACE<\/em><\/strong>:<\/p>\n<p style=\"text-align: justify\"><strong>= REPLACE (A2, FIND(\u201c@ abc\u201d, A2), 4 \u201c@bca\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>M\u1eb9o<\/strong>. Ch\u00fang t\u00f4i bao g\u1ed3m \u201c@\u201d trong c\u00f4ng th\u1ee9c <strong><em>FIND<\/em><\/strong> v\u00e0 <strong><em>REPLACE<\/em><\/strong> \u0111\u1ec3 tr\u00e1nh thay th\u1ebf t\u00ecnh c\u1edd trong ph\u1ea7n t\u00ean c\u1ee7a \u0111\u1ecba ch\u1ec9 th\u01b0 \u0111i\u1ec7n t\u1eed. T\u1ea5t nhi\u00ean, kh\u1ea3 n\u0103ng n\u00e0y x\u1ea3y ra r\u1ea5t \u00edt.<\/p>\n<p style=\"text-align: justify\">Nh\u01b0 b\u1ea1n th\u1ea5y trong h\u00ecnh sau, c\u00f4ng th\u1ee9c kh\u00f4ng c\u00f3 v\u1ea5n \u0111\u1ec1 g\u00ec khi t\u00ecm v\u00e0 thay th\u1ebf v\u0103n b\u1ea3n c\u0169 b\u1eb1ng c\u00e1i m\u1edbi. Tuy nhi\u00ean, n\u1ebfu chu\u1ed7i v\u0103n b\u1ea3n \u0111\u01b0\u1ee3c thay th\u1ebf kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y, c\u00f4ng th\u1ee9c tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u1ed7i <em>#VALUE!<\/em>:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-find-replace-formula.png\" alt=\"The Excel FIND and REPLACE formula to change the domain name in email addresses\" title=\"excel-find-replace-formula\"><\/p>\n<p style=\"text-align: justify\">V\u00e0 ch\u00fang t\u00f4i mu\u1ed1n c\u00f4ng th\u1ee9c tr\u1ea3 v\u1ec1 \u0111\u1ecba ch\u1ec9 th\u01b0 \u0111i\u1ec7n t\u1eed g\u1ed1c thay v\u00ec l\u1ed7i. V\u00ec v\u1eady, ch\u00fang ta h\u00e3y \u0111\u00ednh k\u00e8m c\u00f4ng th\u1ee9c <em>FIND &amp; REPLACE<\/em> c\u1ee7a ch\u00fang ta trong h\u00e0m <em>IFERROR<\/em>:<\/p>\n<p style=\"text-align: justify\"><strong>= IFERROR (REPLACE (A2, FIND (\u201c@ abc\u201d, A2), 4 \u201c@bca\u201d), A2)<\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 c\u00f4ng th\u1ee9c c\u1ea3i ti\u1ebfn n\u00e0y ho\u1ea1t \u0111\u1ed9ng kh\u00e1 ho\u00e0n h\u1ea3o, ph\u1ea3i kh\u00f4ng?<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-find-replace-iserror.png\" alt=\"The improved FIND \/ REPLACE formula\" title=\"excel-find-replace-iserror\"><\/p>\n<p style=\"text-align: justify\"><strong>M\u1eb9o<\/strong>. N\u1ebfu b\u1ea1n mu\u1ed1n thay th\u1ebf c\u00e1c d\u1eef li\u1ec7u ban \u0111\u1ea7u, c\u00f3 m\u1ed9t c\u00e1ch d\u1ec5 d\u00e0ng h\u01a1n l\u00e0 s\u1eed d\u1ee5ng h\u1ed9p tho\u1ea1i <strong><em>FIND<\/em> <\/strong>v\u00e0<strong> <em>REPLACE<\/em><\/strong> c\u1ee7a Excel.<\/p>\n<p style=\"text-align: justify\"><strong>H\u00e0m <em>SUBSTITUTE<\/em> trong Excel<\/strong><\/p>\n<p style=\"text-align: justify\">H\u00e0m <strong><em>SUBSTITUTE<\/em> <\/strong>trong Excel thay th\u1ebf m\u1ed9t ho\u1eb7c nhi\u1ec1u k\u00ed t\u1ef1 cho tr\u01b0\u1edbc t\u1ea1i m\u1ed9t ho\u1eb7c nhi\u1ec1u v\u1ecb tr\u00ed b\u1eb1ng k\u00ed t\u1ef1 c\u1ee5 th\u1ec3.<\/p>\n<p style=\"text-align: justify\">C\u00fa ph\u00e1p c\u1ee7a h\u00e0m <strong><em>SUBSTITUTE<\/em><\/strong> nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>SUBSTITUTE (<\/em><\/strong><strong>text, old_text, new_text, [instance_num])<\/strong><\/p>\n<p style=\"text-align: justify\">Ba \u0111\u1ed1i s\u1ed1 \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c y\u00eau c\u1ea7u v\u00e0 \u0111\u1ed1i s\u1ed1 cu\u1ed1i c\u00f9ng l\u00e0 t\u00f9y ch\u1ecdn.<\/p>\n<ul style=\"text-align: justify\">\n<li><strong><em>Text<\/em><\/strong>\u2013 v\u0103n b\u1ea3n ban \u0111\u1ea7u m\u00e0 b\u1ea1n mu\u1ed1n thay th\u1ebf c\u00e1c k\u00fd t\u1ef1. C\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c cung c\u1ea5p nh\u01b0 l\u00e0 m\u1ed9t chu\u1ed7i, \u00f4 tham chi\u1ebfu, ho\u1eb7c k\u1ebft qu\u1ea3 c\u1ee7a m\u1ed9t c\u00f4ng th\u1ee9c kh\u00e1c.<\/li>\n<li><strong><em>Old_text<\/em><\/strong> \u2013 k\u00fd t\u1ef1 m\u00e0 b\u1ea1n mu\u1ed1n thay th\u1ebf.<\/li>\n<li><strong><em>New_text<\/em><\/strong> \u2013 k\u00fd t\u1ef1 m\u1edbi \u0111\u1ec3 thay th\u1ebf old_text.<\/li>\n<li><strong><em>Instance_num<\/em><\/strong> \u2013 v\u1ecb tr\u00ed th\u1ef1c hi\u1ec7n thay th\u1ebf old_text. N\u1ebfu b\u1ecf qua, m\u1ed7i l\u1ea7n v\u0103n b\u1ea3n c\u0169 xu\u1ea5t hi\u1ec7n s\u1ebd \u0111\u01b0\u1ee3c thay \u0111\u1ed5i th\u00e0nh v\u0103n b\u1ea3n m\u1edbi.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, t\u1ea5t c\u1ea3 c\u00e1c c\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y thay th\u1ebf \u201c1\u201d b\u1eb1ng \u201c2\u201d trong \u00f4 A2, nh\u01b0ng tr\u1ea3 l\u1ea1i c\u00e1c k\u1ebft qu\u1ea3 kh\u00e1c nhau t\u00f9y thu\u1ed9c v\u00e0o s\u1ed1 b\u1ea1n cung c\u1ea5p trong \u0111\u1ed1i s\u1ed1 cu\u1ed1i c\u00f9ng:<\/p>\n<p style=\"text-align: justify\"><strong>= SUBSTITUTE (A2, \u201c1\u201d, \u201c2\u201d, 1)<\/strong> \u2013 Thay th\u1ebf s\u1ef1 xu\u1ea5t hi\u1ec7n \u0111\u1ea7u ti\u00ean c\u1ee7a \u201c1\u201d b\u1eb1ng \u201c2\u201d.<\/p>\n<p style=\"text-align: justify\"><strong>= SUBSTITUTE (A2, \u201c1\u201d, \u201c2\u201d, 2)<\/strong> \u2013 Thay th\u1ebf l\u1ea7n xu\u1ea5t hi\u1ec7n th\u1ee9 hai c\u1ee7a \u201c1\u201d b\u1eb1ng \u201c2\u201d.<\/p>\n<p style=\"text-align: justify\"><strong>= SUBSTITUTE (A2, \u201c1\u201d, \u201c2\u201d)<\/strong> \u2013 Thay th\u1ebf t\u1ea5t c\u1ea3 c\u00e1c l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a \u201c1\u201d b\u1eb1ng \u201c2\u201d.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-substitute-function.png\" alt=\"Excel SUBSTITUTE function\" title=\"excel-substitute-function\"><\/p>\n<p style=\"text-align: justify\"><strong>Ch\u00fa \u00fd<\/strong>. H\u00e0m <strong><em>SUBSTITUTE<\/em><\/strong> trong Excel ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng. V\u00ed d\u1ee5, c\u00f4ng th\u1ee9c sau \u0111\u00e2y s\u1ebd thay th\u1ebf t\u1ea5t c\u1ea3 c\u00e1c tr\u01b0\u1eddng h\u1ee3p ch\u1eef hoa \u201cX\u201d b\u1eb1ng ch\u1eef \u201cY\u201d trong \u00f4 A2, nh\u01b0ng n\u00f3 s\u1ebd kh\u00f4ng thay th\u1ebf b\u1ea5t k\u1ef3 tr\u01b0\u1eddng h\u1ee3p n\u00e0o c\u1ee7a ch\u1eef th\u01b0\u1eddng \u201cx\u201d.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-substitute-cese-sensotive.png\" alt=\"A case-sensitive Excel SUBSTITUTE formula\" title=\"excel-substitute-cese-sensotive\"><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Thay_the_cac_ky_tu_khac_nhau_bang_mot_cong_thuc_long_cac_ham_SUBSTITUTE\"><\/span><strong>Thay th\u1ebf c\u00e1c k\u00fd t\u1ef1 kh\u00e1c nhau b\u1eb1ng m\u1ed9t c\u00f4ng th\u1ee9c (l\u1ed3ng c\u00e1c h\u00e0m <em>SUBSTITUTE<\/em>)<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">Nh\u01b0 tr\u01b0\u1eddng h\u1ee3p v\u1edbi h\u00e0m <strong><em>REPLACE<\/em><\/strong>, b\u1ea1n c\u00f3 th\u1ec3 l\u1ed3ng c\u00e1c <strong><em>SUBSTITUTE<\/em> <\/strong>trong m\u1ed9t c\u00f4ng th\u1ee9c \u0111\u1ec3 th\u1ef1c hi\u1ec7n m\u1ed9t s\u1ed1 thay th\u1ebf c\u00f9ng l\u00fac, t\u1ee9c l\u00e0 thay th\u1ebf nhi\u1ec1u k\u00fd t\u1ef1 b\u1eb1ng m\u1ed9t c\u00f4ng th\u1ee9c.<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t chu\u1ed7i v\u0103n b\u1ea3n nh\u01b0 \u201cPR1, ML1, T1\u201d trong \u00f4 A2, trong \u0111\u00f3 \u201cPR\u201d l\u00e0 vi\u1ebft t\u1eaft c\u1ee7a \u201cProject\u201d, ML \u201cvi\u1ebft t\u1eaft c\u1ee7a\u201d Milestone \u201cv\u00e0\u201d T \u201cc\u00f3 ngh\u0129a l\u00e0\u201d Task \u201c. B\u1ea1n mu\u1ed1n thay th\u1ebf 3 ba m\u00e3 n\u00e0y v\u1edbi t\u00ean \u0111\u1ea7y \u0111\u1ee7. \u0110\u1ec3 \u0111\u1ea1t \u0111\u01b0\u1ee3c \u0111i\u1ec1u n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 vi\u1ebft 3 c\u00f4ng th\u1ee9c <em>SUBSTITUTE <\/em>kh\u00e1c nhau:<\/p>\n<p style=\"text-align: justify\"><strong>= SUBSTITUTE (A2, \u201cPR\u201d, \u201cProject \u201c)<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>= SUBSTITUTE (A2, \u201cML\u201d, \u201cMilestone \u201c)<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>= SUBSTITUTE (A2, \u201cT\u201d, \u201cTask \u201c)<\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 sau \u0111\u00f3 l\u1ed3ng v\u1edbi nhau trong 1 c\u00f4ng th\u1ee9c:<\/p>\n<p style=\"text-align: justify\"><strong>= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A2, \u201cPR\u201d, \u201cProject \u201c), \u201cML\u201d, \u201cMilestone \u201c), \u201cT\u201d, \u201cTask \u201c)<\/strong><\/p>\n<p style=\"text-align: justify\">L\u01b0u \u00fd r\u1eb1ng ch\u00fang ta th\u00eam m\u1ed9t d\u1ea5u c\u00e1ch v\u00e0o cu\u1ed1i m\u1ed7i \u0111\u1ed1i s\u1ed1 <em>new_text <\/em>\u0111\u1ec3 c\u00f3 th\u1ec3 \u0111\u1ecdc t\u1ed1t h\u01a1n.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-nested-substitute-functions.png\" alt=\"Using nested SUBSTITUTE functions in Excel\" title=\"excel-nested-substitute-functions\"><\/p>\n<p style=\"text-align: justify\"><strong>H\u00e0m<em> REPLACE<\/em> v\u00e0 <em>SUBSTITUTE<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">C\u00e1c h\u00e0m <em>REPLACE<\/em> v\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/ham-substitute-trong-excel-huong-dan-su-dung-va-co-vi-du-minh-hoa-cu-the.html\"><strong>h\u00e0m SUBSTITUTE<\/strong><\/a> c\u1ee7a Excel r\u1ea5t gi\u1ed1ng nhau v\u00e0 c\u1ea3 hai \u0111\u1ec1u \u0111\u01b0\u1ee3c thi\u1ebft k\u1ebf \u0111\u1ec3 thay \u0111\u1ed5i chu\u1ed7i v\u0103n b\u1ea3n. S\u1ef1 kh\u00e1c bi\u1ec7t c\u1ee7a hai h\u00e0m nh\u01b0 sau:<\/p>\n<ul style=\"text-align: justify\">\n<li><strong><em>SUBSTITUTE<\/em><\/strong> thay th\u1ebf m\u1ed9t ho\u1eb7c nhi\u1ec1u v\u1ecb tr\u00ed c\u1ee7a k\u00ed t\u1ef1 ho\u1eb7c chu\u1ed7i v\u0103n b\u1ea3n nh\u1ea5t \u0111\u1ecbnh. V\u00ec v\u1eady, n\u1ebfu b\u1ea1n ch\u1ec9 bi\u1ebft \u0111o\u1ea1n c\u1ea7n thay th\u1ebf, h\u00e3y s\u1eed d\u1ee5ng h\u00e0m <strong><em>SUBSTITUTE<\/em><\/strong>.<\/li>\n<li><strong>REPLACE<\/strong> thay th\u1ebf c\u00e1c k\u00fd t\u1ef1 t\u1ea1i m\u1ed9t v\u1ecb tr\u00ed x\u00e1c \u0111\u1ecbnh c\u1ee7a m\u1ed9t chu\u1ed7i v\u0103n b\u1ea3n. V\u00ec v\u1eady, n\u1ebfu b\u1ea1n bi\u1ebft v\u1ecb tr\u00ed c\u1ee7a k\u00ed t\u1ef1 \u0111\u01b0\u1ee3c thay th\u1ebf, s\u1eed d\u1ee5ng h\u00e0m <strong><em>REPLACE<\/em><\/strong>.<\/li>\n<li>H\u00e0m <strong><em>SUBSTITUTE<\/em><\/strong> cho ph\u00e9p th\u00eam m\u1ed9t tham s\u1ed1 t\u00f9y ch\u1ecdn (instance_num) x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed thay th\u1ebf c\u1ee7a <em>old_text<\/em> th\u00e0nh <em>new_text.<\/em><\/li>\n<\/ul>\n<p style=\"text-align: justify\">Ngu\u1ed3n: Ablebits, d\u1ecbch v\u00e0 bi\u00ean t\u1eadp b\u1edfi Hocexcel Online.<\/p>\n<p>R\u1ea5t nhi\u1ec1u ki\u1ebfn th\u1ee9c ph\u1ea3i kh\u00f4ng n\u00e0o? To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2w6YAe8\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a> c\u1ee7a H\u1ecdc Excel Online. \u0110\u00e2y l\u00e0 kh\u00f3a h\u1ecdc gi\u00fap b\u1ea1n h\u1ec7 th\u1ed1ng ki\u1ebfn th\u1ee9c m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, chi ti\u1ebft. H\u01a1n n\u1eefa kh\u00f4ng h\u1ec1 c\u00f3 gi\u1edbi h\u1ea1n v\u1ec1 th\u1eddi gian h\u1ecdc t\u1eadp n\u00ean b\u1ea1n c\u00f3 th\u1ec3 tho\u1ea3i m\u00e1i h\u1ecdc b\u1ea5t c\u1ee9 l\u00fac n\u00e0o, d\u1ec5 d\u00e0ng tra c\u1ee9u l\u1ea1i ki\u1ebfn th\u1ee9c khi c\u1ea7n. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 \u01b0u \u0111\u00e3i r\u1ea5t l\u1edbn cho b\u1ea1n khi \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc.<\/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-313.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":7716,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-7715","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\/7715","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=7715"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7715\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/7716"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=7715"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=7715"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=7715"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}