Trong bài viết này, EDUSA sẽ giải thích hàm Excel thay thế ký tự với 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ãy cùng theo dõi bài viết dưới đây nhé.
1. Hàm Excel thay thế ký tự – Hàm REPLACE
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ự.
Công thức: =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”)
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!.
Xem thêm: Sử dụng hàm Excel ghép chuỗi đơn giản nhất
2. 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 EDUSA đặ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: Cách làm trang tính Excel online trên máy tính
3. 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,…
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”))
Xem thêm: Hướng dẫn dùng hàm Excel bỏ dấu đơn giản nhất
4. Hàm Excel thay thế ký tự – Hàm SUBSTITUTE
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”.
Xem thêm: Cách dùng công thức Excel lọc dữ liệu đơn giản và dễ hiểu
5. 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.
Xem thêm: Cách dùng công thức Excel hàm ROUND dễ hiểu nhất
6. 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.
Xem thêm: Công thức tính số ngày trong Excel nhanh nhất
7. Nên học Excel ở đâu uy tín?
Hiện nay, có rất nhiều trung tâm tin học được mở ra nhằm phục vụ những nhu cầu của các bạn. Tuy nhiên, mỗi trung tâm lại có chất lượng giảng dạy khác nhau, và theo sau đó là những khóa học với chi phí và ưu đãi khác nhau.
Đến với trung tâm EDUSA, bạn sẽ được tư vấn kỹ lưỡng về những khoá học phù hợp với bạn nhất. Bên cạnh đó, những mức phí của khóa học đều nằm ở mức trung bình giúp bạn có thể chi trả một cách dễ dàng. Mặc dù mức phí khá thấp nhưng chất lượng giảng dạy tại EDUSA vô cùng tuyệt vời, đội ngũ giáo viên có chuyên môn cao, nhiệt tình giảng dạy các bạn học viên.
Và đặc biệt hơn, EDUSA sẽ cung cấp một vài buổi học và thi lại miễn phí cho các bạn nếu các bạn thi không đạt chứng chỉ ở lần thi đầu tiên. Thật tuyệt vời phải không nào? Hãy đến trung tâm EDUSA để được tư vấn kỹ hơn nhé.
Xem thêm: Khóa học Excel Offline chất lượng tại EDUSA
Xem thêm: Khóa học Excel Online chất lượng tại EDUSA
8. Câu hỏi thường gặp
8.1 Thời gian học Excel tại EDUSA có linh động không?
100% thời gian học dù là online hay offline đều linh động theo lịch rảnh cá nhân của học viên, đảm bảo được sự thoải mái xuyên suốt quá trình học.
8.2 Khóa học Excel tại EDUSA có bao đậu không?
Khóa học của EDUSA đều sát với đề thi thật, bài tập và đề thi thử được cập nhật thường xuyên. Với chính sách cam kết đầu ra 100% bằng văn bản cụ thể sẽ giúp học viên yên tâm trong suốt quá trình học tại đây.
8.3 EDUSA có gì khác biệt so với các trung tâm luyện thi Excel khác hay không?
Học viên có thể hoàn toàn yên tâm và tin tưởng khi lựa chọn luyện thi Excel tại lớp. Tất cả đều được chúng tôi thể hiện qua văn bản rõ ràng khi thực hiện cam kết với học viên.
9. Lời kết
Hy vọng những thông tin trên sẽ giúp bạn biết cách sử dụng các hàm Excel thay thế ký tự. Nếu bạn có thắc mắc gì thì bạn có thể liên hệ trực tiếp với EDUSA để được tư vấn thêm nhé.
