Trong bài viết này, Tin Học Văn Phòng sẽ hướng dẫn các bạn sử dụng hàm SUBSTITUTE trong Excel để thay thế ký tự trong chuỗi văn bản. Không chỉ tìm hiểu về cú pháp cơ bản, các bạn còn được học cách sử dụng thông qua các ví dụ cụ thể.
Nội dung bài viết
Hàm SUBSTITUTE trong Excel dùng để làm gì?
Hàm SUBSTITUTE được giới thiệu từ phiên bản Microsoft Excel 2007 và thuộc nhóm hàm xử lý chuỗi ký tự.
Hàm SUBSTITUTE được sử dụng để thay thế một hoặc nhiều chuỗi văn bản bằng một chuỗi văn bản khác. Hàm này rất hữu ích khi chúng ta muốn thay thế văn bản cũ trong một chuỗi bằng một chuỗi mới.
Cú pháp hàm SUBSTITUTE trong Excel
Hàn SUBSTITUTE trong Excel có 4 tham số, ba tham số đầu tiên của hàm SUBSTITUTE là bắt buộc và tham số cuối cùng là tùy chọn.
Trong đó:
=SUBSTITUTE(text, old_text, new_text, [instance_num])- text (bắt buộc): là chuỗi văn bản gốc mà bạn muốn thay thế các ký tự. Trong công thức này, text có thể là một chuỗi văn bản trực tiếp, tham chiếu đến một ô hoặc kết quả của một công thức khác.
- old_text (bắt buộc): là chuỗi ký tự mà bạn muốn thay thế.
- new_text (bắt buộc): là chuỗi ký tự mới sẽ thay thế old_text.
- instance_num (tùy chọn): tham số này là tùy chọn, nếu không truyền tham số này vào, tất cả chuỗi old_text xuất hiện trong chuỗi text sẽ được thay thế. Tham số này chỉ định vị trí mà chuỗi ký tự old_text sẽ được thay thế bởi new_text. Để hiểu rõ hơn tham số này thì các bạn hãy xem ví dụ bên dưới.
Ví dụ:
Các công thức bên dưới đều thay thế chữ “2021” thành “2020” trong ô A2. Tuy nhiên, kết quả trả về sẽ khác nhau tùy theo tham số instance_num.
- Khi không truyền tham số instance_num vào: Mặc định thì hàm SUBSTITUTE sẽ thay thế tất cả chữ “2021” thành “2020” .
- Khi truyền tham số instance_num = 1 vào: Hàm SUBSTITUTE chỉ thay thế 1 chữ “2021” được tìm thấy đầu tiên thành “2020”. Chữ “2021” còn lại sẽ được giữ nguyên như cũ.
- Khi truyền tham số instance_num = 2 vào: Hàm SUBSTITUTE sẽ thay thế chữ “2021” xuất hiện lần thứ 2 trong chuỗi văn bản thành “2020”, chữ “2021” xuất hiện lần đầu sẽ được giữ nguyên. Tương tự, khi muốn thay thế chữ ở vị trí số mấy thì bạn nhập số đó vào đối số cuối cùng.
Một số lưu ý khi sử dụng hàm SUBSTITUTE trong Excel
Hàm SUBSTITUTE trong Excel phân biệt chữ hoa chữ thường. Điều này có nghĩa là chữ “X” sẽ khác “x”.
Ví dụ: công thức bên dưới sẽ thay thế tất cả các chữ thường “x” bằng “y” trong ô A2, và sẽ không thay thế bất kỳ chữ hoa “X” nào.
Các ví dụ về cách sử dụng hàm SUBSTITUTE trong Excel
Để hiểu rõ hơn về cách sử dụng của hàm SUBSTITUTE trong Excel, chúng ta hãy xem xét một vài ví dụ sau đây:
Dùng hàm SUBSTITUTE để xóa dấu phẩy trong Excel
Để loại bỏ dấu phẩy bằng cách sử dụng hàm SUBSTITUTE trong Excel, bạn có thể thay thế dấu phẩy bằng một khoảng trống hoặc bất kỳ ký tự nào khác.
Giả sử bạn có một tập dữ liệu như hình dưới đây, nếu muốn xóa tất cả các dấu phẩy trong ô A2, các bạn dùng công thức sau:
=SUBSTITUTE(A2,",","")
Trong đó:
- Đối số đầu tiên là ô A2, chứa văn bản cần thay thế.
- Đối số thứ 2 là dấu phẩy, là ký tự cần thay thế.
- Đối số thứ 3 là chuỗi rỗng, dùng để thay thế dấu phẩy.
Công thức trên sẽ thay thế tất cả dấu phẩy trong ô A2, trong trường hợp bạn muốn thay thế chỉ dấu phẩy đầu tiên thì dùng công thức bên dưới.
=SUBSTITUTE(A2,",","",1)Xóa số 0 đầu dòng trong Excel
Giả sử bạn có một tập dữ liệu được hiển thị như bên dưới, và bạn muốn xóa tất cả các số 0 ở đầu mỗi ô, các bạn dùng công thức sau:
=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)
Trong đó:
- Hàm SUBSTITUTE của công thức sẽ thay thế số 0 bằng một khoảng trống. Vì vậy, đối với giá trị 023LCH, hàm sẽ cho kết quả là 23LCH.
- Hàm LEFT sau đó sẽ trích xuất các ký tự ngoài cùng bên trái của chuỗi kết quả trả về từ hàm SUBSTITUTE, trong trường hợp này sẽ là 2.
- Sau đó, hàm FIND sẽ tìm ký tự ngoài cùng bên trái được cung cấp bởi công thức LEFT và trả về vị trí của nó. Trong ví dụ này, đối với giá trị ở ô A2 là 023LCH, nó sẽ cho kết quả là 2 (là vị trí của số 2 trong chuỗi văn bản gốc).
- 1 được thêm vào kết quả của hàm FIND, để đảm bảo sẽ nhận được toàn bộ chuỗi văn bản được trích xuất (ngoại trừ các số 0 ở đầu).
- Kết quả của hàm FIND sau đó được trừ đi từ kết quả của hàm LEN (được sử dụng để trả về độ dài của toàn bộ chuỗi văn bản).
- Giá trị cuối cùng sau đó được sử dụng làm đối số cho hàm RIGHT để trích xuất toàn bộ chuỗi văn bản (ngoại trừ các số 0 ở đầu).
Các bạn lưu ý, để đảm bảo công thức trên hoạt động chính xác trong trường hợp có khoảng trắng ở đầu hoặc ở cuối văn bản trong các ô, tốt nhất bạn nên sử dụng hàm TRIM với mọi ô được sử dụng trong công thức.
Cuối cùng, chúng ta sẽ có công thức như sau:
=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(LEFT(SUBSTITUTE(TRIM(A2),"0",""),1),TRIM(A2))+1)Sử dụng hàm SUBSTITUTE lồng nhau
Hàm SUBSTITUTE trong Excel không thể thay thế nhiều chuỗi văn bản khác nhau cùng một lúc. Tuy nhiên, hàm SUBSTITUTE có thể được sử dụng lồng vào nhau để thực hiện việc trên.
Ví dụ: với văn bản “tôi có một con chó và một con mèo” trong ô A2, công thức bên dưới sẽ loại thay thế chữ “chó” thành “bò”, chữ “mèo” thành “trâu”:
=SUBSTITUTE(SUBSTITUTE(A2,"chó","bò"),"mèo","trâu")
Trong đó:
- Trước tiên, hàm SUBSTITUTE bên trong sẽ thay thế chữ “chó” trong ô A2 thành chữ “bò”, kết quả thành “tôi có một con bò và một con mèo”.
- Sau đó, hàm SUBSTITUTE bên ngoài sẽ nhận kết quả từ hàm SUBSTITUTE bên trong, rồi thay chữ “mèo” thành “trâu”.
Bên trên là hướng dẫn cách sử dụng hàm SUBSTITUTE trong Excel và một số ví dụ về cách sử dụng hàm SUBSTITUTE. Hy vọng bài viết sẽ giúp ích cho các bạn.
Chúc các bạn thành công!
Xem thêm:
- Hướng dẫn sử dụng hàm TEXT trong Excel
- Hướng dẫn sử dụng hàm REPLACE trong Excel
- Hướng dẫn sử dụng hàm TRIM trong Excel
- Hướng dẫn sử dụng hàm LEN trong Excel
- Hướng dẫn sử dụng hàm MID trong Excel
- Hướng dẫn sử dụng hàm LEFT trong Excel
- Hướng dẫn sử dụng hàm RIGHT trong Excel
- Hướng dẫn sử dụng hàm SEARCH trong Excel
- Hướng dẫn sử dụng hàm FIND trong Excel