Cách dùng hàm SUBSTITUTE trong Excel kèm ví dụ chi tiết

Hàm SUBSTITUTE trong Excel có tác dụng thay thế một đoạn văn bản này thành một đoạn văn bản khác. Vậy chúng ta sẽ ứng dụng nó như thế nào trong công việc thực tế? Hãy cùng chúng mình tìm hiểu qua bài viết ngay dưới đây.

Xem thêm: Thành thạo Substitute và 150 hàm khác nhờ khóa học Excel này

Lý thuyết về hàm SUBSTITUTE trong Excel

Công dụng và cú pháp của hàm SUBSTITUTE 

Hàm SUBSTITUTE trong Excel được sử dụng rất nhiều trong việc xử lý chuỗi văn bản, có tác dụng thay thế một đoạn văn bản thành một đoạn văn bản khác.

Cú pháp của hàm có dạng như sau:

= SUBSTITUTE (text, old_text, new_text, [instance_num])

Trong đó bao gồm các thành phần là:

• text: là đoạn văn bản gốc mà chúng ta cần thay thế nội dung bên trong
• old_text: là nội dung đoạn văn bản cần thay thế
• new_text: là đoạn văn bản mới sẽ thay vào vị trí của old_text
• instance_num: nếu old_text xuất hiện nhiều lần trong đoạn văn bản gốc thì tham số này sẽ xác định vị trí xuất hiện ở lần thứ mấy. Nếu không xác định tham số này thì sẽ tính tất cả các vị trí xuất hiện.

Kết quả của hàm này là đoạn văn bản mới sau khi đã được thay thế nội dung.

Ví dụ minh họa về hàm SUBSTITUTE 

Cho văn bản gốc và yêu cầu về thay thế đoạn văn bản như sau:

Trong đó, đoạn văn bản “Cty TNHH Hà Thành” nằm tại ô D21, còn đoạn “Phạm Hùng” nằm tại ô D22.

Công thức hàm mà chúng ta sử dụng sẽ có dạng như sau:

= SUBSTITUTE(D21, “Cty”, “Công ty”)

= SUBSTITUTE(D21, “ ”, “ Văn ”)

Các bạn chú ý là phía trước và phía sau chữ “Văn” trong công thức trên có dấu cách để đảm bảo phần tên không bị dính vào nhau sau khi áp dụng công thức.

Kết quả trả về như sau:

Xem thêm: Cách loại bỏ dấu cách trong đoạn văn bản trên Excel đơn giản nhanh chóng

Bài tập ứng dụng về hàm SUBSTITUTE 

Các bạn có thể thấy hàm SUBSTITUTE có thể dùng để xử lý dữ liệu dạng Text rất hiệu quả. Bây giờ chúng ta sẽ ứng dụng hàm này để tách riêng phần tên tỉnh, thành phố ra khỏi một chuỗi địa chỉ.

Cho bảng dữ liệu như sau:

Yêu cầu: Dùng hàm SUBSTITUTE để tách riêng phần tên tỉnh, thành phố ra khỏi cột Địa chỉ.

Cách thực hiện như sau:

Trước hết, các bạn tham khảo sơ đồ về cách chúng mình sử dụng các hàm để thực hiện yêu cầu của đề bài.

Sơ đồ này có ý nghĩa như sau:

Trong phần địa chỉ các bạn có thể thấy các thông tin được ngăn cách với nhau bằng dấu phẩy. Ở mỗi dòng địa chỉ chúng ta có 2 dấu phẩy ngăn cách. Chúng ta sẽ dùng hàm SUBSTITUTE để thêm các dấu cách vào chuỗi địa chỉ nhưng vẫn giữ nguyên dấu phẩy. Sau khi thêm dấu cách thì sẽ dùng hàm RIGHT để tách đoạn văn bản ở phía bên tay phải theo một số lượng ký tự nhất định mà không phải lo rằng nó sẽ bị tính vào phần tên quận. Khi đã tách xong thì chúng ta chỉ cần dùng hàm TRIM để loại bỏ các dấu cách thừa là chúng ta sẽ thu được kết quả như đề bài yêu cầu.

Khi bắt đầu viết hàm, chúng ta sẽ viết từ hàm SUBSTITUTE trước với công thức như sau:\

= SUBSTITUTE(A3, “,” ,         “,”&REPT(" ", 255))

Lúc này nếu các bạn bấm Enter thì sẽ thu được kết quả như sau:

Nếu mở rộng cột B ra thì sẽ thầy giữa chữ “Xã Đàn” và chữ “Đống Đa” trong chuỗi địa chỉ là một khoảng cách rất dài như trong hình ảnh dưới đây:

Kéo sang phải tiếp thì các bạn có thể thấy khoảng cách giữa chữ “Đống Đa” và “Hà Nội” cũng rất xa:

Nhưng chúng ta không dừng ở đây mà sẽ lồng thêm hàm RIGHT vào như sau:

=RIGTH(SUBSTITUTE(A3, “,” ,         “,”&REPT(" ", 255)), 255)

Sau khi lồng thêm hàm RIGHT thì kết quả trả về sẽ là tên tỉnh, thành phố kèm theo rất nhiều dấu cách thừa phía trước. Do đó chúng ta dùng thêm hàm TRIM để loại bỏ dấu cách thừa. Công thức hoàn chỉnh cuối cùng như sau:

=TRIM(RIGTH(SUBSTITUTE(A3, “,” ,         “,”&REPT(" ", 255)), 255))

Kết quả thu được cuối cùng như sau:

Bây giờ các bạn chỉ cần thực hiện copy công thức bằng cách rất đơn giản là là kéo ô vuông màu xanh ở góc dưới bên phải ô B3 đi xuống hết các dòng còn lại. Kết quả mà chúng ta thu được là tên tỉnh, thành phố đã được tách riêng đúng theo yêu cầu của đề bài.

Kết luận

Như vậy, qua bài viết của chúng mình thì các bạn đã nắm được nguyên lý hoạt động của hàm SUBSTITUTE trong Excel và cách kết hợp hàm này cùng các hàm khác để xử lý vấn đề trong công việc thực tế. Chúc bạn học tập hiệu quả!