Cú pháp và cách sử dụng hàm SUBSTITUTE trong excel đơn giản!

Hàm SUBSTITUTE là hàm gì? Cú pháp và cách sử dụng hàm SUBSTITUTE trong excel đơn giản kèm ví dụ minh họa

Khi thao tác trên Excel ngoài một số hàm giúp xử lý dữ liệu để văn bản chuyên nghiệp hơn như hàm UPPER, hàm PROPER,... thì hàm SUBSTITUTE cũng sẽ hỗ trợ người dùng trong công việc này. Đây là loại hàm giúp 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, thuận tiện. Nếu bạn chưa biết cú pháp và cách sử dụng hàm SUBSTITUTE hãy cùng Mega tìm hiểu ngay trong bài viết dưới đây.

• Hàm SUBSTITUTE là hàm gì? Cú pháp và ứng dụng của hàm?
• Một số lưu ý khi dùng hàm Substitute trong Excel
• Hướng dẫn sử dụng hàm Substitute qua các ví dụ cụ thể

 Cú pháp và cách sử dụng hàm SUBSTITUTE trong excel

I. Hàm SUBSTITUTE là hàm gì? Tương thích với hệ điều hành nào?

Hàm SUBSTITUTE là hàm giúp 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 và dễ dàng. Đây là hàm thuộc nhóm hàm xử lý chuỗi ký tự.

Hàm SUBSTITUTE sẽ tương tích với hệ điều hành Windows 10 trên phiên bản Excel 2013. Bạn cũng có thể thực hiện trên Excel 2010, 2016, 2019.

II. Công dụng của hàm SUBSTITUTE

Hàm SUBSTITUTE trong excel thường được sử dụng khi người dùng muốn thay thể đoạn văn bản cũ bằng đoạn văn bản mới. Bạn cũng có thể kết hợp hàm SUBSTITUTE với nhiều hàm thông dụng khác để hỗ trợ công việc và thao tác dễ dàng trên máy tính văn phòng.

Hàm SUBTSTITUTE giúp bạn tạo bảng tính có nhiều dữ liệu và dễ dàng thay thế văn bản cũ đó thành một văn bản mới, việc thay thế thủ công sẽ làm mất nhiều thời gian, thậm chí là dữ liệu sẽ không chính xác.

+ Hàm SUBSTITUTE được ứng dụng để thay đổi hay rút ngắn cột họ tên học sinh, tên nhân viên,...

+ Ngoài ra hàm SUBSTITUTE còn được sử dụng để lập các bản báo cáo theo tuần, tháng, năm,.. để rút ngắn thời gian lập nhiều bản báo cáo khác nhau. Thay vì việc bạn thường xuyên phải cập nhật liên tục các báo cáo mới bằng cách làm thông thường thì việc sử dụng hàm SUBSTITUTE sẽ đảm bảo rút ngắn thời gian hơn nhiều.

III. Một số lưu ý khi dùng hàm SUBSTITUTE trong Excel

Hàm SUBSTITUTE trong Excel sẽ phân biệt chữ hoa chữ thường. Điều này có nghĩa là chữ “B” sẽ khác “b”.

Ví dụ: Công thức bên dưới sẽ thay thế tất cả các chữ thường “b” bằng “c” trong ô A2, và sẽ không thay thế bất kỳ chữ hoa “B” nào.

Hàm SUBSTITUTE trong Excel sẽ phân biệt chữ hoa chữ thường

IV. Hướng dẫn sử dụng hàm SUBSTITUTE qua các ví dụ minh họa

1. Cú pháp hàm SUBSTITUTE trong excel

Công thức hàm SUBSTITUTE trong excel sẽ là: =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ự chứa 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: Là vị trí mà văn bản cũ xuất hiện mà bạn muốn thay bằng văn bản mới.

2. Ví dụ minh họa cách sử dụng hàm SUBSTITUTE trong excel

Ví dụ 1: Sử dụng hàm SUBSTITUTE để thay thế ký tự ở vị trí đầu tiên trong bảng dữ liệu minh họa.

+ Trong bảng dữ liệu tính Excel, thực hiện nhập hàm =SUBSTITUTE(B4,C4,D4,1) vào ô tham chiếu muốn hiển thị kết quả.

Sử dụng hàm SUBSTITUTE để thay thế ký tự ở vị trí đầu tiên

Nhấn Enter kết quả sẽ hiển thị như hình trên

Ví dụ 2: Sử dụng hàm SUBSTITUTE để thay thế tất cả các ký tự cũ tìm thấy trong bảng dữ liệu minh họa.

