Bạn đang xem bài viết Cách Sử Dụng Hàm Replace Để Thay Thế Đoạn Văn Bản Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Đăng ký ngay khóa học Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ
Cú pháp hàm REPLACE trong Excel
Hàm REPLACE trong Excel là một hàm chuyên sử dụng khi cần thay thế một số ký tự hoặc đoạn văn bản trong Excel, với số lượng ký tự bị thay thế do chính bạn thiết lập.
Hàm REPLACE có cú pháp như sau:
=REPLACE(old_text,start_num,num_charts,new_text)
Trong đó
– old_text là phần văn bản mà bạn cần xử lý, bạn có thể nhập toàn bộ nội dung của văn bản có ký tự cần thay thế đó hoặc điền ô địa chỉ của văn bản đó để hàm REPLACE tham chiếu ô trong Excel.– start_num là tham số chỉ vị trí mà văn bản thay thế sẽ được điền vào, tính từ trái sang phải. – num_charts là tham số chỉ số lượng ký tự trong văn bản gốc sẽ bị thay thế bởi văn bản mới– new_text là phần nội dung văn bản mới mà bạn muốn thay thế vào văn bản gốc. Bạn cũng có thể nhập trực tiếp nội dung vào công thức hoặc viết nội dung ra một ô khác, và điền địa chỉ ô tham chiếu vào công thức.
Ví dụ ứng dụng hàm REPLACE trong Excel
Để nắm rõ cách hoạt động của hàm REPLACE trong Excel cũng như hiểu rõ hơn hai tham số start_num và num_charts, chúng ta sẽ đến với một ví dụ cụ thể như sau
Chúng ta có bảng sau, giả sử họ tên của học sinh ở ô A7 ” Nguyễn Thái Anh” đang bị sai. Chúng ta sẽ thay thế họ của học sinh này sang họ “Lương“. Áp dụng vào công thức hàm chúng ta có:
Như vậy các bạn sẽ được kết quả là Họ tên mới đã được thay thế của học sinh này.
Tuy nhiên đây là cách làm cho những văn bản ngắn, dễ đếm thủ công. Vậy với những văn bản dài thì sao?
Kết hợp hàm REPLACE với hàm FIND và LEN để đếm số ký tự trong Excel
Chúng ta sẽ dùng hàm FIND để xác định đoạn văn bản cần thay thế, cũng chính là xác định vị trí bắt đầu được điền vào của văn bản mới. Sau đó dùng hàm LEN để xác định số ký tự sẽ bị thay thế.
Với cách kết hợp 2 hàm này, bạn sẽ cần nắm rõ nội dung đoạn văn bản cần thay thế, để có thể điền trực tiếp văn bản đó vào công thức mà không cần đếm ký tự. Hoặc bạn cũng nhập văn bản cần thay thế ra một ô khác và điền địa chỉ tham chiếu của ô đó.
Công thức trên được hiểu như sau
– A8 là vị trí địa chỉ ô tham chiếu của văn bản cần xử lý, ở đây là họ tên Trần Huyền Trang– FIND(“Huyền”,A8) là hàm để tìm giá trị “Huyền” trong ô A8, khi tìm giá trị này nằm ở đâu, văn bản mới sẽ được thay thế vào vị trí đó.– LEN(“Huyền”) là hàm để đếm số lượng ký tự của văn bản cần thay thế. Hàm sẽ đếm cho chúng ta văn bản cần thay thế có bao nhiêu ký tự.
Và cuối cùng chúng ta có kết quả như trên. Việc kết hợp hai hàm tìm kiếm và đếm số ký tự này sẽ giúp bạn không cần phải đếm thủ công số lượng ký tự cũng như vị trí bắt đầu của văn bản mới trong trường hợp chuỗi văn bản quá dài nữa. Thật tiện phải không nào.
HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾHướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Hướng dẫn cách sắp xếp ngày trong Excel
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
Cách Dùng Hàm Replace Để Thay Thế Một Phần Của Chuỗi Văn Bản
Hàm REPLACE được sử dụng trong Excel để thay thế một phần của chuỗi văn bản bằng một chuỗi văn bản khác. Ví dụ bạn cần thay thế một đoạn 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ì hàm REPLACE sẽ giúp bạn làm điều đó. Bài viết sau đây sẽ hướng dẫn các bạn cách sử dụng hàm REPLACE trong Excel.
1. Cấu trúc hàm REPLACE
Cú pháp hàm: =REPLACE(old_text; start_num; num_chars; new_text)
Old_text: đối số bắt buộc, là văn bản hoặc tham chiếu đến một văn bản mà bạn muốn thay thế ký tự trong đó.
Start_num: đối số bắt buộc, là vị trí ký tự đầu tiên trong old_text mà bạn muốn thay thế bằng văn bản mới.
Num_chars: đối số bắt buộc, là số lượng ký tự trong old_text mà bạn muốn thay thế.
New_text: đối số bắt buộc, là văn bản sẽ thay thế cho old_text. Nếu viết thẳng new_text vào hàm thì cần để trong dấu nháy kép””.
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!.
Hàm REPLACE luôn trả kết quả là chuỗi văn bản chứ không phải số. 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.
2. Cách sử dụng hàm REPLACE
Ví dụ ta cần thay thế các văn bản theo các yêu cầu như sau:
=REPLACE(A3;7;1;”n”)
Thay thế Bia loa thành Bia lon
Thay thế Nước khoáng thành Nước tinh khiết
Thay thế Năm 2019 thành Năm 2020
Thay thế 0981421689 thành 0981.421.689
Áp dụng cấu trúc hàm như trên, ta có công thức để thay thế Bia loa thành Bia lon như sau:
=REPLACE(A4;6;6;”tinh khiết”)
Ta có công thức để thay thế Nước khoáng thành Nước tinh khiết như sau:
Ta có công thức để thay thế Năm 2019 thành Năm 2020 như sau:
=REPLACE(A5;7;2;20)
Để thay thế 0981421689 thành 0981.421.689, ta có công thức dùng 2 hàm REPLACE liên tiếp nhau như sau:
Sử Dụng Hàm Replace Và Substitute Trong Excel
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 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 SUBSTITUTE.
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ự.
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:
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ụ:
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”).
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 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 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:
= 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 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:
= 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:
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ố start_num 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 #VALUE!:
= 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 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.
= 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”.
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 , 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.
= SUBSTITUTE (A2, “PR”, “Project “) = SUBSTITUTE (A2, “ML”, “Milestone “) = SUBSTITUTE (A2, “T”, “Task “)
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 (SUBSTITUTE (SUBSTITUTE (A2, “PR”, “Project “), “ML”, “Milestone “), “T”, “Task “)
Và sau đó lồng với nhau trong 1 công thức:
Các hàm REPLACE và 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 .
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.
Hướng Dẫn Sử Dụng Chức Năng Hàm Find And Replace Trong Excel
Bạn nhập vào chữ a và bấm nút Find Next thì Excel sẽ tìm đến ô đầu tiên chứa chữ a nếu tìm thấy.
I. Các kiểu giới hạn tìm kiếm hàm find and replace trong Excel
Excel có khả năng phân biệt các ký tự, từ hoặc cả cụm từ trong cell dựa theo định dạng màu sắc của chữ, màu sắc của ô, các kiểu in đậm, in nghiêng hay gạch chân của chúng trong cell đó.
Tuy nhiên, khi bạn muốn giới hạn nhỏ hơn nữa khi có nhiều hơn 1 ô có cùng định dạng nhưng chứa text khác nhau thì ta nhập text cần tìm vào ô Find What để tìm. Lúc này Excel sẽ loại bỏ các ô có cùng định dạng nhưng không chứa text giống như text trong hộp Find What.
2. Giới hạn theo khu vực tìm kiếm
Như hình trên sau khi mở rộng Option tìm kiếm thì ta thấy có các tiêu chí giới hạn sau:
Option: Within: Có 2 tùy chọn là Sheet và Workbook. Khi bạn chọn Sheet thì Excel chỉ tìm kiếm trong khu vực của Sheet hiện hành, ngược lại nó sẽ tìm kiếm trên toàn file nếu bạn chọn Workbook. Option: Search: nhằm giới hạn tìm kiếm theo hàng (By Row) hoặc theo cột (Column).
Ngoài ra ta có thêm 2 tùy chọn nhỏ nữa là:
Match case: cho phép tìm kiếm ký tự chính xác theo kiểu định dạng viết hoa hay viết thường. Match entire cell contents: tìm kiếm chính xác tất cả nội dung trong cell.
II. Chức năng Replace
Để thực hiện chức năng Replace ta chỉ cần bấm vào nút để gọi thêm box để thiết lập giá trị cần thay thế cho kết quả tìm được ở chức năng Find trong excel như hình bên dưới.
Sau khi thiết lập chức năng Find, ta có thể thực hiện tìm kiếm (Find hoặc Find All) trước rồi bấm nút Replace để tiến hành thay thế. Hoặc chúng ta có thể bấm nút Replace hoặc Replace All luôn. Chức năng Replace trong excel là dùng để thay thế text trực tiếp trong ô, còn Hàm Substitute hoặc Hàm Replace mà chúng ta đã học trước đây cũng dùng để thay thế nhưng trả về kết quả ở ô khác (ô chứa công thức được thiết lập).
Cập nhật thông tin chi tiết về Cách Sử Dụng Hàm Replace Để Thay Thế Đoạn Văn Bản Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!