SUBSTITUTE
SUBSTITUTE(chuỗi, chữ_cũ, chữ_mới, [lần_thứ_mấy])Thay một đoạn chữ bằng đoạn chữ khác, dựa trên NỘI DUNG. Đây là hàm chủ lực khi dọn dữ liệu dán về: bỏ dấu chấm ngăn nghìn, gỡ ký tự lạ, chuẩn hoá cách viết tên công ty.
Các đối số
| Đối số | Bắt buộc? | Ý nghĩa |
|---|---|---|
| chuỗi | Bắt buộc | Ô cần xử lý. |
| chữ_cũ | Bắt buộc | Đoạn cần thay. PHÂN BIỆT hoa thường: thay "cty" sẽ không đụng tới "Cty". |
| chữ_mới | Bắt buộc | Thay bằng gì. Để hai dấu ngoặc kép rỗng nghĩa là xoá hẳn đoạn đó. |
| lần_thứ_mấy | Tuỳ chọn | Chỉ thay đúng lần xuất hiện thứ n. Bỏ trống thì thay TẤT CẢ. |
Trả về gì
Chuỗi mới. Kể cả khi kết quả toàn chữ số, nó vẫn là chữ — muốn cộng trừ phải bọc VALUE.
Ví dụ
=VALUE(SUBSTITUTE(A2, ".", ""))Bỏ dấu chấm ngăn nghìn của chuỗi 1.250.000 rồi đổi thành số thật
Kết quả: 1250000
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))Đổi dấu cách cứng của dữ liệu dán từ web thành dấu cách thường rồi mới TRIM
Kết quả: Công ty TNHH ABC
=SUBSTITUTE(A2, "Cty", "Công ty")Chuẩn hoá cách viết tên khách trước khi gộp danh mục
Kết quả: Công ty CP Thành Đạt
Lưu ý khi dùng
- Lồng nhiều SUBSTITUTE vào nhau để dọn nhiều thứ một lượt, nhưng quá ba tầng thì nên tách ra vài cột phụ cho dễ soát.
- Bộ ba dọn dữ liệu dán từ web theo thứ tự: SUBSTITUTE bỏ CHAR(160), rồi CLEAN, rồi TRIM. Đảo thứ tự là TRIM chạy trước khi ký tự lạ được đổi nên vô tác dụng.
Bẫy hay dính
Đoạn cần thay lại nằm lọt bên trong một đoạn khác: thay "01" thành rỗng để bỏ tiền tố thì mã 0105123456 cũng bị moi mất cụm 01 ở giữa, ra một mã vẫn đúng định dạng nên không ai nghi. Chặn bằng đối số lần_thứ_mấy, hoặc kiểm sau khi thay: LEN cột kết quả phải giảm đúng bằng số ký tự dự kiến.
Chọn cái nào
SUBSTITUTE hay REPLACE
SUBSTITUTE thay theo NỘI DUNG — biết chữ cần thay là gì. REPLACE thay theo VỊ TRÍ — biết nó nằm ở ký tự thứ mấy, không cần biết nội dung.
=SUBSTITUTE(A2, "Cty", "Công ty")