Có thể bạn quan tâm: Gộp Các Sheet Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Mở đầu: Tại sao hàm SUBSTITUTE lại quan trọng?
Trong môi trường làm việc với dữ liệu, việc xử lý và làm sạch dữ liệu (data cleaning) là một trong những công đoạn không thể thiếu. Dữ liệu thực tế thường chứa các ký tự không mong muốn, lỗi nhập liệu, hoặc các mẫu text cần được chuẩn hoá để phân tích tiếp theo. Excel, một trong những công cụ phổ biến nhất cho công việc này, cung cấp một loạt các hàm xử lý chuỗi ký tự, trong đó hàm SUBSTITUTE (tiếng Việt thường được gọi là “hàm THAY THẾ”) đóng vai trò then chốt.
Hàm SUBSTITUTE cho phép bạn thay thế một hoặc nhiều đoạn văn bản (substring) trong một ô bằng một giá trị mới, mà không làm thay đổi các ký tự khác. Đây là công cụ mạnh mẽ để:
- Xóa ký tự thừa như dấu gạch ngang, dấu chấm, khoảng trắng thừa.
- Chuẩn hoá định dạng (ví dụ: chuyển “01/02/2026” thành “01‑02‑2026”).
- Thay đổi mã sản phẩm, ví dụ “PROD‑001” → “PROD‑A001”.
- Kết hợp với các hàm khác (LEFT, RIGHT, MID, TEXT, IF, VLOOKUP…) để xây dựng các công thức phức tạp.
Bài viết này sẽ cung cấp cho bạn một hiểu biết sâu rộng về hàm SUBSTITUTE: cấu trúc, các tham số, cách sử dụng cơ bản, các ví dụ thực tế, so sánh với các hàm tương tự, và một loạt mẹ́o nâng cao giúp bạn tối ưu hoá quy trình làm việc.
Có thể bạn quan tâm: Gạch Đầu Dòng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Cú pháp và các tham số của hàm SUBSTITUTE
1.1. Cú pháp chuẩn
SUBSTITUTE(text, old_text, new_text, instance_num)
| Tham số | Kiểu dữ liệu | Mô tả |
|---|---|---|
| text | Text (hoặc reference) | Văn bản gốc chứa đoạn cần thay thế. |
| old_text | Text | Đoạn văn bản (hoặc ký tự) cần được thay thế. |
| new_text | Text | Đoạn văn bản (hoặc ký tự) sẽ thay thế cho old_text. |
| instance_num | Number (tùy chọn) | Số lần xuất hiện của old_text mà bạn muốn thay thế. Nếu bỏ qua, Excel sẽ thay thế tất cả các lần xuất hiện. |
1.2. Chi tiết các tham số
- text: Có thể là một ô chứa dữ liệu (ví dụ: A2) hoặc một chuỗi ký tự được đặt trong dấu ngoặc kép (“Hello World”). Khi là tham chiếu ô, công thức sẽ tự động cập nhật khi giá trị ô thay đổi.
- old_text: Đoạn cần thay thế. Nếu không tìm thấy old_text trong text, hàm trả về text nguyên gốc.
- new_text: Đoạn thay thế. Có thể là một chuỗi rỗng (“”) để thực hiện việc xóa đoạn ký tự.
- instance_num: Khi có nhiều hơn một lần xuất hiện của old_text, tham số này cho phép bạn chỉ thay thế lần thứ n. Ví dụ,
instance_num = 2sẽ chỉ thay thế lần xuất hiện thứ hai. Nếu giá trị vượt quá số lần xuất hiện thực tế, hàm trả về text không thay đổi.
1.3. Lưu ý quan trọng
- Hàm SUBSTITUTE không phân biệt chữ hoa/thường. Nếu muốn phân biệt, bạn cần kết hợp với hàm EXACT hoặc FIND.
- Khi old_text hoặc new_text là một chuỗi rỗng (“”), hàm thực hiện xóa hoặc chèn ký tự tại vị trí mong muốn.
- Tham số instance_num chỉ áp dụng khi old_text xuất hiện nhiều lần. Khi không cung cấp, tất cả các lần xuất hiện sẽ được thay thế.
Có thể bạn quan tâm: Câu Lệnh If Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao
2. Sử dụng cơ bản: Các ví dụ thực tế
2.1. Thay thế toàn bộ ký tự
Mục tiêu: Thay thế dấu gạch ngang (“-”) bằng dấu chấm (“.”) trong cột mã sản phẩm.
| A (Mã cũ) |
|---|
| ABC-123 |
| XYZ-456 |
| LMN-789 |
Công thức:
=SUBSTITUTE(A2, "-", ".")
Kết quả:
| B (Mã mới) |
|---|
| ABC.123 |
| XYZ.456 |
| LMN.789 |
2.2. Xóa ký tự không mong muốn
Mục tiêu: Loại bỏ dấu cách thừa ở đầu và cuối một chuỗi.
| A (Tên) |
|---|
| ” Nguyễn Văn A “ |
| ” Trần Thị B “ |
| ” Lê C “ |
Công thức:
=SUBSTITUTE(TRIM(A2), " ", "")
Giải thích: Đầu tiên dùng TRIM để loại bỏ khoảng trắng đầu/cuối, sau đó dùng SUBSTITUTE để xóa tất cả khoảng trắng còn lại trong chuỗi, nếu muốn chỉ xóa khoảng trắng đầu/cuối thì chỉ cần TRIM.
2.3. Thay thế lần xuất hiện thứ nhất
Mục tiêu: Thay thế dấu “/” đầu tiên trong ngày tháng năm thành dấu “-”.
| A (Ngày) |
|---|
| 12/03/2026 |
| 05/11/2026 |
| 31/12/2026 |
Công thức:
=SUBSTITUTE(A2, "/", "-", 1)
Kết quả:
| B (Ngày chuẩn) |
|---|
| 12-03/2026 |
| 05-11/2026 |
| 31-12/2026 |
Ở đây chỉ thay đổi dấu “/” đầu tiên, còn lại giữ nguyên.
2.4. Thay thế nhiều ký tự đồng thời (kết hợp hàm)
Mục tiêu: Chuyển đổi định dạng số điện thoại “(012) 345‑6789” thành “012-345-6789”.
| A (SĐT gốc) |
|---|
| (012) 345‑6789 |
| (098) 765‑4321 |
Công thức (lồng 3 lần SUBSTITUTE):
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), " ", "-")
Kết quả:
| B (SĐT chuẩn) |
|---|
| 012-345-6789 |
| 098-765-4321 |
3. So sánh SUBSTITUTE với các hàm thay thế khác
| Hàm | Đặc điểm | Khi dùng |
|---|---|---|
| SUBSTITUTE | Thay thế text trong chuỗi, không phân biệt vị trí, có tùy chọn instance_num. | Thay thế ký tự/chuỗi bất kỳ, không cần dựa vào vị trí. |
| REPLACE | Thay thế dựa trên vị trí và số ký tự (cần biết vị trí bắt đầu và độ dài). | Thay đổi phần cố định trong chuỗi, ví dụ: thay đổi 3 ký tự từ vị trí 5. |
| TEXTJOIN + FILTERXML (đối với Excel 2026+), REGEXREPLACE (Google Sheets) | Thay thế theo biểu thức chính quy (regex). | Khi cần thay thế phức tạp, pattern matching. |
| FIND/SEARCH + LEFT/RIGHT/MID | Tìm vị trí rồi cắt ghép lại. | Khi muốn thay thế dựa trên vị trí động, hoặc khi SUBSTITUTE không đáp ứng yêu cầu. |
Khi nào nên dùng SUBSTITUTE?
- Khi bạn biết đoạn ký tự cần thay thế, nhưng không biết vị trí xuất hiện.
- Khi cần thay thế tất cả hoặc các lần xuất hiện cụ thể.
- Khi muốn xóa một ký tự (đặt
new_text = "").
Khi nào nên dùng REPLACE?
- Khi bạn có vị trí và độ dài cố định của phần cần thay đổi, ví dụ: “ABCD1234” → thay “1234” thành “5678” (
REPLACE(A2,5,4,"5678")).
4. Ứng dụng nâng cao: Kết hợp SUBSTITUTE với các hàm khác
4.1. Tách và chuẩn hoá địa chỉ email
Giả sử cột A chứa email dạng “Tên Đệm email@example.com”. Bạn muốn tách ra tên và địa chỉ email riêng biệt.
| A (Email gốc) |
|---|
| Nguyễn Văn A nguyenvana@example.com |
| Trần Thị B tranb@example.org |
Bước 1: Loại bỏ dấu “<” và “>”
=SUBSTITUTE(SUBSTITUTE(A2, "<", ""), ">", "")
Kết quả tạm thời: “Nguyễn Văn A email@example.com”
Bước 2: Tách tên và email bằng FIND và LEFT/RIGHT
=TRIM(LEFT(B2, FIND("@", B2)-1-1)) // Tên
=TRIM(RIGHT(B2, LEN(B2)-FIND("@", B2)+1)) // Email
Bạn có thể gộp lại thành một công thức duy nhất:
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A2,"<",""),">",""), FIND("@", SUBSTITUTE(SUBSTITUTE(A2,"<",""),">",""))-2))
4.2. Thay đổi định dạng ngày tháng trong báo cáo
Có thể bạn quan tâm: Cách Hiện Thanh Công Cụ (toolbar) Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
Trong một báo cáo, ngày tháng được nhập dưới dạng “dd/mm/yyyy”, nhưng cần xuất ra “yyyy-mm-dd” cho hệ thống khác.
| A (Ngày) |
|---|
| 15/04/2026 |
| 01/12/2026 |
Công thức:
=TEXT(DATE(MID(A2,7,4), MID(A2,4,2), LEFT(A2,2)), "yyyy-mm-dd")
Nếu muốn dùng SUBSTITUTE để thay dấu “/” bằng “-” và sau đó dùng DATEVALUE:
=TEXT(DATEVALUE(SUBSTITUTE(A2, "/", "-")), "yyyy-mm-dd")
4.3. Tạo mã số tự động từ tên
Bạn muốn tạo mã nhân viên dựa trên tên: lấy ký tự đầu tiên của họ và năm sinh.
| A (Tên + Năm sinh) |
|---|
| Nguyễn Văn A 1990 |
| Trần Thị B 1985 |
Công thức:
=UPPER(LEFT(SUBSTITUTE(A2," ",""),1)) & RIGHT(A2,4)
Ở đây SUBSTITUTE(A2," ","") loại bỏ dấu cách để lấy ký tự đầu tiên chính xác.
4.4. Xử lý dữ liệu đa ngôn ngữ
Khi dữ liệu chứa các ký tự đặc biệt (ví dụ tiếng Việt có dấu), bạn muốn loại bỏ dấu để so sánh.
Excel không có hàm chuẩn để bỏ dấu, nhưng có thể dùng bảng thay thế:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,
"á","a"),"à","a"),"ả","a"),"ã","a"),"ạ","a")
Bạn có thể lặp lại cho toàn bộ các nguyên âm có dấu. Để ngắn gọn, tạo một hàm tùy chỉnh (VBA) hoặc dùng Power Query.
5. Thực hành: Bài tập thực tế và giải pháp
Bài 1: Chuẩn hoá mã sản phẩm
Dữ liệu (cột A):PROD-001, PROD-002, PROD-003A, PROD-004B
Yêu cầu:
– Loại bỏ dấu “-”.
– Thêm tiền tố “X” trước số.
Giải pháp:
="X"&SUBSTITUTE(A2,"-","")
Kết quả: XPROD001, XPROD002, XPROD003A, XPROD004B.
Bài 2: Xóa ký tự “” và “#” trong mô tả
Dữ liệu (cột B):Sản phẩm tốt#, #Khuyến mãi, Giảm giá#
Giải pháp (đặt trong C2):
=SUBSTITUTE(SUBSTITUTE(B2,"",""),"#","")
Kết quả: Sản phẩm tốt, Khuyến mãi, Giảm giá.
Bài 3: Thay đổi dấu chấm thành dấu phẩy trong số thập phân
Dữ liệu (cột C):12.345, 0.99, 123.4567
Giải pháp:
=SUBSTITUTE(C2,".",",")
Kết quả: 12,345, 0,99, 123,4567.
Bài 4: Thay thế lần xuất hiện thứ hai của “/”
Dữ liệu (cột D):/home/user/docs/file.txt
Mục tiêu: Thay “/” thứ hai bằng “\”.
Giải pháp:
=SUBSTITUTE(D2, "/", "\", 2)
Kết quả: /home\user/docs/file.txt.
6. Những lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #VALUE! | old_text hoặc new_text không phải là chuỗi, hoặc instance_num không phải số nguyên dương. | Kiểm tra dữ liệu, dùng TEXT() để chuyển đổi. |
| Kết quả không thay đổi | old_text không tồn tại trong text (có thể do lỗi chính tả, khoảng trắng thừa). | Dùng TRIM, CLEAN, hoặc SEARCH để xác định vị trí. |
| Thay thế không phân biệt hoa/thường | Bạn muốn thay thế chỉ một dạng (ví dụ “ABC” nhưng không “abc”). | Kết hợp với EXACT hoặc dùng hàm REPLACE dựa trên vị trí. |
| Thay thế quá nhiều | Khi không dùng instance_num, hàm sẽ thay thế tất cả. | Thêm instance_num hoặc dùng hàm LEFT/RIGHT để giới hạn. |
| Ký tự đặc biệt không được nhận diện (ví dụ: dấu gạch ngang “‑” khác “-”) | Các ký tự Unicode trông giống nhau nhưng mã khác nhau. | Sao chép ký tự thực tế từ ô, hoặc dùng CODE() để kiểm tra mã ký tự. |
7. Tối ưu hoá hiệu suất khi làm việc với hàng ngàn dòng
Khi bạn áp dụng SUBSTITUTE trên một bảng dữ liệu lớn (hàng nghìn – hàng chục nghìn), có một số chiến lược để giảm thời gian tính toán:
- Sử dụng bảng tính tĩnh (Values Only)
- Sau khi tính công thức, sao chép và dán dưới dạng Giá trị (Paste Values) để ngừng tính toán lại.
- Áp dụng công thức trong Power Query
- Power Query (Get & Transform) cho phép thực hiện thay thế trên dữ liệu trước khi tải vào bảng tính, nhanh hơn và không làm nặng Excel.
- Hàm mảng (Array Formula) trong Office 365/Excel 2026
- Dùng
LETđể lưu kết quả trung gian, giảm số lần gọiSUBSTITUTE. Ví dụ:
excel =LET( txt, A2, step1, SUBSTITUTE(txt, "-", ""), step2, SUBSTITUTE(step1, " ", ""), step2 )
- VBA Macro
- Khi cần thực hiện thay đổi trên toàn bộ cột, viết macro:
vba Sub ReplaceBatch() Dim rng As Range, cell As Range Set rng = Range("A2:A10000") For Each cell In rng cell.Value = Replace(cell.Value, "-", "") cell.Value = Replace(cell.Value, " ", "") Next cell End Sub
- Sử dụng tính năng “Find & Replace” (Ctrl+H)
- Đối với thay đổi đơn giản, công cụ này nhanh hơn vì thực hiện trực tiếp trên sheet mà không tạo công thức.
8. Thực hành nâng cao: Tạo hàm tùy chỉnh (UDF) bằng VBA
Nếu bạn thấy việc lồng nhiều hàm SUBSTITUTE gây rối mắt, có thể tạo một hàm tùy chỉnh cho phép truyền một mảng các cặp thay thế.
Function MultiSub(ByVal txt As String, ByVal oldNew As Variant) As String Dim i As Long Dim result As String result = txt For i = LBound(oldNew, 1) To UBound(oldNew, 1) result = Replace(result, oldNew(i, 1), oldNew(i, 2)) Next i MultiSub = result
End Function
Cách dùng trong Excel:
=MultiSub(A2, {{"-",""}, {" ",""}, {"_","-"}})
Lưu ý: Đối với Excel 365, hàm LAMBDA cũng có thể thực hiện tương tự mà không cần VBA.
9. Tổng kết: Khi nào và làm sao khai thác tối đa hàm SUBSTITUTE
- Hiểu rõ yêu cầu: Xác định bạn muốn thay thế toàn bộ hay chỉ một lần.
- Chuẩn bị dữ liệu: Dùng
TRIM,CLEANđể loại bỏ khoảng trắng thừa, ký tự không hiển thị. - Kết hợp hàm:
SUBSTITUTE+TEXT,DATE,LEFT/RIGHT,LET,IFERRORđể tạo công thức mạnh mẽ. - Kiểm tra kết quả: Luôn dùng
IFERRORhoặcIFđể tránh lỗi khiold_textkhông tồn tại.
=IFERROR(SUBSTITUTE(A2, "-", ""), A2)
- Tối ưu cho dữ liệu lớn: Sử dụng Power Query, VBA hoặc chuyển sang giá trị tĩnh sau khi tính toán.
Với kiến thức chi tiết trên, bạn đã nắm vững cách sử dụng hàm THAY THẾ (SUBSTITUTE) trong mọi tình huống: từ việc làm sạch dữ liệu cơ bản, chuẩn hoá định dạng, cho tới các giải pháp phức tạp kết hợp nhiều hàm và macro. Hãy áp dụng ngay vào dự án của mình và trải nghiệm sự tăng tốc đáng kể trong công việc xử lý dữ liệu!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.