Hàm substitute trong Excel: Công Thức và Cách sử dụng Hàm substitute trong Excel có ví dụ minh họa cụ thể
Excel là một công cụ hỗ trợ chúng ta rất nhiều hàm tính toán trong các lĩnh vực khác nhau: toán học, thống kê, kế toán, ... và hàm substitute thường được mọi người sử dụng để thay thế văn bản của các ô, dãy, các trang tính hoặc các bảng tính. Trong bài viết sau đây, mình sẽ chia sẻ kiến thức về công thức và cách sử dụng hàm substitute một cách chi tiết và cụ thể nhất kèm theo ví dụ minh họa để bạn có thể hình dung rõ hơn. Thay vì phải tìm từng ô, dãy, cột, văn bản để sửa các văn bản cũ, dữ liệu cũ bằng phương pháp thủ công tốn rất nhiều thời gian thì lựa chọn hàm substitute là một cách thông minh và tối ưu để làm việc đó. Ngoài ra, hàm substitute có thể kết hợp được với nhiều hàm thông dụng khác để hỗ trợ công việc của bạn hiệu quả hơn.
1. Công thức hàm substitute trong Excel
Hình ảnh minh họa của hàm substitute trong Excel
Hàm substitute có công thức như sau:
=SUBSTITUTE (text, old_text, new_text, [instance_num])
hay công thức trên được viết rút gọn như sau:
=SUBSTITUTE(text,a,a1,a2,a3)
Trong đó:
- Text có nghĩa là văn bản sẵn có lúc đầu chúng ta muốn thay thế. Text ở đây có thể là công thức, ký tự, địa chỉ hoặc đường link,...
- a có nghĩa là văn bản trước đó mà chúng ta muốn được thay mới
- a1 có nghĩa là văn bản mới hoàn toàn mà chúng ta muốn thay thế cho văn bản cũ trước đó.
- a2 có nghĩa là vị trí mà văn bản cũ xuất hiện mà chúng ta muốn thay bằng văn bản mới
- a3 có nghĩa là vị trí thực hiện thay thế văn bản cũ trước đó (a1)
2. Cách sử dụng Hàm substitute trong Excel
Hàm SUBSTITUTE trong Excel giúp chúng ta thay thế chuỗi văn bản cũ thành chuỗi văn bản mới một cách nhanh chóng, đơn giản và dễ dàng. Ngoài ra hàm substitute là hàm được ứng dụng khá phổ biến trong xử lý dữ liệu như xóa khoảng trống, thay thế dữ liệu sai bằng dữ liệu đúng.
3. Các ví dụ minh họa về hàm substitute
3.1. hàm substitute được sử dụng để thay thế văn bản cũ thành văn bản mới
Ví dụ 1: Cho bảng dữ liệu dưới đây, hãy dùng hàm substitute để thay thế văn bản cũ thành văn bản mới
Yêu cầu thay www.tienganh thành tienganh
Chúng ta thực hiện công thức tại ô C4 như sau:
=SUBSTITUTE(B4;”www.”;””)
sau đó nhấn Enter và kết quả chúng ta nhận được là:
Yêu cầu hãy thay Học Excel thành Excel
Chúng ta sử dụng công thức tại ô C5 như sau:
=SUBSTITUTE(B5;”Học”;””)
sau đó nhấn Enter và kết quả sẽ hiển thị như sau:
Yêu cầu hãy thay Excel thành Word
Chúng ta có công thức tại ô C6 như sau:
=SUBSTITUTE(B6;”Excel”;”Word”)
Ví dụ 2: Giả sử tất cả các công thức dưới đây thay thế “1” bằng “2” trong ô A2, nhưng trả lại các kết quả khác nhau tùy thuộc vào số bạn cung cấp trong đối số cuối cùng:
Chúng ta thực hiện các bước như dưới đây:
Thay thế sự xuất hiện đầu tiên của “1” bằng “2” bằng công thức
= SUBSTITUTE (A2, “1”, “2”, 1)
Thay thế lần xuất hiện thứ hai của “1” bằng “2” bằng công thức
= SUBSTITUTE (A2, “1”, “2”, 2)
Thay thế tất cả các lần xuất hiện của “1” bằng “2” bằng công thức
= SUBSTITUTE (A2, “1”, “2”)
Chú ý rằng: trong ví dụ này công thức sau đây sẽ thay thế tất cả các trường hợp chữ hoa “X” bằng chữ “Y” trong ô A2, nhưng nó sẽ không thay thế bất kỳ trường hợp nào của chữ thường “x”.
3.2. Lồng các hàm SUBSTITUTE để thay thế các ký tự khác nhau bằng một công thức
Chúng ta có thể lồng các SUBSTITUTE trong một công thức để thực hiện một số thay thế cùng lúc, tức là thay thế nhiều ký tự bằng một công thức.
Ví dụ:Giả sử chúng ta có một chuỗi văn bản như “PR1, ML1, T1” trong ô A2, trong đó “PR” là viết tắt của “Project”, ML “viết tắt của” Milestone “và” T “có nghĩa là” Task “. Chúng ta muốn thay thế 3 ba mã này với tên đầy đủ. Để thực hiện được điều này, Chúng ta có thể viết 3 công thức SUBSTITUTE khác nhau như sau:
= SUBSTITUTE (A2, “PR”, “Project “)
= SUBSTITUTE (A2, “ML”, “Milestone “)
= SUBSTITUTE (A2, “T”, “Task “)
Và sau đó chúng ta lồng với nhau trong 1 công thức như dưới đây
= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A2, “PR”, “Project “), “ML”, “Milestone “), “T”, “Task “)
4. Một số lưu ý khi sử dụng hàm substitute trong Excel
- Hàm SUBSTITUTE trong Excel có phân biệt chữ hoa và chữ thường
- Hàm SUBSTITUTE có thể thay thế một hoặc nhiều vị trí của ký tự hoặc chuỗi văn bản nhất định, do vậy, nếu như các bạn chỉ biết đoạn cần thay thế thì hãy sử dụng hàm SUBSTITUTE.
- Hàm SUBSTITUTE có cho phép thêm một tham số tùy chọn (instance_num) xác định vị trí thay thế của old_text thành new_text và chúng ta nên thêm một dấu cách vào cuối mỗi đối số new_text.
Qua bài viết trên, chúng ta đã có thể hiểu rõ cụ thể về hàm về công thức của hàm, cách sử dụng và một số lưu ý cần biết khi sử dụng hàm substitute trong Excel. Trên đây là hướng dẫn chi tiết cách sử dụng hàm substitute cơ bản nhất và một số ví dụ cụ thể về sự lồng hàm của hàm substitute trong Excel. Hi vọng các bạn đã hiểu rõ hơn về hàm substitute và có thể ứng dụng nó tốt nhất trong công việc của mình. Chúc các bạn thành công!