Trong bài viết này, Tin Học văn phòng và công sở sẽ hướng dẫn chúng ta sử dụng hàm SUBSTITUTE vào Excel để sửa chữa ký tự trong chuỗi văn bản. Không chỉ khám phá về cú pháp cơ bản, chúng ta còn được học phương pháp sử dụng trải qua các ví dụ gắng thể.

Bạn đang xem: Hàm substitute trong excel, ví dụ minh họa và cách dùng


*
sử dụng hàm SUBSTITUTE trong excel

Hàm SUBSTITUTE vào Excel dùng để làm gì?

Hàm SUBSTITUTE được giới thiệu từ phiên bản Microsoft Excel 2007 cùng thuộc đội hàm cách xử lý chuỗi cam kết tự.

Hàm SUBSTITUTE được thực hiện để thay thế một hoặc nhiều chuỗi văn phiê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 bọn họ muốn thay thế sửa chữa văn phiên bản cũ vào một chuỗi bằng một chuỗi mới.

Cú pháp hàm SUBSTITUTE vào Excel

Hàn SUBSTITUTE vào Excel có 4 tham số, cha tham số đầu tiên của hàm SUBSTITUTE là phải và tham số sau cuối là tùy chọn.

Trong đó:

=SUBSTITUTE(text, old_text, new_text, )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 bí quyết này, text hoàn toàn có thể là một chuỗi văn phiên bản trực tiếp, tham chiếu mang đến một ô hoặc hiệu quả của một bí quyết khác.old_text (bắt buộc): là chuỗi ký kết tự mà bạn có nhu cầu thay thế.new_text (bắt buộc): là chuỗi ký kết tự bắt đầu sẽ sửa chữa old_text.instance_num (tùy chọn): tham số này là tùy chọn, nếu không truyền thông số này vào, tất cả chuỗi old_text xuất hiện thêm trong chuỗi text sẽ tiến hành thay thế. Tham số này chỉ xác định trí cơ mà chuỗi ký kết tự old_text sẽ được sửa chữa thay thế bởi new_text. Để làm rõ hơn tham số này thì các bạn hãy coi ví dụ mặt dưới.Ví dụ:

Các công thức bên dưới đều thay thế chữ “2021” thành “2020” vào ô A2. Tuy nhiên, kết quả trả về sẽ khác nhau tùy theo thông số instance_num.

Khi ko truyền thông số instance_num vào: mang định thì hàm SUBSTITUTE sẽ thay thế sửa chữa tất cả chữ “2021” thành “2020” .
*
sử dụng hàm SUBSTITUTE trong excel -không nhập thông số cuối

Khi truyền thông số instance_num = 1 vào: Hàm SUBSTITUTE chỉ thay thế sửa chữa 1 chữ “2021” được kiếm tìm thấy trước tiên thành “2020”. Chữ “2021” còn lại sẽ được giữ nguyên như cũ.
*
sử dụng hàm SUBSTITUTE trong excel – nhập tham số cuối là 1

Khi truyền tham số instance_num = 2 vào: Hàm SUBSTITUTE sẽ sửa chữa chữ “2021” xuất hiện thêm 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ế sửa chữa chữ tại vị trí số mấy thì bạn nhập số đó vào đối số cuối cùng.

Xem thêm: Lịch Sử Ghép Cầu Rồng Bạch Kim 20, Rồng Bạch Kim 22


*
sử dụng hàm SUBSTITUTE vào excel – nhập thông số cuối là 2

Một số chú ý khi sử dụng hàm SUBSTITUTE vào Excel

Hàm SUBSTITUTE trong Excel sáng tỏ chữ hoa chữ thường. Điều này có nghĩa là chữ “X” đã khác “x”.

Ví dụ: công thức dưới sẽ sửa chữa tất cả các chữ thường xuyên “x” bởi “y” trong ô A2, và sẽ không thay thế ngẫu nhiên chữ hoa “X” nào.


*
sử dụng hàm SUBSTITUTE trong excel – rành mạch hoa thường

Các ví dụ về phong thái sử dụng hàm SUBSTITUTE vào Excel

Để hiểu rõ hơn về cách sử dụng của hàm SUBSTITUTE vào Excel, chúng ta hãy chú ý một vài ví dụ sau đây:

