=SUBSTITUTE(A2,"-","") xóa mọi dấu gạch ngang trong văn bản ở A2: SUBSTITUTE tìm văn bản cũ và đặt văn bản mới vào chỗ đó, ở đây là văn bản rỗng. Còn =SUBSTITUTE(A2,"-",".") sẽ đặt dấu chấm vào chỗ mỗi dấu gạch ngang.
| A | B | C | |
|---|---|---|---|
| 1 | Phone | Digits only | With dots |
| 2 | 555-201-3344 | 5552013344 | 555.201.3344 |
| 3 | 555-870-1122 | 5558701122 | 555.870.1122 |
| 4 | 555-433-9001 | 5554339001 | 555.433.9001 |
Kết quả là văn bản, kể cả khi chỉ còn lại chữ số. Thêm -- vào trước, =--SUBSTITUTE(A2,"-",""), khi bạn cần một con số.
SUBSTITUTE và REPLACE
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=REPLACE(old_text, start_num, num_chars, new_text)
SUBSTITUTE tìm theo nội dung: nó không quan tâm old_text nằm ở đâu. REPLACE không tìm gì cả: nó đếm ký tự và ghi đè num_chars ký tự bắt đầu từ start_num.
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | Report 2025 final | Report 2026 final |
| 3 | Report 2025 final | Report 2026 final |
| 4 | ID-0042 | SKU-0042 |
| 5 | 5551234 | 555-1234 |
- B2 và B3 cho cùng kết quả, nhưng B3 chỉ đúng vì năm bắt đầu ở ký tự thứ 8. Nếu văn bản thay đổi, SUBSTITUTE vẫn tìm thấy; REPLACE thì không.
- B4 đổi 2 ký tự đầu tiên, bất kể là gì, thành
SKU. - B5 dùng độ dài 0: không ký tự nào bị xóa, nên REPLACE chèn dấu gạch ngang sau ký tự thứ ba. Đây là cách chuẩn để chèn một ký tự vào một vị trí.
Dùng SUBSTITUTE khi bạn biết văn bản, REPLACE khi bạn biết vị trí. Với các thao tác theo vị trí, FIND có thể cung cấp vị trí: =REPLACE(A2,FIND(" ",A2),1,"_") thay khoảng trắng đầu tiên.
Chỉ thay lần xuất hiện thứ hai (thứ n)
Đối số thứ tư của SUBSTITUTE chọn lần xuất hiện cần đổi. Không có đối số này, mọi lần xuất hiện đều bị đổi:
| A | B | |
|---|---|---|
| 1 | Path | Result |
| 2 | 2026-03-15-final | 2026/03/15/final |
| 3 | 2026-03/15-final | |
| 4 | 2026-03-15 final |
B2 đổi mọi dấu gạch ngang. B3, với 2, chỉ đổi dấu thứ hai và cho 2026-03/15-final; B4 đặt một khoảng trắng vào chỗ dấu thứ ba. Số thứ tự lớn hơn số lần khớp thì không đổi gì cả.
| A | B | |
|---|---|---|
| 1 | Plan | New name |
| 2 | Basic monthly |
Đến lượt bạn: Ở B2, thay Basic bằng Standard trong tên gói ở A2, bằng một công thức.
Xóa nhiều ký tự khác nhau
Mỗi lời gọi SUBSTITUTE chỉ xử lý một old_text. Với nhiều ký tự, hãy lồng các lời gọi: mỗi lời gọi làm việc trên kết quả của lời gọi nằm bên trong nó.
| A | B | |
|---|---|---|
| 1 | Phone | Digits |
| 2 | (555) 201-3344 | 5552013344 |
| 3 | (555) 870-1122 | 5558701122 |
Đọc từ trong ra ngoài: xóa (, rồi ), rồi -, rồi khoảng trắng. Trong Microsoft 365, =REGEXREPLACE(A2,"[^0-9]","") xóa mọi ký tự không phải chữ số chỉ trong một lời gọi. Riêng với khoảng trắng thừa, TRIM là công cụ phù hợp hơn, vì nó giữ lại một khoảng trắng giữa các từ.
Đếm số lần xuất hiện của một ký tự hoặc một từ
SUBSTITUTE với văn bản rỗng xóa mọi lần xuất hiện, nên độ dài bị mất chính là số ký tự đã xóa. Chia cho độ dài của từ để đếm số từ:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Commas | "red" appears |
| 2 | red, green, red, blue | 3 | 2 |
| 3 | Red, red, red | 2 | 2 |
C3 trả về 2, không phải 3: SUBSTITUTE phân biệt chữ hoa chữ thường, nên Red không bị xóa. Dùng SUBSTITUTE(LOWER(A3),"red","") để đếm mà không phân biệt chữ hoa chữ thường.
| A | B | |
|---|---|---|
| 1 | Tags | Count |
| 2 | excel,text,formulas,tips |
Đến lượt bạn: Các thẻ trong A2 được ngăn cách bằng dấu phẩy. Ở B2, đếm xem có bao nhiêu thẻ (nhiều hơn số dấu phẩy một đơn vị).
Lỗi thường gặp: dùng SUBSTITUTE để đổi một con số
SUBSTITUTE làm việc trên dạng văn bản của con số, và kết quả là văn bản. =SUBSTITUTE(A2,"0","") với 1050 cho 15 ở dạng văn bản, và định dạng như $1,050.00 bị bỏ qua vì SUBSTITUTE đọc giá trị được lưu, 1050. Để đổi con số, hãy dùng phép tính. Để đổi văn bản trong nhiều ô cùng lúc mà không cần công thức, Home > Find & Select > Replace (Ctrl+H; Control+H trên Mac) có tùy chọn Match case và thay thế ngay tại chỗ.
Câu hỏi thường gặp
SUBSTITUTE và REPLACE trong Excel khác nhau thế nào?
SUBSTITUTE tìm văn bản theo nội dung: =SUBSTITUTE(A2,"old","new") đổi mọi chữ old. REPLACE làm việc theo vị trí: =REPLACE(A2,5,3,"new") ghi đè 3 ký tự bắt đầu từ ký tự thứ 5, bất kể đó là ký tự gì.
Làm sao để xóa một ký tự khỏi ô trong Excel?
Thay ký tự đó bằng văn bản rỗng: =SUBSTITUTE(A2,"-","") xóa mọi dấu gạch ngang. Với nhiều ký tự, hãy lồng các lời gọi: =SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","").
Làm sao để đếm số lần một ký tự xuất hiện trong ô?
So sánh độ dài trước và sau khi xóa ký tự đó: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) đếm số dấu phẩy trong A2. Với một từ, chia cho độ dài của từ: =(LEN(A2)-LEN(SUBSTITUTE(A2,"red","")))/3.
SUBSTITUTE có phân biệt chữ hoa chữ thường không?
Có. =SUBSTITUTE("Red red","red","blue") cho Red blue. Để không phân biệt chữ hoa chữ thường, hãy chuyển văn bản thành chữ thường trước, =SUBSTITUTE(LOWER(A2),"red","blue"), hoặc dùng Find and Replace (Ctrl+H), vốn mặc định không phân biệt chữ hoa chữ thường.
