Đánh giá lequocthai.com:
Hàm REPLACE cũng có thể được dùng để loại bỏ một phần của chuỗi bằng cách thay thế phần đó bằng chuỗi trống.
Cú pháp
Hàm REPLACE nhận bốn đối số, tất cả đều bắt buộc. Cú pháp như sau.
=REPLACE(old_text, start_num, num_chars, new_text)Đối số:
Hiểu rõ mỗi đối số để sử dụng hàm REPLACE một cách hiệu quả.
- old_text – Đây là đoạn văn bản đầu vào cần thay thế. Giá trị của `old_text` có thể nhập trực tiếp trong dấu ngoặc kép hoặc tham chiếu tới một ô.
- start_num – Chỉ vị trí ký tự bắt đầu trong đoạn văn bản đầu vào mà từ đó muốn thực hiện việc thay thế.
- num_chars – Số ký tự cần thay thế trong đoạn văn bản đầu vào.
- new_text – Chứa giá trị văn bản hoặc ký tự thay thế. Đây là văn bản mới sẽ thay thế phần cũ.
Những đặc điểm quan trọng của hàm REPLACE
- Mỗi ký tự, bao gồm số, chữ cái và dấu cách, đều được tính là 1 ký tự, bất kể cài đặt ngôn ngữ của máy tính.
- Ngay cả khi giá trị của đối số `old_text` ở dạng số, hàm REPLACE vẫn sẽ thay thế ký tự theo yêu cầu, nhưng giá trị trả về sẽ ở dạng văn bản.
- Nếu giá trị của đối số `start_num` nhỏ hơn hoặc bằng 0, hàm REPLACE sẽ trả về lỗi #VALUE!.
- Nếu một giá trị không phải dạng số được dùng làm `start_num` hoặc `num_chars`, hàm sẽ trả về lỗi #NAME?.
- Hàm REPLACE trả về lỗi #VALUE! khi giá trị của `num_chars` là số âm.
- Nếu giá trị văn bản của `new_text` không được bao quanh bởi dấu ngoặc kép, hàm sẽ trả về lỗi #NAME?.
Ví dụ về hàm REPLACE
Như hàm REPLACE chấp nhận nhiều đối số, chúng ta sẽ hiểu các chức năng cơ bản qua một ví dụ.
Ví dụ 1 – Chức năng cơ bản của hàm REPLACE
Trong ví dụ này, chúng tôi sử dụng dữ liệu mẫu gồm họ và tên, địa chỉ, và số điện thoại để minh họa cách hàm REPLACE hoạt động với các văn bản đầu vào khác nhau.
Trong ví dụ đầu tiên, chúng ta chỉ thay thế một ký tự ở vị trí 1 bằng chữ “S”. Như trong ô B3, ký tự ở vị trí đầu tiên, chữ ‘T’, đã được thay bằng chữ ‘S’.
Tương tự, trong ví dụ thứ hai, chúng ta thay thế 4 ký tự bắt đầu từ vị trí 1 bằng “XXXX”, như thể hiện trong ô B4.
Trong ví dụ thứ ba, giá trị đầu vào ở dạng số và chúng ta thay thế 1 ký tự ở vị trí 4 (số “5”) bằng “@”. Kết quả trả về như mong đợi nhưng ở dạng văn bản, điều này rõ ràng qua căn lề trái.
Hy vọng chức năng cơ bản của hàm REPLACE đã trở nên rõ ràng. Trong các ví dụ tiếp theo, chúng ta sẽ khám phá thêm các ứng dụng của nó.
Ví dụ 2 – Thay thế văn bản khi vị trí đã biết
Đôi khi các công ty đổi thương hiệu bằng cách thay đổi tên thương hiệu. Gần đây, Facebook đã đổi tên thành Meta. Trong ví dụ này, chúng ta có một số dữ liệu về Facebook, nhưng sau khi đổi thương hiệu, từ ‘Facebook’ phải được thay bằng ‘Meta’.
Thực hiện thủ công là công việc khó khăn và không hiệu quả. Thay vào đó, chúng ta có thể dùng hàm REPLACE để giải quyết.
Công thức được sử dụng như sau:
=REPLACE(B3,1,8,"Meta")
Vì từ cần thay thế nằm ở đầu chuỗi, việc dùng hàm REPLACE trở nên dễ dàng khi `start_num` được đặt bằng 1. Độ dài của từ ‘Facebook’ là 8, do đó `num_chars` = 8.
Trong tình huống trên, vị trí của từ cần thay thế đã biết và cố định trong toàn bộ dữ liệu. Trong thực tế, nhiều khi vị trí không cố định. Hãy xem cách thay thế văn bản khi vị trí bắt đầu của từ thay đổi.
Ví dụ 3 – Thay thế văn bản với vị trí biến đổi
Tiếp nối ví dụ trước, chúng ta vẫn xem lại kịch bản Facebook đổi tên thành Meta. Giờ chúng ta có các địa chỉ email của nhân viên sẽ được cập nhật theo tên công ty mới. Độ dài phần tên người dùng (username) khác nhau, vì vậy đối số `start_num` sẽ khác nhau.
Để xác định vị trí ký tự đầu tiên của “@facebook” trong các địa chỉ email, chúng ta có thể dùng hàm FIND. Công thức như sau:
=FIND("@facebook",B3)Sau đó, chúng ta ghép hàm REPLACE để thay “@facebook” bằng “@meta” và có danh sách email đã cập nhật.
Giá trị trả về của hàm FIND sẽ làm đối số `start_num` cho hàm REPLACE. Số ký tự cần thay thế là độ dài của “@facebook” (9 ký tự).
Công thức cuối cùng:
=REPLACE(B3,FIND("@facebook",B3),9,"@meta")
Ví dụ 4 – Hàm REPLACE với ngày tháng
Trong ví dụ này, chúng ta có danh sách ngày lễ cố định cho năm 2022. Bây giờ cần cập nhật lịch cho năm 2023. Giải pháp đơn giản là thay “2022” bằng “2023”.
Như chúng ta biết, hàm REPLACE có thể áp dụng cho giá trị số nhưng sẽ chuyển định dạng sang dạng văn bản. Vì vậy, khi dùng hàm REPLACE với ngày tháng, giá trị được thay thế nhưng định dạng gốc sẽ bị chuyển thành dạng văn bản.
Áp dụng hàm REPLACE cho cột B, công thức:
=REPLACE(B3,8,4,2023)
Như quan sát, kết quả trong cột C không còn là ngày tháng. Nguyên nhân là Excel lưu ngày dưới dạng số tuần tự và chỉ hiển thị định dạng ngày khi được định dạng lại. Để giữ lại định dạng ngày, chúng ta có thể kết hợp hàm TEXT và REPLACE.
Đầu tiên, chuyển ngày trong cột B sang dạng văn bản bằng hàm TEXT với định dạng “dd-mmm-yyyy”. Sau đó, thay 4 ký tự của năm (2022) bằng 2023, bắt đầu từ vị trí thứ 8. Công thức cuối cùng:
=REPLACE(TEXT(B3,"dd-mmm-yyyy"),8,4,2023)
Bây giờ chúng ta đã có lịch cập nhật cho năm 2023.
Ví dụ 5 – Xóa văn bản bằng hàm REPLACE
Như các ví dụ trên đã chỉ ra cách dùng hàm REPLACE để thay thế văn bản, một ứng dụng hữu ích khác là loại bỏ ký tự khỏi một chuỗi bằng cách thay thế chúng bằng chuỗi trống.
Trong ví dụ này, chúng ta có danh sách các cuốn sách phải đọc trong kỳ nghỉ. Mỗi tên sách đều có tiền tố “Book n:”. Chúng ta muốn chia sẻ danh sách này trên mạng xã hội và loại bỏ tiền tố.
Chúng ta sẽ dùng hàm REPLACE để thay thế 8 ký tự đầu tiên “Book n:” bằng chuỗi trống, bắt đầu từ vị trí 1. Công thức:
=REPLACE(B3,1,8,"")
Bây giờ danh sách sách đã không còn tiền tố đánh số.
Ví dụ 6 – Hàm REPLACE lồng nhau (Nested REPLACE)
Trong một số trường hợp, chúng ta cần thay thế nhiều ký tự trong một chuỗi. Thay vì thực hiện thay thế lần lượt, chúng ta có thể dùng hàm REPLACE lồng nhau để giảm số vòng lặp.
- Thêm mã quốc gia ở đầu số điện thoại bằng cách thay thế 0 ký tự bằng “+1” tại vị trí 1:
=REPLACE(C3,1,0,"+1")
- Thêm dấu gạch ngang sau mã quốc gia, thay thế 0 ký tự bằng “-” tại vị trí 3 trên kết quả của bước trước:
=REPLACE(REPLACE(C3,1,0,"+1"),3,0,"-")
- Tiếp tục chèn dấu gạch ngang tại vị trí 7 và 11 để tách mã vùng và phần còn lại. Công thức cuối cùng:
=REPLACE(REPLACE(REPLACE(REPLACE(C3,1,0,"+1"),3,0,"-"),7,0,"-"),11,0,"-")
Đọc thêm đề xuất
- Cách định dạng số điện thoại trong Excel
- Hàm REPLACE vs Hàm SUBSTITUTE
Hàm REPLACE thay thế một phần văn bản bằng văn bản được chỉ định, dựa trên vị trí bắt đầu và số ký tự muốn thay thế.
Hàm SUBSTITUTE rất giống với hàm REPLACE, nhưng nó thay thế văn bản dựa trên việc tìm kiếm trong chuỗi đầu vào. Vì vậy, hàm REPLACE được dùng khi vị trí của văn bản cần thay thế đã biết và cố định, trong khi hàm SUBSTITUTE hữu ích khi vị trí không xác định.
Hàm REPLACE có thể kết hợp với hàm FIND nếu vị trí của văn bản chưa rõ, như trong ví dụ ở trên.
Thêm vào đó, hàm SUBSTITUTE còn chấp nhận một đối số tùy chọn (`instance_num`) để xác định lần xuất hiện nào của văn bản cần thay thế.
Chúng tôi hy vọng hướng dẫn chi tiết về hàm REPLACE cùng với các ứng dụng thực tế sẽ hữu ích cho bạn. Hãy thực hành để khám phá thêm các cách sử dụng hàm này. Chúng tôi sẽ sớm trở lại với một hàm Excel thú vị và hữu ích khác.
