Tiếp theo chuỗi bài viết về những thủ thuật và cách sử dụng hàm Excel thì hôm nay mình sẽ chia sẻ với các bạn cách để sử dụng hàm thay thế văn bảng trong Excel là hàm REPLACE và SUBSTITUTE.
Dùng luôn công cụ Find and Replace để thay thế văn bản cho nhanh, dùng hàm REPLACE và SUBSTITUTE làm gì? Thì công cụ Find and Replace có thể giúp bạn tìm kiếm và thay thế văn bản rất nhanh, nhưng nó cũng có rất nhiều hạn chế trong nhiều tình huống. Ví dụ như: mình cần thay thế vào vị trí xác định trong văn bản cũ, chèn thêm văn bản mới vào giữa hay thay thế vào vị trí xuất hiện của văn bản thì công cụ Find and Replace sẽ không giúp được. Thay vào đó thì hàm REPLACE và SUBSTITUTE sẽ giúp mình thực hiện những việc đó và cách thực hiện như sau:
Cách sử dụng hàm REPLACE
Mô tả:
Hàm REPLACE là hàm thay thế văn bản, có chức năng là thay thế một phần của văn bản bằng văn bản khác vào vị trí được bạn chỉ định.
Cú pháp:
=REPLACE(old_text, start_num, num_chars, new_text)
➤ Trong đó:
- old_text: là văn bản hoặc tham chiếu đến một Cells.
- start_num: vị trí của ký tự bắt đầu trong văn bản cũ mà bạn muốn thay thế bằng văn bản mới.
- num_chars: số lượng ký tự mà bạn muốn thay thế bằng văn bản mới.
- new_text: văn bản sẽ thay thế phần văn bản trong old_text.
Ví dụ:
➤ Giải thích các ví dụ trên
- REPLACE(A2;12;2;”26″): sẽ thay thế 26 vào văn bản trong Cells A2 ở vị trí bắt đầu là 12 và thay 2 ký tự. Kết quả 26 sẽ thay thế vào 25.
- REPLACE(A3;1;2;26): thay thế 26 vào vị trí bắt đầu là 1 và thay 2 ký tự. Kết quả 25 sẽ được thay thế bằng 26.
- REPLACE(REPLACE(B4;1;6;””);2;5;”-“): ở hàm này thực hiện 2 chức năng là loại bỏ và thay thế. Khi bạn sử dụng dấy “” vào new_text thì sẽ loại bỏ đươc ký tự chỉ định. Trong hàm này, mình cũng sử dụng 2 hàm REPLACE thì hàm REPLACE trong cùng sẽ thực hiện trước và kết quả của hàm REPALCE đó sẽ là old_text của hàm REPLACE phía trước nó. Để hiểu rõ hơn khi sử dụng nhiều hàm REPLACE, các bạn tham khảo ví dụ bên dưới:
Như ví dụ trên, khi sử dụng nhiều hàm REPLACE, thì bạn sẽ phải xác định lại giá trị start_num cho hàm trước đó thì kết quả mới chính xác. - REPLACE(REPLACE(B5;3;0;”/”);6;0;”/”): tương tự như ví dụ thứ 3. Tuy nhiên trong ví dụ này, ở num_chars mình để là 0, điều này có nghĩa là sẽ thêm new_text vào old_text theo vị trí xác định tại start_num.
- REPLACE(B6;FIND(“@gmail”;B6);6;”******”): kết hợp sử dụng thêm hàm FIND để tìm kiếm tất cả các văn bản được chỉ định trong hàm . Lưu ý: khi sử dụng cú pháp này thì new_text sẽ thay thế vào tất cả vị trí mà hàm FIND trả về.
- DATEVALUE(REPLACE(TEXT(B7;”dd-mm-yyyy”);4;2;5)): và ở ví dụ này, thì mình sử dụng thêm hàm TEXT và hàm DATEVALUE để thay đổi một trong các chỉ số thời gian như ngày tháng hoặc năm.
Cách sử dụng hàm SUBSTITUDE
Mô tả:
Hàm REPLACE sẽ thay thế văn bản mới vào văn bản cũ theo vị trí được chỉ định trong hàm. Còn hàm SUBSTITUDE cũng sẽ thay thế văn bản mới vào văn bản cũ nhưng sẽ thay thế vào vị trí có văn bản giống với văn bản được so sánh.
Cú pháp:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
➤ Trong đó:
- text: là văn bản hay tham chiếu đến một Cells
- old_text: là văn bản trong văn bản cũ mà bạn muốn thay thế.
- new_text: văn bản mới mà bạn muốn thay thế vào old_text
- [instance-num]: xác định vị trí xuất hiện của old_text. Nếu không sử dụng thì new_text sẽ thay thế vào tất cả old_text xuất hiện trong text, nếu >0 thì sẽ thay thế vào vị trí xác định.
Ví dụ:
- Cách sử dụng [instance-num] trong hàm SUBSTITUDE
Trong 2 ví dụ trên, bạn sẽ thấy được cách thực hiện của [instance-num]. Ví dụ 1, thì mình không sử dụng tham số [instance-num] nên tất cả x sẽ được thay thế vào -. Và trong ví dụ 2, thì mình xác định chỉ thay từ và vào dấu – thứ 4. - Cách thức thực hiện nhiều hàm SUBSTITUDE
Kết hợp nhiều hàm SUBSTITUDE, thì kết quả của hàm SUBSTITUDE trong cùng sẽ thực hiện trước và kết quả sẽ là text của hàm SUBSTITUDE phía trước nó. - Kết hợp sử dụng với các hàm khác nhau trong hàm SUBSTITUDE
Và trong ví dụ này, text là (26-03-2018) có 2 dấu “–” và mình muốn thay dấu “-” thứ nhất thành tháng và dấu – thứ 2 thành năm thì sau khi sau khi thực hiện hàm SUBSTITUDE bên trong thì kết quả là (ngày 26 tháng 03-2018). Chính vì vậy mà hàm SUBSTITUDE còn lại thì [instance-num] mình để là 1 chứ không phải là 2.
Kết luận
Cả 2 hàm REPLACE và SUBSTITUDE đều có chức năng là thay thế văn bản trong Excel. Tuy vậy, mỗi hàm lại có những đặc trưng riêng và sẽ phù hợp với từng tình huống khác nhau. Thông qua các ví dụ mình nêu ra, hy vọng sẽ giúp các bạn giải quyết được tình huống đang gặp phải.
Chúc các bạn một ngày vui vẻ!
