Học Excel
September 16, 2021408 views0
Tiếp tục các bài viết về cách xử lý chuỗi, Kienit.com sẽ chia sẻ với các bạn một công thức đơn giản khác – đó chính là hàm SUBSTITUTE trong Excel. Mời các bạn tham khảo bài viết để hiểu đây là hàm gì và cách dùng như thế nào.
1/ Hàm SUBSTITUTE là gì?
SUBSTITUTE là hàm dùng để thay thế chuỗi cũ thành một chuỗi mới một cách nhanh chóng.
Trong trường hợp bạn muốn tính toán trên dữ liệu nhưng không muốn tác động trực tiếp thì bạn dùng hàm SUBSTITUTE. Ngoài ra hàm này còn kết hợp với các công thức khác để hỗ trợ trong công việc.
2/ Cách sử dụng hà m SUBSTITUTE
Cú pháp
Cú pháp hàm SUBSTITUTE: =SUBSTITUTE[text, old_text, new_text, [instance_num]]
Trong đó:
Text: Là văn bản mà bạn muốn thay thế các ký tự trong đó.
Old_text: Là văn bản cũ bạn muốn được thay thế.
New_text: Là văn bản mới mà bạn muốn thay thế cho văn bản cũ.
Instance_num: Vị trí văn bản cũ xuất hiện, bạn muốn thay bằng văn bản mới.
Cách dùng
Ví dụ 1: Thay thế “www.” thành ” ” như sau
Cú pháp: =SUBSTITUTE[A5,”www.”,” “]
Cách sử dụng hàm SUBSTITUTE trong Excel đơn giản
Trong chuỗi cũ có “www.” và chuỗi mới là mình để khoảng trống. Kết quả trả về là kienit.com
Ví dụ 2: Thay đổi năm 2020 thành 2021. Do trong chuỗi này chỉ có một 2020 nên khi mình thay thành năm 2021 thì không cần thêm vị trí, hàm SUBSTITUTE vẫn sẽ hiểu.
Cú pháp: =SUBSTITUTE[A6,”2020″,”2021″]
Cách thay thế chuỗi cũ bằng chuỗi mới nhanh chóng
Kết quả trả về là năm 2020 đã được thay mới bằng năm 2021.
Ví dụ 3: Trong ví dụ này mình có thêm một tham số để khác với ví dụ trên, vẫn sẽ thay đổi năm 2020 thành năm 2021.
Cú pháp: =SUBSTITUTE[A7,”2020″,B7]
Dùng hàm SUBSTITUTE để thay thé ký tự cũ bằng tham số có chứa ký tự mới
Hàm SUBSTITUTE hiểu được ô B7 có chứa giá trị 2021 để thêm vào giá trị cũ.
Ví dụ 4: Mình sẽ để trường hợp có 2 vị trí giống nhau và cần thay 1 trong 2 để áp dụng tham số[Instance_num].
Cú pháp: =SUBSTITUTE[A8,”2021″,B8,1]
Thay thế chuỗi cũ thành chuỗi mới có ở vị trí thứ nhất
Như bạn thấy có tới 2 năm 2020 vì vậy khi thêm 1 vào thì hàm SUBSTITUTE sẽ hiểu thay 2021 ở vị trí thứ nhất [từ trái sang]. Còn nếu bạn không để [Instance_num] = 1 thì hàm sẽ thay toàn bộ giá trị cũ 2021 thành giá trị mới 2020.
Tổng kết lại mình có bảng ví dụ sau đây.
Ví dụ cách sử dụng hàm SUBSTITUTE trong Excel đơn giản
Bạn thấy đó việc thay đổi chuỗi cũ thành chuỗi mới bằng cách sử dụng hàm SUBSTITUTE trong Excel cực kỳ đơn giản. Đây vẫn là một trong các công thức cơ bản mà ai cũng nên biết để phối hợp trong công việc. Hy vọng bài viết đã chia sẻ đến bạn những kiến thức hữu ích. Cảm ơn các bạn đã tham khảo.
Bài viết xem thêm:
Hàm TRIM trong Excel, cách xóa khoảng trống dư thừa
Hàm SEARCH trong Excel – Ví dụ cách sử dụng cụ thể
Hàm FIND trong Excel – Ví dụ cách sử dụng cơ bản đến nâng cao
Hàm CONCATENATE có điều kiện – Hàm nối nhiều chuỗi trong Excel
Hàm LEN trong Excel là gì? Ví dụ hướng dẫn cách dùng dễ hiểu
Share
Trang chủ » Hàm xử lý chuỗi » 【Hướng Dẫn】3 Cách sử dụng hàm SUBSTITUTE trong Excel có ví dụ minh họa
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ể.
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: