Sử dụng hàm REPLACE trong Excel – công thức ví dụ

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Sử dụng hàm REPLACE trong Excel – công thức ví dụ

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Trong bài viết này, sẽ giải thích hàm REPLACE trong Excel với các ví dụ. Cách dùng hàm REPLACE trong Excel với chuỗi văn bản, số và ngày, và làm thế nào để lập một công thức với nhiều hàm REPLACE

1. Hàm Replace trong Excel

Hàm REPLACE trong Excel cho phép bạn thay đổi một hoặc nhiều ký tự trong một chuỗi văn bản với một ký tự khác hoặc một chuỗi các ký tự.

REPLACE (old_text, start_num, num_chars, new_text)

Như bạn thấy, hàm REPLACE có 4 đối số, tất cả đều được yêu cầu.

  • Old_text – văn bản gốc (hoặc tham chiếu đến ô có văn bản gốc) mà bạn muốn thay thế một số ký tự.
  • Start_num – vị trí của ký tự đầu tiên trong old_text mà bạn muốn thay thế.
  • Num_chars – số ký tự mà bạn muốn thay thế.
  • New_text – văn bản thay thế.

Ví dụ: để thay đổi từ “sun” thành “son”, bạn có thể sử dụng công thức sau:

= REPLACE (“sun”, 2, 1, “o”)

Và nếu bạn đặt từ muốn thay trong một ô, ví như ô A2, bạn có thể cung cấp ô tham chiếu tương ứng chứa old_text:

= REPLACE (A2, 2, 1, “o”)

 

Chú ý: Nếu đối số start_num hoặc num_chars là số âm hoặc không phải số, hàm Replace trả kết quả lỗi #VALUE!.

2. Sử dụng hàm REPLACE trong Excel với các giá trị số

Hàm REPLACE được thiết kế để làm việc với chuỗi văn bản. Tất nhiên, bạn có thể sử dụng nó để thay thế các ký tự số là một phần của một chuỗi văn bản, ví dụ:

= REPLACE (A2, 7, 4, “2016”)

Sử dụng hàm REPLACE trong Excel với các giá trị số

Sử dụng hàm REPLACE trong Excel với các giá trị số

Lưu ý rằng chúng tôi đặt “2016” vào dấu nháy kép vì nó là giá trị văn bản.

Theo cách tương tự, bạn có thể thay thế một hoặc nhiều chữ số trong một số. Ví dụ:

= REPLACE (A4, 4, 4, “6”)

Và một lần nữa, bạn phải đặt giá trị thay thế trong dấu nháy kép (“6”).

Chú ý. Hàm REPLACE luôn trả kết quả là chuỗi văn bản chứ không phải số. Trong hình trên, chú ý đến việc căn lề bên trái của giá trị văn bản trả về trong B2, và so sánh nó với số căn lề phải trong A2. Bởi vì đó là một giá trị văn bản nên không thể sử dụng trong các phép tính trừ khi bạn chuyển nó trở lại số, ví dụ nhân với 1 hoặc bằng những phương pháp khác được mô tả trong Cách chuyển đổi văn bản sang số.

3. Sử dụng hàm REPLACE trong Excel với ngày tháng

Như bạn đã thấy, hàm REPLACE hoạt động tốt với các con số, ngoại trừ việc nó trả kết quả là chuỗi văn bản. Trong hệ thống Excel, các ngày được lưu dưới dạng số, bạn có thể thử sử dụng hàm Replace với ngày tháng. Nhưng kết quả sẽ không như mong đợi.

Ví dụ, bạn có một ngày trong A2, là 1-Oct-14, và bạn muốn thay đổi “Oct” thành “Nov”. Vì vậy, bạn viết công thức REPLACE (A2, 4, 3, “Nov”) để nói Excel thay thế 3 ký tự trong ô A2, bắt đầu với kí tự thứ 4 … và có kết quả sau đây:

Tại sao vậy? Bởi vì “01-Oct-14” chỉ là một hình ảnh đại diện của con số (41913) đại diện cho ngày tháng. Vì vậy, hàm Replace thay đổi 3 số cuối cùng thành “Nov” và trả về chuỗi văn bản “419Nov”.

