Cách sử dụng hàm SUBSTITUTE trong Excel đơn giản, dễ hiểu

Hàm SUBSTITUTE trong Excel hỗ trợ thay thế một chuỗi văn bản cũ bằng một chuỗi văn bản mới một cách nhanh chóng. Nếu bạn chưa nắm rõ cách dùng hàm này, hãy theo dõi bài viết sau để tìm hiểu chi tiết về hàm SUBSTITUTE cũng như cách sử dụng nó nhé.

Hàm SUBSTITUTE trong Excel là gì?

Trong quá trình làm việc với Excel, ngoài một số hàm như UPPER và PROPER giúp tối ưu hóa xử lý dữ liệu văn bản, hàm SUBSTITUTE cũng là một công cụ hữu ích khác hỗ trợ người dùng. Hàm SUBSTITUTE có chức năng thay thế chuỗi văn bản cũ bằng chuỗi mới một cách linh hoạt và tiện lợi. 

Những ứng dụng phổ biến của hàm này bao gồm:

• Thay thế đoạn văn bản cũ hiệu quả.
• Kết hợp linh hoạt với nhiều hàm khác để tối ưu hóa công việc.

Khi sử dụng hàm SUBSTITUTE nhiều điều kiện, người dùng có thể nhanh chóng thay thế các chuỗi ký tự cũ bằng chuỗi ký tự mới mà không cần nhập lại thủ công. Điều này giúp tiết kiệm rất nhiều thời gian khi chỉnh sửa văn bản, giúp người dùng chủ động hơn trong công việc.

Hướng dẫn sử dụng hàm SUBSTITUTE trong Excel

Cú pháp hàm SUBSTITUTE

Cú pháp của hàm SUBSTITUTE là: =SUBSTITUTE(text, old_text, new_text, [instance_num])

Trong đó:

• Text: Chuỗi văn bản mà bạn muốn thay thế các ký tự trong đó.
• Old_text: Chuỗi văn bản cũ mà bạn muốn thay thế.
• New_text: Chuỗi văn bản mới mà bạn muốn sử dụng để thay thế cho văn bản cũ.
• Instance_num: Vị trí xuất hiện của văn bản cũ mà bạn muốn thay thế bằng văn bản mới (tùy chọn).

Cách dùng hàm SUBSTITUTE 

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.

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

Bước 2: Nhấn phím Enter để hiển thị kết quả.

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.

Bước 1: Trong bảng dữ liệu Excel, nhập công thức =SUBSTITUTE(B5, C5, D5) vào ô tham chiếu muốn hiển thị kết quả.

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Ví dụ 3: Sử dụng hàm SUBSTITUTE để thay thế đoạn văn bản cũ bằng đoạn văn bản mới trong bảng dữ liệu minh họa.

Bước 1: Trong bảng dữ liệu Excel, nhập công thức =SUBSTITUTE(B6, “Thời tiết hôm qua”, “Khí hậu hôm nay”) vào ô tham chiếu muốn hiển thị kết quả.

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Dùng hàm SUBSTITUTE trong trường hợp nào?

Hàm SUBSTITUTE rất hữu ích trong nhiều tình huống khác nhau khi làm việc với dữ liệu văn bản. Dưới đây là một số ứng dụng cụ thể của hàm này:

Tách chữ theo vị trí đầu tiên với LEFT, cuối với RIGHT, bất kỳ vị trí nào với MID:

Ví dụ bạn có dãy số “55 77 99” và muốn tách các phần bên trái và bên phải, bạn có thể sử dụng hàm LEFT và RIGHT. Đối với việc chuyển đổi chữ thành số, bạn có thể sử dụng dấu trừ trừ (–, hai dấu trừ). Nếu dữ liệu cần tách là chữ, thì bạn không cần sử dụng hai dấu trừ này. Với hàm MID, bạn có thể ghép một khoảng trắng vào phía trước để dễ dàng tách ra vị trí mong muốn từ trái sang.

Tách cụm chữ giữa các ký tự phân cách:

Ví dụ, để lấy cụm chữ “123456789” từ chuỗi “190309-123456789-012345”, bạn có thể ghép một dấu gạch nối vào đầu chuỗi và sau đó sử dụng hàm SUBSTITUTE để tách cụm chữ cần thiết.