+ Trong bảng dữ liệu tính Excel, thực hiện nhập hàm =SUBSTITUTE(B5,C5,D5) vào ô tham chiếu muốn hiển thị kết quả.

Cú pháp như hình trên

Nhấn Enter kết quả sẽ hiển thị như hình

Ví dụ 3: 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 trong bảng dữ liệu minh họa.

+ Trong bảng dữ liệu tính Excel, thực hiện nhập hàm =SUBSTITUTE(B6,"Mua hàng công nghệ giá tốt","Mua hàng công nghệ giá tốt đến ngay Mega") vào ô tham chiếu muốn hiển thị kết quả.

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ố

 Nhấn Enter kết quả sẽ hiển thị như hình 

Ví dụ 4: 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 ký tự nào khác bất kỳ.

Với bảng dữ liệu minh họa bên dưới, nếu muốn xóa tất cả các dấu phẩy trong ô A2, bạn thực hiện theo cú pháp sau: =SUBSTITUTE(A3,",","")

Dùng hàm SUBSTITUTE để xóa dấu phẩy trong Excel

Kết quả dấu phẩy trong chuỗi ký tự đã được xóa

Trong đó:

+ Đối số đầu tiên là ô A23, 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 ô A3, 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: =SUBSTITUTE(A3,",","",1)

Kết quả khi xóa chỉ dấu phẩy đầu tiên

Ví dụ 5: Dùng hàm SUBSTITUTE xóa số 0 đầu dòng trong Excel

Giả sử bạn có bảng dữ liệu được hiển thị như hình dưới, nếu bạn muốn xóa tất cả các số 0 ở đầu mỗi ô bạn dùng công thức sau:

=RIGHT(A3,LEN(A3)-FIND(LEFT(SUBSTITUTE(A3,"0",""),1),A3)+1)

Dùng hàm SUBSTITUTE xóa số 0 đầu dòng trong Excel

Lưu ý:

+ Trong công thức này hàm SUBSTITUTE sẽ thay thế số 0 bằng một khoảng trống. Vì vậy, đối với giá trị 00762ST hàm sẽ cho kết quả là 762ST.

+ Hàm LEFT 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.

+ Hàm FIND sẽ tìm ký tự ngoài cùng bên trái trong công thức LEFT và trả về vị trí của nó. Trong ví dụ này, đối với giá trị ở ô A3 là 00762ST, nó sẽ cho kết quả là 2 (là vị trí của số 2 trong chuỗi văn bản gốc).

+ Kết quả của hàm FIND sẽ được thêm 1 để đả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 chuỗi văn bản.

Kết quả ký tự số 0 đầu dòng được xóa

Ví dụ 6: 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 thao tác này.

Với văn bản “tại mega có máy tính xách tay giá hấp dẫn” trong ô A3, công thức bên dưới sẽ loại thay thế chữ “máy tính xách tay” thành “sản phẩm công nghệ”, chữ “hấp dẫn” thành “tốt”.

=SUBSTITUTE(SUBSTITUTE(A3," máy tính xách tay "," sản phẩm công nghệ "), "hấp dẫn","tốt")

Sử dụng hàm SUBSTITUTE lồng nhau

Trong đó:

+ Hàm SUBSTITUTE bên trong sẽ thay thế chữ “máy tính xách tay” trong ô A2 thành chữ “sản phẩm công nghệ”, kết quả sẽ thành “tại mega có sản phẩm công nghệ giá tốt".

+ Hàm SUBSTITUTE bên ngoài sẽ nhận kết quả từ hàm SUBSTITUTE bên trong, sau đó sẽ thay chữ “hấp dẫn” thành “tốt” như hình dưới.

Kết quả như hình trên, chuỗi ký tự đã được thay thế

V. Tổng kết

Hàm SUBSTITUTE trong excel có nhiều cách sử dụng để xử lý chuỗi ký tự. Việc thành thạo về cú pháp và cách sử dụng hàm này sẽ giúp bạn thao tác với văn bản một cách thuận tiện, nhanh chóng và chuyên nghiệp hơn. Hy vọng bài viết trên sẽ hữu ích với bạn. Chúc bạn thực hiện thành công!

Đừng quên truy cập website mega.com.vn để cập nhật các bài viết mới nhất liên quan đến thủ thuật excel cũng như tin học văn phòng bạn nhé!

Xem thêm >>>

Hướng dẫn cách sử dụng hàm right trong excel cực chi tiết kèm ví dụ!

Hướng dẫn sử dụng hàm value trong excel cực đơn giản

Hướng dẫn cách kẻ đường thẳng, mũi tên trong excel 2007, 2010, 2013 đơn giản

copyright © mega.com.vn