Dùng hàm SUBSTITUTE để xóa lốt phẩy trong Excel

Để sa thải dấu phẩy bằng phương pháp sử dụng hàm SUBSTITUTE trong Excel, chúng ta cũng có thể thay nắm dấu phẩy bằng một không gian hoặc bất kỳ ký tự nào khác.

Giả sử chúng ta có một tập tài liệu như hình dưới đây, nếu còn muốn xóa tất cả các vệt phẩy vào ô A2, các bạn dùng công thức sau:

=SUBSTITUTE(A2,",","")

*

Trong đó:

Đối số trước tiên là ô A2, cất văn bạn dạng cần nuốm thế.Đối số thứ 2 là lốt phẩy, là ký kết tự phải thay thế.Đối số sản phẩm công nghệ 3 là chuỗi rỗng, dùng để làm thay cụ dấu phẩy.

Công thức trên sẽ thay thế sửa chữa tất cả lốt phẩy vào ô A2, vào trường hợp bạn muốn thay cố kỉnh chỉ vệt phẩy thứ nhất thì cần sử dụng công thức mặt dưới.

=SUBSTITUTE(A2,",","",1)

Xóa số 0 đầu dòng trong Excel

Giả sử các 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 toàn bộ các số 0 ở đầu mỗi ô, các bạn dùng cách làm sau:

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

*

Trong đó:

Hàm SUBSTITUTE của phương pháp sẽ sửa chữa thay thế số 0 bằng một khoảng tầm trống. Bởi vì vậy, đối với giá trị 023LCH, hàm đang cho công dụng là 23LCH.Hàm LEFT kế tiếp sẽ trích xuất những ký tự ko kể cùng phía bên trái của chuỗi kết quả trả về tự hàm SUBSTITUTE, vào trường hòa hợp này vẫn là 2.Sau đó, hàm FIND sẽ tìm ký kết tự ngoài cùng bên trái được cung ứng bởi bí quyết LEFT và trả về địa chỉ của nó. Trong lấy ví dụ này, đối với giá trị ngơi nghỉ ô A2 là 023LCH, nó vẫn cho tác dụng là 2 (là địa chỉ của số 2 vào chuỗi văn bản gốc).1 được thêm vào kết quả của hàm FIND, để bảo vệ sẽ nhấn được tổng thể chuỗi văn bạn dạng được trích xuất (ngoại trừ những số 0 sống đầu).Kết quả của hàm FIND kế tiếp được trừ đi từ kết quả của hàm LEN (được áp dụng để trả về độ dài của toàn thể chuỗi văn bản).Giá trị sau cùng sau đó được sử dụng làm cho đối số mang lại hàm RIGHT để trích xuất toàn bộ chuỗi văn bạn dạng (ngoại trừ các số 0 làm việc đầu).

Các các bạn lưu ý, để bảo vệ công thức trên hoạt động đúng chuẩn trong ngôi trường hợp có khoảng trắng ở đầu hoặc ở cuối văn bản trong những ô, cực tốt bạn nên áp dụng hàm TRIM với mọi ô được thực hiện trong công thức.

Cuối cùng, họ sẽ bao gồm 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 vào Excel không thể thay thế nhiều chuỗi văn bạn dạng khác nhau và một lúc. Mặc dù nhiên, hàm SUBSTITUTE hoàn toàn có thể được thực hiện lồng vào với nhau để thực hiện việc trên.

Ví dụ: cùng với văn bản “tôi có một con chó và một bé mèo” vào ô A2, công thức 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 phía trong sẽ thay thế sửa chữa chữ “chó” vào ô A2 thành chữ “bò”, công dụng thành “tôi gồm một nhỏ bò với một nhỏ mèo”.Sau đó, hàm SUBSTITUTE phía bên ngoài sẽ nhận tác dụng từ hàm SUBSTITUTE mặt trong, rồi cụ chữ “mèo” thành “trâu”.

Bên trên là hướng dẫn cách thực hiện hàm SUBSTITUTE trong Excel và một số ví dụ về kiểu cách sử dụng hàm SUBSTITUTE. Hy vọng nội dung bài viết sẽ giúp ích cho các bạn.