Trong bài viết này, Tin Học Văn Phòng sẽ hướng dẫn các bạn cách sử dụng hàm REPLACE trong Excel thông qua một số ví dụ cụ thể. Hãy cùng tìm hiểu nhé.
Nội dung bài viết
Chức năng của hàm REPLACE trong Excel
Hàm REPLACE thuộc nhóm hàm xử lý chuỗi trong Excel.
Hàm REPLACE trong Excel có chức năng thay thế các ký tự trong một chuỗi văn bản theo vị trí và dựa trên số ký tự mà chúng ta chỉ định bằng một chuỗi văn bản khác.
Chức năng của hàm REPLACE hữu ích khi chúng ta biết được vị trí của văn bản được thay thế được biết hoặc có thể dễ dàng xác định được vị trí đó.
Ví dụ: với công thức bên dưới, hàm REPLACE sẽ tìm ra chuỗi “123” và thay bằng chuỗi “456”.
=REPLACE("ABC123",4,3,"456")// sẽ trả về kết quả là ABC456Cú pháp của hàm REPLACE trong Excel
Hàm REPLACE trong Excel nhận vào bốn đối số riêng biệt với cú pháp như sau:
=REPLACE(old_text, start_num, num_chars, new_text)Trong đó:
- old_text (bắt buộc): đây là chuỗi văn bản mà bạn muốn thay thế một số ký tự trong đó.
- start_num (bắt buộc): đây là vị trí của ký tự đầu tiên trong old_text mà bạn muốn thay thế.
- num_chars (bắt buộc): đây là số lượng ký tự mà bạn muốn thay thế, bắt đầu từ start_num.
- new_text (bắt buộc): chuỗi văn bản mới mà bạn muốn thay thế vào old_text.
Ví dụ: để thay thế chuỗi văn bản trong cột A là “tôi muốn đi học” thành “tôi muốn đi chơi”, các bạn dùng công thức sau:
=REPLACE(A2,10,6,"đi chơi")
Giải thích công thức:
- A2: là ô chứa chuỗi văn bản có nội dung cần thay thế.
- 10: vị trí bắt đầu của chuỗi ký tự cần thay thế trong ô A2, 10 là vị trí của chữ “đ”.
- 6: sẽ lấy 6 ký tự bắt đầu từ vị trí số 10, như vậy ta sẽ có được chuỗi cần thay thế là “đi học”.
- “đi chơi”: chuỗi ký tự sẽ thay thế chữ “đi học”.
Lưu ý khi sử dụng hàm REPLACE trong Excel
- Khi muốn xóa một chuỗi ký tự nào đó ra khỏi văn bản, hãy sử dụng ký tự rỗng (“”) cho đối số new_text.
- Hàm REPLACE trong Excel sẽ xảy ra lỗi #VALUE khi đối số start_num hoặc num_chars bạn truyền vào là số âm hoặc không phải là giá trị số.
- Hàm REPLACE được thiết kế để sử dụng với các chuỗi văn bản và trả về một chuỗi văn bản. Nên khi bạn sử dụng hàm REPLACE với số, hàm này cũng sẽ trả về kết quả dưới dạng chuỗi văn bản.
- Vì hàm REPLACE dựa vào vị trí của chuỗi văn bản xử lý công việc, cho nên việc có dư khoảng trắng thừa trong văn bản cần xử lý sẽ gây phiền phức cho chúng ta. Vì vậy, các bạn lưu ý hãy sử dụng hàm TRIM để bỏ khoảng trắng thừa trước khi sử dụng hàm REPLACE.
Ví dụ cách sử dụng hàm REPLACE trong Excel
Để hiểu rõ hơn về hàm REPLACE trong Excel, các bạn hãy xem thêm một số ví dụ cách sử dụng hàm REPLACE dưới đây.
Dùng hàm REPLACE để xóa chuỗi ký tự trong văn bản
Để xóa một chuỗi ký tự trong văn bản, các bạn dùng hàm REPLACE để thay thế chuỗi ký tự đó thành chuỗi rỗng (“”).
Giả sử bạn có tập dữ liệu sau và bạn muốn thay thế chuỗi ký tự “PRID-” trong ô A2 và chỉ muốn giữ lại phần số. Để thực hiện, các bạn sử dụng công thức sau:
=REPLACE(A2,1,5,"")
Giải thích công thức:
- A2 là ô chứa văn bản có chuỗi ký tự bạn muốn thay thế.
- 1: lấy từ ký tự đầu tiên, trong trường hợp này là chữ “P”
- 5: lấy 5 ký tự (vì chữ cần thay thế là “PRID-” có 5 ký tự), bắt đầu từ ký tự đầu tiên, kết quả là chữ “PRID-“
Dùng hàm REPLACE để lấy username từ địa chỉ email
Giả sử các bạn có một cột chứa danh sách các địa chỉ email, và bạn muốn lấy ra username từ các địa chỉ email đó (xóa đi phần từ dấu @ trở về sau).
Để thực hiện, các bạn có thể sử dụng kết hợp các hàm REPLACE, hàm LEN và hàm FIND theo công thức sau:
=REPLACE(A2,FIND("@",A2),LEN(A2),"")
Giải thích công thức:
- Đầu tiên, chúng ta sử dụng hàm FIND để lấy vị trí của ký tự @ trong ô A2. Lúc này, hàm FIND sẽ trả về số 19, giá trị này được sử dụng làm đối số cho start_num.
- Hàm LEN sẽ trả về chiều dài của chuỗi ký tự chứa trong ô A2, trong ví dụ này là 28.
- Do chúng ta muốn xóa đi phần ký tự từ dấu @ trở về sau mà không quan tâm đến số lượng ký tự. Cho nên chỉ cần truyền vào cho tham số num_chars một số lớn hơn số lượng ký tự từ dầu @ trở về sau là được. Trong trường hợp này chúng ta sử dụng hàm LEN để lấy số lượng ký tự của ô A2.
Khi thay các hàm trong hàm REPLACE bằng kết quả trả về, các bạn sẽ có được công thức:
=REPLACE(A2,19,28,"")Dùng hàm REPLACE với ngày tháng
Như đã đề cập ở trên, hàm REPLACE được thiết kế để làm việc với chuỗi văn bản. Vì vậy, để hàm REPLACE hoạt động chính xác với dữ liệu ngày tháng, trước tiên, bạn phải chuyển đổi ngày tháng thành chuỗi văn bản trước rồi mới sử dụng hàm REPLACE trên chuỗi ngày tháng đó.
Ở ví dụ dưới đây, mình sẽ sử dụng trực tiếp hàm TEXT để chuyển đổi ngày tháng thành chuỗi văn bản và truyền vào làm đối số old_text của hàm REPLACE.
Trong ví dụ bên dưới, chúng ta có ô A2 sẽ chứa ngày tháng, và ô B2 sẽ chứa công thức để thay thế năm 2021 thành 2020 như sau:
=REPLACE(A2, 6, 4, "2020")Trong đó:
- A2 là ô chứa ngày tháng
- 6 là vị trí bắt đầu của chuỗi sẽ được thay thế, ở ví dụ này là số 2 (trong 2021)
- “2020” là chuỗi sẽ thay thế
Tuy nhiên, các bạn có thể thấy kết quả như trong hình, nó sẽ không như chúng ta mong muốn như khi hàm REPLACE xử lý với chuỗi ký tự bình thường.
Tiếp theo, chúng ta hãy sử dụng hàm TEXT để chuyển ngày tháng trong ô A2 thành chuỗi ngày tháng bằng công thức sau:
=TEXT(A2, "m/dd/yyyy")
Kết quả của công thức trên vẫn có giá trị là “8/26/2021” như ở ô A2, tuy nhiên nó là ở dạng chuỗi ký tự chứ không phải ngày tháng.
Và công thức cuối cùng của chúng ta sẽ như bên dưới:
=REPLACE(TEXT(A2, "m/dd/yyyy"), 6, 4, "2020")
Các bạn có thể thấy, sau khi chuyển ngày tháng thành dạng chuỗi ký tự thì hàm REPLACE đã hoạt động đúng như mong đợi như hình trên.
Bên trên là hướng dẫn sử dụng hàm REPLACE trong Excel cùng với một số ví dụ cách dùng hàm REPLACE. Hy vọng bài viết sẽ giúp ích cho các bạn.
Chúc các bạn thành công!
Xem thêm:
- Hướng dẫn sử dụng hàm TEXT trong Excel
- Hướng dẫn sử dụng hàm SUBSTITUTE trong Excel
- Hướng dẫn sử dụng hàm TRIM trong Excel
- Hướng dẫn sử dụng hàm LEN trong Excel
- Hướng dẫn sử dụng hàm MID trong Excel
- Hướng dẫn sử dụng hàm LEFT trong Excel
- Hướng dẫn sử dụng hàm RIGHT trong Excel
- Hướng dẫn sử dụng hàm SEARCH trong Excel
- Hướng dẫn sử dụng hàm FIND trong Excel