Làm Thế Nào Để Lập Một Công Thức Trong Excel Với Nhiều Hàm Replace Hoặc Substitute

Trong bài viết này, Học Excel Online sẽ giải thích hàm và trong Excel với các ví dụ. Cách dùng hàm 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 hoặc .

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ự.

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

• - 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ự.
• - vị trí của ký tự đầu tiên trong old_text mà bạn muốn thay thế.
• - số ký tự mà bạn muốn thay thế.
• - 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 :

= REPLACE (A2, 2, 1, "o")

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

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

Hàm đượ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, "2024")

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").

SỬ DỤNG HÀM 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 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:

Để hàm 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 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 trực tiếp vào của hàm :

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

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

CÁC HÀM 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 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 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:

= 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:

= (REPLACE (REPLACE (A2,3,0, "/"), 6,0, "/"))

= 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à 2024 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 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, ":")

Cho đến nay, trong tất cả các ví dụ chúng ta đã làm việc với các giá trị có tính chất giống nhau và đã thay thế ở cùng một vị trí trong mỗi ô. Nhưng công việc thực tế thường phức tạp hơn thế. Trong bảng tính của bạn, các ký tự được thay thế có thể không nhất thiết xuất hiện ở cùng một nơi trong mỗi ô và do đó bạn sẽ phải tìm vị trí của ký tự đầu tiên cần được thay thế. Ví dụ sau đây sẽ chứng minh những điều đó.

Giả sử bạn có một danh sách địa chỉ email trong cột A. Và tên miền của một công ty đã thay đổi từ "ABC" sang, nói "BCA". Vì vậy, bạn phải cập nhật tất cả địa chỉ email của khách hàng theo.

Nhưng vấn đề là tên khách hàng có chiều dài khác nhau, và đó là lý do tại sao bạn không thể xác định chính xác vị trí bắt đầu của tên công ty. Nói cách khác, bạn không biết giá trị cung cấp trong đối số start_num của hàm . Để tìm ra nó, sử dụng hàm để xác định vị trí của kí tự đầu tiên trong chuỗi "@abc":

= FIND("@ abc", A2)

Và sau đó, cung cấp hàm ở trên trong đối số của công thức :

= REPLACE (A2, FIND("@ abc", A2), 4 "@bca")

Mẹo. Chúng tôi bao gồm "@" trong công thức và để tránh thay thế tình cờ trong phần tên của địa chỉ thư điện tử. Tất nhiên, khả năng này xảy ra rất ít.

Như bạn thấy trong hình sau, công thức không có vấn đề gì khi tìm và thay thế văn bản cũ bằng cái mới. Tuy nhiên, nếu chuỗi văn bản được thay thế không được tìm thấy, công thức trả về kết quả lỗi :

= IFERROR (REPLACE (A2, FIND ("@ abc", A2), 4 "@bca"), A2)

Và công thức cải tiến này hoạt động khá hoàn hảo, phải không?

Hàm trong Excel

Hàm trong Excel thay thế một hoặc nhiều kí tự cho trước tại một hoặc nhiều vị trí bằng kí tự cụ thể.

Cú pháp của hàm như sau:

text, old_text, new_text, [instance_num])

Ba đối số đầu tiên được yêu cầu và đối số cuối cùng là tùy chọn.

• - văn bản ban đầu mà bạn muốn thay thế các ký tự. Có thể được cung cấp như là một chuỗi, ô tham chiếu, hoặc kết quả của một công thức khác.
• - ký tự mà bạn muốn thay thế.
• - ký tự mới để thay thế old_text.
• - vị trí thực hiện thay thế old_text. Nếu bỏ qua, mỗi lần văn bản cũ xuất hiện sẽ được thay đổi thành văn bản mới.

= SUBSTITUTE (A2, "1", "2", 1) - Thay thế sự xuất hiện đầu tiên của "1" bằng "2".

= SUBSTITUTE (A2, "1", "2", 2) - Thay thế lần xuất hiện thứ hai của "1" bằng "2".

= SUBSTITUTE (A2, "1", "2") - Thay thế tất cả các lần xuất hiện của "1" bằng "2".

Như trường hợp với hàm , bạn có thể lồng các trong một công thức để thực hiện một số thay thế cùng lúc, tức là thay thế nhiều ký tự bằng một công thức.

Giả sử bạn có một chuỗi văn bản như "PR1, ML1, T1" trong ô A2, trong đó "PR" là viết tắt của "Project", ML "viết tắt của" Milestone "và" T "có nghĩa là" Task ". Bạn muốn thay thế 3 ba mã này với tên đầy đủ. Để đạt được điều này, bạn có thể viết 3 công thức khác nhau:

= SUBSTITUTE (A2, "PR", "Project ")

= SUBSTITUTE (A2, "ML", "Milestone ")

= SUBSTITUTE (A2, "T", "Task ")

Và sau đó lồng với nhau trong 1 công thức:

= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A2, "PR", "Project "), "ML", "Milestone "), "T", "Task ")

Lưu ý rằng chúng ta thêm một dấu cách vào cuối mỗi đối số để có thể đọc tốt hơn.

• thay thế một hoặc nhiều vị trí của kí tự hoặc chuỗi văn bản nhất định. Vì vậy, nếu bạn chỉ biết đoạn cần thay thế, hãy sử dụng hàm .
• REPLACE thay thế các ký tự tại một vị trí xác định của một chuỗi văn bản. Vì vậy, nếu bạn biết vị trí của kí tự được thay thế, sử dụng hàm .
• Hàm cho phép thêm một tham số tùy chọn (instance_num) xác định vị trí thay thế của thành

#quantriexcel #kynangmoi