Để hàm REPLACE làm việc đúng với ngày, đầu tiên bạn có thể chuyển các ngày thành chuỗi văn bản bằng cách sử dụng hàm TEXT hoặc bất kỳ phương pháp nào được trình bày trong Cách chuyển đổi ngày sang văn bản. Ngoài ra, bạn có thể đưa hàm TEXT trực tiếp vào old_text của hàm REPLACE:

= REPLACE (TEXT (A2, “dd-mmm-yy”), 4, 3, “Nov”)

Hãy nhớ rằng kết quả của công thức trên là một chuỗi văn bản, vì vậy chỉ dùng được giải pháp này nếu bạn không sử dụng các ngày đã sửa đổi để tính toán. Nếu bạn cần ngày chứ không phải là chuỗi văn bản, sử dụng hàm DATEVALUE để đưa các giá trị kết quả của hàm REPLACE trở lại dạng ngày:

= DATEVALUE (REPLACE (TEXT (A2, “dd-mmm-yy”), 4, 3, “Nov”))

4. Các hàm Replace lồng nhau để thực hiện nhiều thay thế trong một ô

Bạn thường phải thay thế nhiều đoạn trong cùng một ô. Bạn có thể sử dụng các chức năng REPLACE lồng nhau cho phép bạn thực hiện nhiều thay thế chỉ bằng một công thức. Trong ngữ cảnh này, “lồng” có nghĩa là đặt một hàm bên trong một hàm khác.

Xem xét ví dụ sau: Giả sử bạn có một danh sách các số điện thoại trong cột A được định dạng là “123456789” và bạn muốn làm cho chúng trông giống như số điện thoại bằng cách thêm các dấu nối. Nói cách khác, mục tiêu của bạn là chuyển “123456789” thành “123-456-789”.

Chèn dấu nối đầu tiên là dễ dàng. Bạn viết một công thức Replace thông thường để thay thế không ký tự nào ở vị trí thứ 4 bằng dấu gạch ngang, tức là thêm một dấu gạch nối vào vị trí thứ 4 trong ô:

= REPLACE (A2,4,0, “-“)

Kết quả của công thức Replace ở trên là như sau:

Được rồi, và bây giờ chúng ta cần chèn thêm một dấu nối vào vị trí thứ 8. Để làm điều này, bạn đặt công thức trên trong một hàm REPLACE khác. Cụ thể hơn, bạn gắn nó vào đối số old_text của hàm khác, để hàm REPLACE thứ hai xử lý giá trị trả về bởi REPLACE đầu tiên, mà không phải là giá trị trong ô A2:

= REPLACE (REPLACE (A2,4,0, “-“), 8,0, “-“)

Kết quả là, bạn sẽ nhận được số điện thoại ở định dạng mong muốn:

Theo cách tương tự, bạn có thể sử dụng các hàm REPLACE lồng vào nhau để tạo chuỗi văn bản trông giống như ngày tháng bằng cách thêm dấu gạch chéo (/) nếu thích hợp:

= (REPLACE (REPLACE (A2,3,0, “/”), 6,0, “/”))

Hơn nữa, bạn có thể chuyển đổi chuỗi văn bản thành ngày thực bằng cách lồng công thức REPLACE ở trên với hàm DATEVALUE:

= DATEVALUE (REPLACE (REPLACE (A2,3,0, “/”), 6,0, “/”))

Bạn không giới hạn số lượng hàm mà bạn có thể lồng trong một công thức (các phiên bản hiện đại của Excel 2010, 2013 và 2016 cho phép tối đa 8192 ký tự và 64 chức năng lồng vào nhau trong một công thức).

Ví dụ, bạn có thể sử dụng 3 hàm REPLACE lồng nhau để có một số trong A2 xuất hiện như ngày tháng và thời gian:

= REPLACE (REPLACE (REPLACE (REPLACE (A2,3,0, “/”), 6,0, “/”), 9,0, “”), 12,0, “:”)

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