Tách địa chỉ ra từng phần như Xã/Phường, Huyện/Quận, Tỉnh/Thành phố:

Hàm SUBSTITUTE có thể được sử dụng để thay thế các ký tự phân cách và sau đó sử dụng các hàm khác như LEFT, RIGHT, hoặc MID để tách các phần địa chỉ cụ thể.

Đếm số lượng từ xuất hiện trong ô:

Ví dụ, để đếm số lần các từ như Bò, Gà, Chó xuất hiện trong một ô, bạn có thể sử dụng hàm SUBSTITUTE để thay thế từ cần đếm bằng chuỗi trống và sau đó tính toán sự chênh lệch độ dài để xác định số lần xuất hiện của từ đó.

Ngoài ra hàm SUBSTITUTE kết hợp với REPT để biến mọi khoảng trắng trong chuỗi văn bản gốc thành nhiều khoảng trống, cụ thể hơn là 99 khoảng trắng.

Các điểm cần lưu ý khi sử dụng hàm SUBSTITUTE

Cú pháp hàm SUBSTITUTE:

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

• text: Chuỗi văn bản chứa ký tự cần thay thế.
• old_text: Chuỗi văn bản cũ mà bạn muốn thay thế.
• new_text: Chuỗi văn bản mới để thay thế cho chuỗi văn bản cũ.
• instance_num: (Tùy chọn) Vị trí của chuỗi văn bản cũ mà bạn muốn thay thế. Nếu không chỉ định, tất cả các chuỗi old_text sẽ được thay thế.

Phân biệt chữ hoa và chữ thường: Hàm SUBSTITUTE phân biệt chữ hoa và chữ thường, nên “A” khác với “a”.

Không thay thế toàn bộ chuỗi nếu không chỉ định instance_num: Nếu không chỉ định instance_num, hàm sẽ thay thế tất cả các lần xuất hiện của old_text trong text.

Thay thế một lần xuất hiện cụ thể: Để thay thế một lần xuất hiện cụ thể của old_text, hãy sử dụng instance_num. Ví dụ, để thay thế lần xuất hiện thứ hai của “apple” trong chuỗi, sử dụng công thức =SUBSTITUTE(“apple apple apple”, “apple”, “orange”, 2).

Ứng dụng với các hàm khác: Hàm SUBSTITUTE có thể được kết hợp với các hàm khác như LEFT, RIGHT, MID, LEN để thực hiện các tác vụ phức tạp hơn.

Xử lý dữ liệu lớn: Khi làm việc với dữ liệu lớn, hãy lưu ý rằng hàm SUBSTITUTE có thể làm chậm quá trình tính toán nếu sử dụng nhiều lần trên các chuỗi văn bản dài.

Không thay đổi dữ liệu gốc: Hàm SUBSTITUTE không thay đổi dữ liệu gốc trong ô mà chỉ trả về kết quả mới sau khi thay thế. Nếu bạn muốn lưu kết quả thay thế, bạn cần sao chép và dán giá trị của kết quả.

Kiểm tra kết quả: Sau khi sử dụng hàm SUBSTITUTE, hãy kiểm tra kỹ kết quả để đảm bảo rằng các thay thế đã diễn ra như mong muốn, đặc biệt khi làm việc với dữ liệu quan trọng hoặc nhạy cảm.

Nhớ các điểm này sẽ giúp bạn sử dụng hàm SUBSTITUTE hiệu quả và tránh các lỗi phổ biến khi xử lý dữ liệu văn bản trong Excel.

Tạm kết

Hàm SUBSTITUTE rất đa năng và hữu ích trong việc xử lý dữ liệu văn bản. Bạn có thể áp dụng hàm này trong nhiều tình huống khác nhau để thay thế hoặc tách chuỗi văn bản một cách hiệu quả. Hãy thử nghiệm và vận dụng hàm SUBSTITUTE để tối ưu hóa công việc của bạn!

Xem thêm:

• Nguyên nhân và 3 cách sửa lỗi Value trong Excel thường gặp
• Tổng hợp 7 cách bỏ số 0 sau dấu chấm trong Excel đơn giản