Sử Dụng Hàm REPLACE Và SUBSTITUTE Trong Excel – Công Thức Ví ...

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

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

Sử dụng hàm REPLACE 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”)

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

Xem thêm: Tổng hợp các thủ thuật Excel hay

Sử dụng hàm REPLACE 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”))

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

Đăng ký ngay: Khóa học Word Excel cơ bản

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, “:”)

Thay thế một chuỗi xuất hiện ở vị trí khác nhau trong mỗi ô:

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 REPLACE. Để tìm ra nó, sử dụng hàm FIND để 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 FIND ở trên trong đối số start_num của công thức REPLACE:

= REPLACE (A2, FIND(“@ abc”, A2), 4 “@bca”)

Mẹo. Chúng tôi bao gồm “@” trong công thức FIND và REPLACE để 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 #VALUE!:

Và chúng tôi muốn công thức trả về địa chỉ thư điện tử gốc thay vì lỗi. Vì vậy, chúng ta hãy đính kèm công thức FIND & REPLACE của chúng ta trong hàm IFERROR:

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

Mẹo. Nếu bạn muốn thay thế các dữ liệu ban đầu, có một cách dễ dàng hơn là sử dụng hộp thoại FIND và REPLACE của Excel.

Hàm SUBSTITUTE trong Excel

Hàm SUBSTITUTE 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 SUBSTITUTE như sau:

SUBSTITUTE (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.

• Text– 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.
• Old_text – ký tự mà bạn muốn thay thế.
• New_text – ký tự mới để thay thế old_text.
• Instance_num – 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.

Ví dụ, tất cả các công thức dưới đây thay thế “1” bằng “2” trong ô A2, nhưng trả lại các kết quả khác nhau tùy thuộc vào số bạn cung cấp trong đối số cuối cùng:

= 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”.

Chú ý. Hàm SUBSTITUTE trong Excel phân biệt chữ hoa chữ thường. Ví dụ, công thức sau đây sẽ thay thế tất cả các trường hợp chữ hoa “X” bằng chữ “Y” trong ô A2, nhưng nó sẽ không thay thế bất kỳ trường hợp nào của chữ thường “x”.

Thay thế các ký tự khác nhau bằng một công thức (lồng các hàm SUBSTITUTE)

Như trường hợp với hàm REPLACE, bạn có thể lồng các SUBSTITUTE 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 SUBSTITUTE 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ố new_text để có thể đọc tốt hơn.

Hàm REPLACE và SUBSTITUTE

Các hàm REPLACE và hàm SUBSTITUTE của Excel rất giống nhau và cả hai đều được thiết kế để thay đổi chuỗi văn bản. Sự khác biệt của hai hàm như sau:

• SUBSTITUTE 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 SUBSTITUTE.
• 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 REPLACE.
• Hàm SUBSTITUTE cho phép thêm một tham số tùy chọn (instance_num) xác định vị trí thay thế của old_text thành new_text.

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học.