Công thức chung
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Tóm lược
Để tìm và thay thế nhiều giá trị bằng một công thức, bạn có thể lồng nhiều hàm SUBSTITUTE lại với nhau và nạp các cặp tìm / thay thế từ một bảng khác bằng hàm INDEX. Trong ví dụ được hiển thị, chúng tôi đang thực hiện 4 hoạt động tìm và thay thế riêng biệt. Công thức trong G5 là:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
trong đó "tìm" là phạm vi được đặt tên E5: E8 và "thay thế" là phạm vi được đặt tên F5: F8. Xem bên dưới để biết thông tin về cách làm cho công thức này dễ đọc hơn.
Lời nói đầu
Không có công thức tích hợp nào để chạy một loạt các thao tác tìm và thay thế trong Excel, vì vậy đây là công thức "khái niệm" để chỉ một cách tiếp cận. Văn bản cần tìm và thay thế được lưu trữ trực tiếp trên trang tính trong một bảng và được truy xuất bằng hàm INDEX. Điều này làm cho giải pháp "động" - bất kỳ giá trị nào trong số này được thay đổi, kết quả cập nhật ngay lập tức. Tất nhiên, không có yêu cầu sử dụng INDEX; bạn có thể mã hóa các giá trị vào công thức nếu muốn.
Giải trình
Về cơ bản, công thức sử dụng hàm SUBSTITUTE để thực hiện mỗi thay thế, với mẫu cơ bản sau:
=SUBSTITUTE(text,find,replace)
"Văn bản" là giá trị đến, "tìm" là văn bản cần tìm và "thay thế" là văn bản cần thay thế bằng. Văn bản cần tìm và thay thế được lưu trữ trong bảng bên phải, trong phạm vi E5: F8, mỗi cặp một hàng. Các giá trị bên trái nằm trong dải ô được đặt tên "find" và các giá trị bên phải nằm trong dải ô được đặt tên "thay thế". Hàm INDEX được sử dụng để truy xuất cả văn bản "tìm" và văn bản "thay thế" như sau:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Vì vậy, để chạy thay thế đầu tiên (tìm kiếm "đỏ", thay thế bằng "hồng"), chúng tôi sử dụng:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Tổng cộng, chúng tôi chạy bốn thay thế riêng biệt và mỗi SUBSTITUTE tiếp theo bắt đầu bằng kết quả từ SUBSTITUTE trước đó:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Ngắt dòng để dễ đọc
Bạn sẽ nhận thấy loại công thức lồng nhau này khá khó đọc. Bằng cách thêm dấu ngắt dòng, chúng ta có thể làm cho công thức dễ đọc và dễ bảo trì hơn nhiều:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
Thanh công thức trong Excel bỏ qua khoảng trắng thừa và ngắt dòng, vì vậy công thức trên có thể được dán trực tiếp vào:
Nhân tiện, có một phím tắt để mở rộng và thu gọn thanh công thức.
Thay thế khác
Có thể thêm nhiều hàng vào bảng để xử lý nhiều cặp tìm / thay thế hơn. Mỗi khi một cặp được thêm vào, công thức cần được cập nhật để đưa vào cặp mới. Điều quan trọng là đảm bảo các phạm vi đã đặt tên (nếu bạn đang sử dụng chúng) được cập nhật để bao gồm các giá trị mới nếu cần. Ngoài ra, bạn có thể sử dụng Bảng Excel thích hợp cho các phạm vi động, thay vì các phạm vi đã đặt tên.
Sử dụng khác
Phương pháp tương tự có thể được sử dụng để làm sạch văn bản bằng cách "loại bỏ" dấu chấm câu và các ký hiệu khác khỏi văn bản với một loạt các thay thế. Ví dụ: công thức trên trang này cho biết cách làm sạch và định dạng lại số điện thoại.
