Hàm SUBSTITUTE là một hàm văn bản trong Excel, cho phép bạn thay thế một hoặc nhiều ký tự hoặc đoạn văn bản cũ bằng ký tự hoặc đoạn văn bản mới. Hàm SUBSTITUTE có thể được sử dụng để cải thiện định dạng, loại bỏ các ký tự không mong muốn, hoặc thay đổi nội dung của các ô trong Excel. Trong bài viết này, chúng tôi sẽ giới thiệu cho bạn cách sử dụng hàm SUBSTITUTE trong Excel, công dụng của hàm SUBSTITUTE và một số ứng dụng thực tế của hàm này.
Công dụng của hàm substitute trong excel
Công dụng của hàm SUBSTITUTE là giúp bạn thay đổi nội dung của các ô trong Excel một cách nhanh chóng và chính xác. Bạn có thể sử dụng hàm này để:
-
Thay thế ký tự ở vị trí đầu tiên, cuối cùng hoặc bất kỳ trong chuỗi văn bản.
-
Thay thế tất cả các ký tự cũ bằng ký tự mới trong chuỗi văn bản.
-
Thay thế cả đoạn văn bản cũ bằng đoạn văn bản mới trong chuỗi văn bản.
-
Xóa các ký tự không mong muốn như dấu phẩy, khoảng trắng, số 0 hoặc các ký tự đặc biệt khác trong chuỗi văn bản.
-
Sử dụng hàm substitute excel lồng nhau để thực hiện nhiều thao tác thay thế cùng một lúc.
Hàm SUBSTITUTE giúp thay đổi nội dung của các ô trong Excel nhanh chóng
Công thức substitute trong excel
Cấu trúc hàm substitute trong excel như sau:
=SUBSTITUTE(text;old_text;new_text;[instance_num])
Trong đó:
-
text: là ô hoặc chuỗi văn bản cần thay thế.
-
old_text: là ký tự hoặc đoạn văn bản cũ cần được thay thế.
-
new_text: là ký tự hoặc đoạn văn bản mới sẽ được thêm vào thay cho old_text.
-
instance_num: là số nguyên dương chỉ ra vị trí của old_text cần được thay thế. Nếu bỏ qua tham số này, hàm SUBSTITUTE sẽ thay thế tất cả các old_text trong text.
Công thức hàm SUBSTITUTE
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
Ứng dụng của hàm SUBSTITUTE trong Excel
Hàm SUBSTITUTE có nhiều ứng dụng trong Excel, tùy thuộc vào nhu cầu và mục đích của bạn. Dưới đây là một số ứng dụng của hàm SUBSTITUTE trong Excel:
Thay thế một phần của văn bản trong một ô
Ví dụ, nếu bạn có một chuỗi "Hello World" trong ô A1 và muốn thay thế "World" bằng "Excel", bạn có thể sử dụng công thức sau:
Thay thế một phần của văn bản trong một ô
Loại bỏ các ký tự không mong muốn
Đôi khi, bạn có thể muốn loại bỏ các ký tự như dấu cách, dấu chấm hoặc các ký tự khác từ một chuỗi. Ví dụ, để loại bỏ dấu cách trong một chuỗi, bạn có thể sử dụng:
Loại bỏ các ký tự không mong muốn
Thay thế các dấu phân cách
Hàm SUBSTITUTE cũng có thể được sử dụng để thay thế dấu phân cách, ví dụ, để chuyển đổi số tiền từ định dạng chung sang định dạng số thập phân:
Hàm SUBSTITUTE cũng có thể được sử dụng để thay thế dấu phân cách
Thay thế nhiều trường hợp cùng một lúc
Hàm SUBSTITUTE cũng có thể thay thế nhiều trường hợp cùng một lúc. Ví dụ, để thay thế cả "World" và "Hello" thành "Excel":
Hàm SUBSTITUTE cũng có thể thay thế nhiều trường hợp cùng một lúc
Thay thế phân cách trong số điện thoại hoặc mã vùng
Đây là một ví dụ cụ thể khi bạn muốn loại bỏ hoặc thay thế dấu cách, dấu ngoặc đơn hoặc dấu gạch ngang từ số điện thoại.
Thay thế phân cách trong số điện thoại hoặc mã vùng
6 cách dùng hàm substitute trong excel
Để giúp bạn hiểu hơn về hàm SUBSTITUTE trong Excel, mời bạn theo dõi 6 ví dụ dưới đây:
Sử dụng hàm SUBSTITUTE để thay thế ký tự ở vị trí đầu tiên
Bài tập hàm hàm substitute trong excel: Cho bảng ví dụ 1:
Cho bảng ví dụ
Yêu cầu: Dùng hàm SUBSTITUTE để thay thế ký tự đầu trong chuỗi văn bản ở bảng số 1.
Cách thực hiện:
Bước 1: Ở hàm D3, bạn nhập công thức =SUBSTITUTE(A3;B3;C3;1).
Nhập công thức hàm SUBSTITUTE
Bước 2: Nhấn Enter, Excel sẽ trả về kết quả là:
Mã sản phẩm A122, tồn A123.
Kết quả sau nhập công thức
Bước 3: Copy công thức xuống bên dưới sẽ thu được kết quả như sau:
Copy công thức xuống bên dưới
Sử dụng hàm SUBSTITUTE để thay thế tất cả các ký tự cũ
Ví dụ hàm hàm substitute trong excel: Cho bảng ví dụ 2:
Cho bảng ví dụ
Yêu cầu: Dùng hàm SUBSTITUTE để thay thế tất cả các ký tự cũ.
Cách thực hiện hàm substitute trong excel:
Bước 1: Đặt chuột vào ô D20, nhập công thức dưới đây:
=SUBSTITUTE(A20;B20;C20).
Nhập công thức vào D20
Bước 2: Nhấn enter, Excel sẽ trả về kết quả là:
Mã sản phẩm A122, tồn A122.
Kết quả nhập công thức
Bước 3: Kéo thả chuột xuống bên dưới, bảng kết quả cuối cùng bạn thu được là:
Kết quả sau nhập công thức
Sử dụng hàm SUBSTITUTE để thay thế cả đoạn văn bản cũ thành đoạn văn bản mới
Ví dụ hàm hàm substitute trong excel: Cho bảng ví dụ cho bảng ví dụ 3:
Cho bảng ví dụ
Yêu cầu: Dùng hàm SUBSTITUTE để thay thế cả đoạn văn bản cũ thành đoạn văn bản mới.
Cách thực hiện:
Bước 1: Nhập công thức hàm substitute trong excel sau đây vào ô D33:
=SUBSTITUTE(A33;B33;C33) hoặc bạn có thể nhập công thức =SUBSTITUTE(A33;”Bầu trời ngày hôm ấy”;”Thời tiết ngày hôm ấy”).
Nhập công thức tính
Bước 2: Nhấn Enter, Excel trả về kết quả là: Thời tiết ngày hôm ấy thật đẹp.
Kết quả sau khi nhập công thức tính
Bước 3: Kéo thả công thức xuống phía dưới sẽ thu được bảng kết quả là:
Kéo thả công thức xuống phía dưới
Dùng hàm SUBSTITUTE để xóa dấu phẩy trong Excel
Bài tập hàm hàm substitute trong excel: Cho bảng ví dụ 4:
Cho bảng ví dụ
Yêu cầu: Dùng hàm SUBSTITUTE để loại bỏ toàn bộ dấu phẩy trong chuỗi văn bản.
Cách thực hiện hàm substitute trong excel:
Bước 1: Ở ô B44, bạn nhập công thức:
=SUBSTITUTE(A44;",";"").
Nhập công thức SUBSTITUTE vào B44
Bước 2: Nhấn Enter, kết quả trả về là một câu văn không còn dấu phẩy như hình dưới đây:
Nhấn Enter
Bước 3: Sao chép công thức xuống phía dưới, bảng Excel bạn nhận được như sau:
Sao chép công thức xuống phía dưới
Dùng hàm SUBSTITUTE xóa số 0 đầu dòng trong Excel
Bài tập hàm hàm substitute trong excel: Cho bảng ví dụ 5:
Cho bảng ví dụ
Yêu cầu: Dùng hàm SUBSTITUTE để bỏ số 0 ở đầu những mã sản phẩm có trong bảng 5.
Cách thực hiện:
Bước 1: Nhập công thức thay thế dưới đây vào ô B57:
=RIGHT(A57;LEN(A57)-FIND(LEFT(SUBSTITUTE(A57;"0";"");1);A57)+1).
Nhập công thức vào B57
Bước 2: Nhấn enter, kết quả bạn nhận được là 54TA.
Kết quả sau khi nhập công thức
Bước 3: Copy công thức xuống bên dưới sẽ thu được bảng kết quả sau:
Copy công thức xuống bên dưới
Sử dụng hàm SUBSTITUTE lồng nhau
Bài tập hàm hàm substitute trong excel: Cho bảng ví dụ 6:
Cho bảng ví dụ
Yêu cầu: Dùng hàm SUBSTITUTE lồng nhau để để thay thế toàn bộ văn bản cũ 1 thành văn bản mới 2.
Cách thực hiện:
- Bước 1: Nhập công thức hàm substitute trong excel dưới đây vào ô F69:
=SUBSTITUTE(SUBSTITUTE(A69;B69;C69);D69;E69).
Nhập công thức SUBSTITUTE
Bước 2: Nhấn enter, sẽ thu được kết quả là:
Bầu trời tháng 7 đẹp và nên thơ.
Kết quả sau khi nhập công thức
Bước 3: Copy công thức xuống bên dưới, bạn sẽ nhận được bảng kết quả như sau:
Copy công thức xuống bên dưới
Một số lưu ý khi dùng hàm SUBSTITUTE trong Excel
Khi sử dụng hàm SUBSTITUTE trong Excel, bạn cần lưu ý một số điểm sau:
-
Hàm SUBSTITUTE phân biệt chữ hoa chữ thường. Nếu bạn muốn thay thế một ký tự hoặc đoạn văn bản không phân biệt chữ hoa chữ thường, bạn có thể sử dụng hàm UPPER hoặc LOWER để chuyển đổi text, old_text, và new_text về cùng một dạng trước khi sử dụng hàm SUBSTITUTE.
-
Lệnh SUBSTITUTE không thể thay thế các ký tự đại diện như ?, *, hoặc ~. Nếu bạn muốn thay thế các ký tự đại diện, bạn có thể sử dụng hàm REPLACE hoặc hàm REPT.
-
Hàm SUBSTITUTE có thể trả về lỗi #VALUE! nếu text, old_text, hoặc new_text không phải là chuỗi văn bản, hoặc nếu instance_num không phải là số nguyên dương.
-
Hàm SUBSTITUTE có thể trả về kết quả không mong muốn nếu text, old_text, hoặc new_text chứa các ký tự đặc biệt như dấu nháy kép, dấu bằng, hoặc dấu phẩy. Bạn cần đặt các ký tự này trong dấu nháy kép để Excel nhận diện chúng là một phần của chuỗi văn bản.
Một số lưu ý khi dùng hàm SUBSTITUTE trong Excel
Tổng kết
Hàm substitute trong excel giúp bạn thay thế một hoặc nhiều ký tự hoặc đoạn văn bản cũ bằng ký tự hoặc đoạn văn bản mới. Bạn có thể sử dụng hàm này để cải thiện định dạng, loại bỏ các ký tự không mong muốn hoặc thay đổi nội dung của các ô trong Excel. Tuy nhiên, bạn cần lưu ý một số điểm khi sử dụng hàm này, như phân biệt chữ hoa chữ thường, không thể thay thế các ký tự đại diện, và cẩn thận với các ký tự đặc biệt. Để bài học Excel được hiệu quả cao hơn, các bạn nên thực hành thường xuyên hơn nhé. Cảm ơn các bạn đã đọc hết bài viết này nhé!
>>> Xem thêm: Gợi ý 5 thủ thuật bỏ dấu tiếng Việt trong excel mới nhất
>>> Xem thêm: Cách bỏ dấu chấm trong excel mà không phải ai cũng biết
>>> Xem thêm: 9 cách xóa khoảng trắng trong Excel tự động cho mọi phiên bản cực đơn
