Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm SUBSTITUTE trong Excel trong Excel để tìm và thay thế văn bản.
Tổng quan về hàm SUBSTITUTE
Hàm SUBSTITUTE Tìm và thay thế văn bản hiện có bằng một chuỗi văn bản mới. Trường hợp nhạy cảm.
Để sử dụng Hàm SUBSTITUTE Trang tính Excel, hãy chọn một ô và nhập:
(Chú ý cách các đầu vào công thức xuất hiện)
Hàm SUBSTITUTE Cú pháp và đầu vào:
| 1 | = SUBSTITUTE (text, old_text, new_text, instance_num) |
chữ - Chuỗi văn bản gốc.
old_text - Chuỗi văn bản mà bạn muốn tìm và thay thế.
new_test - Văn bản thay thế.
instance_num - Không bắt buộc. Số phiên bản để thay thế. Nếu trống, tất cả các trường hợp được thay thế.
SUBSTITUTE là gì?
Hàm SUBSTITUTE trong Excel được sử dụng để thay thế một chuỗi ký tự trong chuỗi văn bản bằng một chuỗi văn bản được chỉ định khác.
Chúng ta hãy xem ví dụ sau đây thay thế chuỗi "nâu nhanh" bằng "trắng chậm".
| 1 | = SUBSTITUTE (C2, C3, C4) |
SUBSTITUTE tìm trong chuỗi ở C2, tìm thấy "màu nâu nhanh" và thay thế bằng "màu trắng chậm".
Cách sử dụng SUBSTITUTE
Hàm SUBSTITUTE nhận 3 đối số bắt buộc và 1 đối số tùy chọn:
- Văn bản: Văn bản bạn muốn thay thế các ký tự
- Old_text: Văn bản bạn muốn thay thế
- New_text: Văn bản bạn muốn thay thế Old_text bằng
- Instance_num (tùy chọn): Chỉ định sự xuất hiện của Old_text mà bạn muốn thay thế bằng New_text. Theo mặc định, tất cả các lần xuất hiện của Old_text sẽ được thay thế nếu một trường hợp không được chỉ định.
Lồng SUBSTITUTE
Điều gì sẽ xảy ra nếu chúng ta phải thay thế hai phần của cùng một chuỗi? Hãy để chúng tôi xem chúng tôi sẽ kết hợp hai hàm SUBSTITUTE như thế nào.
| 1 | = SUBSTITUTE (SUBSTITUTE (C2, C3, C4), C5, C6) |
Lưu ý cách kết quả của SUBSTITUTE (C2, C3, C4) được sử dụng làm đối số đầu tiên cho SUBSTITUTE thứ hai. Nếu chúng ta chia nhỏ công thức:
= SUBSTITUTE (C2, C3, C4) đánh giá là “ trắng chậm cáo nhảy qua con chó lười biếng ”.
Sau đó, SUBSTITUTE (“Con cáo trắng chậm chạp nhảy qua con chó lười biếng”, C5, C6) cho kết quả là “Con cáo trắng chậm chạp con sư tử nhảy qua con chó lười biếng ”.
Sự khác biệt giữa SUBSTITUTE và REPLACE
Một chức năng tương tự như SUBSTITUTE là REPLACE. Trong hầu hết các trường hợp, chúng có thể được sử dụng thay thế cho nhau, tuy nhiên, quy tắc ngón tay cái sau đây sẽ được áp dụng.
Hàm SUBSTITUTE sẽ được sử dụng khi chuỗi văn bản được thay thế đã được biết đến, hoặc toàn bộ từ hoặc các từ đang được thay thế, giống như ví dụ về con cáo của chúng tôi đã sử dụng trước đó.
| 1 | = SUBSTITUTE (C2, C3, C4) |
Hàm REPLACE nên được sử dụng khi vị trí của các ký tự văn bản được thay thế trong chuỗi được biêt đên, hoặc khi một phần của từ hoặc chuỗi đang được thay thế. Ví dụ: loại bỏ dấu gạch nối khỏi một chuỗi số.
Hàm REPLACE cũng có thể được lồng như SUBSTITUTE tuy nhiên, điều quan trọng cần lưu ý là vị trí tương đối của ký tự bắt đầu có thể thay đổi dựa trên độ dài của chuỗi được thay thế trong bước đầu tiên của công thức lồng nhau.
Ví dụ: thay thế "nâu nhanh" bằng "trắng chậm" là thay thế 11 ký tự bằng 10 ký tự. Do đó, nếu được lồng vào nhau, ký tự bắt đầu cho REPLACE thứ hai sẽ lệch 1 ký tự so với các vị trí ký tự ban đầu.
Mẹo SUBSTITUTE
- SUBSTITUTE có thể được sử dụng trên các chuỗi con:
- Hãy nhớ rằng bạn cần chỉ định instance_num nếu bạn đang cố gắng thay thế một tập hợp các ký tự lặp lại trong chuỗi:
Lưu ý rằng 3030 không chính xác vì cả "20" và "20" trong "2020" đã được thay thế bằng "30".
Quan tâm đến các chức năng Văn bản khác?
Xem các bài viết khác của chúng tôi để biết thêm về REPLACE hoặc cách sử dụng các hàm văn bản khác trong Excel như LEFT, MID và RIGHT.
SUBSTITUTE trong Google Trang tính
Hàm SUBSTITUTE hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
Ghi chú bổ sung
Hàm SUBSTITUTE phân biệt chữ hoa chữ thường !. Bạn có thể sử dụng Hàm LOWER hoặc UPPER để chuyển đổi chuỗi văn bản của bạn thành một trường hợp nhất quán trước khi sử dụng Hàm SUBSTITUTE. Sau đó, nếu muốn, bạn có thể sử dụng Hàm PROPER để viết hoa chữ cái đầu tiên của mỗi từ.
Ngoài ra, bạn có thể sử dụng Hàm TÌM KIẾM và THAY THẾ để mô phỏng SUBSTITUTE không phân biệt chữ hoa chữ thường. Sử dụng Hàm TÌM KIẾM để tìm vị trí bắt đầu của chuỗi văn bản. Hàm TÌM KIẾM không phân biệt chữ hoa chữ thường. Sau đó sử dụng Hàm REPLACE để thay thế văn bản. Bạn sẽ cần sử dụng kết quả từ Hàm TÌM KIẾM làm đầu vào start_num trong REPLACE. Bạn cũng sẽ cần xác định số ký tự để thay thế (num_chars). Bạn có thể đếm chúng theo cách thủ công hoặc sử dụng Hàm LEN để đếm số ký tự. Thí dụ:
Thay thế - Xóa dấu gạch nối
Một câu hỏi phổ biến là làm thế nào để loại bỏ dấu gạch nối khỏi văn bản. Bí quyết là thay thế dấu gạch ngang bằng dấu gạch nối.
Đối với ví dụ này, giả sử ô A1 có văn bản với dấu gạch nối trong đó. Đây là cách xóa chúng
1. Trong ô trợ giúp trống, hãy nhập loại = SUBSTITUTE (
2. Nhấp vào ô bạn muốn thay thế các ký tự trong hoặc A1 (điều này tự động điền vào công thức của bạn)
3. Nhập, (dấu phẩy)
4. Nhập “-”
5. Nhập, (dấu phẩy)
6. Nhập “” và nhấn enter
Kết quả cuối cùng của bạn trong ô trợ giúp của bạn sẽ giống như sau:
= SUBSTITUTE (A1, ”-“, ””)
3 Ví dụ thay thế khác:
Nhìn vào ô B3, chúng tôi muốn thay thế từ "ốm" bằng "lớn". Chúng ta có thể sử dụng biểu thức sau:
| 1 | = SUBSTITUTE (B3, "ốm", "lớn", 1) |
Nếu văn bản mà chúng ta muốn thay thế xảy ra nhiều lần thì chúng ta cần chỉ định trường hợp mà chúng ta muốn nói. Trong ví dụ thứ hai, chúng ta có hai lần xuất hiện của từ "cũ". Vì vậy, nếu chúng ta muốn thay đổi trường hợp thứ hai thành từ "xám" thì chúng ta phải sử dụng:
| 1 | = SUBSTITUTE (B4, "cũ", "xám", 2) |
Lưu ý rằng nếu sự kiện xảy ra không được chỉ định thì tất cả các trường hợp đều được thay thế. Vì thế:
| 1 | = SUBSTITUTE (B4, "cũ", "xám") |
Sẽ loại bỏ tất cả các trường hợp của từ "cũ" và thay thế chúng bằng từ "xám". Lưu ý rằng nếu không tìm thấy văn bản cũ thì chuỗi không thay đổi. Vì vậy, hãy nhìn vào ví dụ cuối cùng:
| 1 | = SUBSTITUTE (B5, "đen", "xám", 1) |
Có nghĩa là chúng tôi thử và thay thế từ “đen” bằng từ “xám”. Tuy nhiên, vì "màu đen" không xảy ra, chuỗi ban đầu vẫn còn levitra được vận chuyển ở các bang thống nhất không thay đổi:
Để tải xuống tệp .XLSX từ bài viết này, hãy nhấp vào đây
Ví dụ về SUBSTITUTE trong VBA
Bạn cũng có thể sử dụng hàm SUBSTITUTE trong VBA. Kiểu:ứng dụng.worksheet Chức năng.subsults (văn bản, văn bản cũ, văn bản mới, thể hiện_num)
Đối với các đối số của hàm (văn bản, v.v.), bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
Khi đoạn mã sau được thực thi
| 1 | Range ("A1") = WorksheetFunction.Subsrupt (Phạm vi ("A1"), "using", "", 1) |
Nội dung của ô A1 sẽ trở thành:
Vì vậy, từ “using” đã được thay thế bằng một chuỗi trống. Tham số cuối cùng (1) cho biết phiên bản nào của văn bản nên được thay thế, trong trường hợp văn bản hiện diện nhiều hơn một lần. Vì vậy, chẳng hạn nếu chúng ta thực hiện câu lệnh sau
| 1 | Range ("A1") = WorksheetFunction.Subsrupt (Phạm vi ("A1"), "the", "an", 1) |
Ô A1 sẽ trông như thế này
Lưu ý rằng lần xuất hiện đầu tiên của “the” được thay thế bằng “an”, trong khi “the” thứ hai không bị ảnh hưởng. Nếu chúng ta bỏ qua tham số cuối cùng, thì cả hai từ "the" sẽ được thay thế
Quay lại danh sách tất cả các hàm trong Excel
