Hướng dẫn which function is used to replace a part of a text in a cell in excel? - chức năng nào được sử dụng để thay thế một phần văn bản trong một ô trong excel?
|
Excel cho Microsoft 365Excel cho Microsoft 365 cho MacExcel cho WebExcel 2021Excel 2021 cho MACEXCEL 2019Excel 2019 cho MacExcel 2016Excel 2016 cho MACEXCEL 2013Excel 2010Excel 2007cel cho MAC 2011Excel khởi động 2010 ... Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less Show
Bài viết này mô tả cú pháp công thức và sử dụng của thay thế và thay thếb & nbsp; hàm trong Microsoft Excel.REPLACE and REPLACEB function in Microsoft Excel. Sự mô tảThay thế thay thế một phần của chuỗi văn bản, dựa trên số lượng ký tự bạn chỉ định, bằng một chuỗi văn bản khác. Thay thếb thay thế một phần của chuỗi văn bản, dựa trên số byte bạn chỉ định, bằng một chuỗi văn bản khác. Important:
Các ngôn ngữ hỗ trợ DBC bao gồm Nhật Bản, tiếng Trung (đơn giản hóa), tiếng Trung (truyền thống) và tiếng Hàn. Cú phápThay thế (old_text, start_num, num_chars, new_text) Thay thếb (old_text, start_num, num_bytes, new_text) Cú pháp chức năng thay thế và thay thếB có các đối số sau:
Thí dụSao chép dữ liệu ví dụ trong bảng sau và dán nó vào ô A1 của bảng tính Excel mới. Để các công thức hiển thị kết quả, chọn chúng, nhấn F2, sau đó nhấn Enter. Nếu bạn cần, bạn có thể điều chỉnh chiều rộng cột để xem tất cả dữ liệu.
Cần sự giúp đỡ nhiều hơn?Hướng dẫn giải thích các chức năng thay thế và thay thế Excel với các ví dụ về việc sử dụng. Xem cách sử dụng hàm thay thế với các chuỗi văn bản, số và ngày và cách làm tổ một số hàm thay thế hoặc thay thế trong một công thức. Tuần trước chúng tôi đã thảo luận về nhiều cách khác nhau để sử dụng các chức năng tìm kiếm và tìm kiếm trong bảng tính Excel của bạn. Hôm nay, chúng tôi sẽ xem xét sâu hơn hai chức năng khác để thay thế văn bản trong một ô dựa trên vị trí của nó hoặc thay thế một chuỗi văn bản bằng một chuỗi khác dựa trên nội dung. Như bạn có thể đoán, tôi đang nói về các chức năng thay thế và thay thế Excel. Chức năng thay thế ExcelHàm thay thế trong Excel cho phép bạn trao đổi một hoặc một số ký tự trong chuỗi văn bản với một ký tự khác hoặc một bộ ký tự. Thay thế (old_text, start_num, num_chars, new_text) Như bạn thấy, hàm thay thế Excel có 4 đối số, tất cả đều được yêu cầu.
Ví dụ: để thay đổi từ "mặt trời" thành "con trai", bạn có thể sử dụng công thức sau:
Và nếu bạn đặt từ gốc vào một số ô, nói A2, bạn có thể cung cấp tham chiếu ô tương ứng trong đối số Old_Text:
Ghi chú. Nếu đối số start_num hoặc num_chars là âm hoặc không phải là số, một công thức thay thế excel sẽ trả về #Value! lỗi. If the start_num or num_chars argument is negative or non-numeric, an Excel Replace formula returns the #VALUE! error. Sử dụng chức năng thay thế Excel bằng các giá trị sốChức năng thay thế trong Excel được thiết kế để hoạt động với các 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 chuỗi văn bản, ví dụ:
 Lưu ý rằng chúng tôi gửi kèm "2016" trong các trích dẫn kép như bạn thường làm với các 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ụ:
Và một lần nữa, bạn phải đặt giá trị thay thế trong các trích dẫn kép ("6"). Ghi chú. Công thức thay thế Excel luôn trả về chuỗi văn bản, không phải số. Trong ảnh chụp màn hình ở trên, hãy chú ý căn chỉnh bên trái của giá trị văn bản được trả về trong B2 và so sánh nó với số gốc được liên kết bên phải trong A2. Và bởi vì đó là giá trị văn bản, bạn sẽ không thể sử dụng nó trong các tính toán khác trừ khi bạn chuyển đổi nó thành số, ví dụ bằng cách nhân với 1 hoặc bằng cách sử dụng bất kỳ phương thức nào khác được mô tả trong cách chuyển đổi văn bản thành số. An Excel REPLACE formula always returns a text string, not number. In the screenshot above, notice the left alignment of the returned text value in B2, and compare it to the right-aligned original number in A2. And because it's a text value you won't be able to use it in other calculations unless you convert it back to number, for example by multiplying by 1 or by using any other method described in How to convert text to number. Sử dụng chức năng thay thế excel với ngàyNhư bạn vừa thấy, hàm thay thế hoạt động tốt với các số, ngoại trừ việc nó trả về chuỗi văn bản :) Nhớ rằng trong hệ thống Excel bên trong, ngày được lưu trữ dưới dạng số, bạn có thể cố gắng sử dụng một số công thức thay thế vào ngày. Kết quả sẽ khá xấu hổ. Chẳng hạn, bạn có một ngày trong A2, nói 1-tháng 10-14 và bạn muốn thay đổi "tháng 10" thành "tháng 11". Vì vậy, bạn viết công thức thay thế (A2, 4, 3, "NOV") bảo Excel thay thế 3 ký tự trong các ô A2 bắt đầu bằng Char thứ 4 và có kết quả sau: Tại sao vậy? Bởi vì "01-tháng 10-14" chỉ là một biểu diễn trực quan của số sê-ri cơ bản (41913) đại diện cho ngày. Vì vậy, công thức thay thế của chúng tôi thay đổi 3 chữ số cuối cùng trong số sê -ri trên thành "NOV" và trả lại chuỗi văn bản "419Nov". Để có chức năng thay thế Excel để hoạt động chính xác với ngày, bạn có thể chuyển đổi ngày thành chuỗi văn bản trước bằng cách sử dụng chức năng văn bản hoặc bất kỳ kỹ thuật nào khác được trình bày trong cách chuyển đổi ngày sang văn bản trong Excel. Ngoài ra, bạn có thể nhúng chức năng văn bản trực tiếp vào đối số cũ của hàm thay thế:
Xin nhớ rằng kết quả của công thức trên là một chuỗi văn bản và do đó, giải pháp này chỉ hoạt động nếu bạn không có kế hoạch sử dụng các ngày sửa đổi trong các tính toán thêm. Nếu bạn cần ngày thay vì chuỗi văn bản, hãy sử dụng hàm DateValue để biến các giá trị được trả về bởi hàm thay thế Excel trở lại ngày:text string, and therefore this solution works only if you are not planning to use the modified dates in further calculations. If you do need dates rather than text strings, use the DATEVALUE function to turn the values returned by the Excel REPLACE function back to dates:
Lồng nhau thay thế các chức năng để thực hiện nhiều thay thế trong một ôRất thường xuyên, bạn có thể cần phải làm nhiều hơn một thay thế trong cùng một ô. Tất nhiên, bạn có thể thực hiện một thay thế, xuất kết quả trung gian vào một cột bổ sung, sau đó sử dụng lại chức năng thay thế. Tuy nhiên, một cách tốt hơn và chuyên nghiệp hơn là sử dụng các chức năng thay thế lồng nhau cho phép bạn thực hiện một số thay thế với một công thức duy nhất. Trong bối cảnh này, "làm tổ" có nghĩa là đặt một chức năng trong một hàm khác.nested REPLACE functions that let you perform several replacements with a single formula. In this context, "nesting" means placing one function within another. Xem xét các 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 dấu gạch nối. Nói cách khác, mục tiêu của bạn là biến "123456789" thành "123-456-789". Chèn dấu gạch nối đầu tiên là dễ dàng. Bạn viết một công thức thay thế Excel thông thường thay thế các ký tự bằng không bằng dấu gạch nối, tức là thêm dấu gạch nối ở vị trí thứ 4 trong một ô:zero characters with a hyphen, i.e. adds a hyphen in the 4th position in a cell:
Kết quả của công thức thay thế ở trên như sau: Được rồi, và bây giờ chúng ta cần chèn thêm một dấu gạch nối ở vị trí thứ 8. Để làm điều này, bạn đặt công thức trên trong một chức năng thay thế Excel khác. Chính xác hơn, bạn đã nhúng nó vào đối số Old_Text của hàm khác, để hàm thay thế thứ hai sẽ xử lý giá trị được trả về bởi thay thế đầu tiên và không phải là giá trị trong ô A2:old_text argument of the other function, so that the second REPLACE function will handle the value returned by the first REPLACE, and not the value in cell A2:
Kết quả là, bạn nhận được số điện thoại trong định dạng mong muốn: Theo cách tương tự, bạn có thể sử dụng các hàm thay thế lồng nhau để làm cho các chuỗi văn bản trông giống như ngày bằng cách thêm một dấu gạch chéo phía trước (/) khi thích hợp:
Hơn nữa, bạn có thể chuyển đổi các chuỗi văn bản thành ngày thực bằng cách gói công thức thay thế ở trên bằng hàm DateValue:
Và một cách tự nhiên, bạn không bị giới hạn về số lượng các chức năng bạn có thể làm tổ trong một công thức (phiên bản hiện đại của Excel 2010, 2013 và 2016 cho phép lên tới 8192 ký tự và tối đa 64 hàm lồng nhau trong một công thức). Ví dụ: bạn có thể sử dụng 3 chức năng thay thế lồng nhau để có một số trong A2 xuất hiện như ngày và giờ:
Thay thế một chuỗi xuất hiện ở một vị trí khác nhau trong mỗi ôCho đến nay, trong tất cả các ví dụ chúng tôi đã xử lý các giá trị có tính chất tương tự và đã thay thế ở cùng một vị trí trong mỗi ô. Nhưng các nhiệm vụ ngoài đời thực 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 phải xuất hiện ở cùng một vị trí 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 gì tôi đang nói.find the position of the first character that should be replaced. The following example will demonstrate what I'm talking about. Giả sử bạn có một danh sách địa chỉ email trong cột A. và tên của một công ty đã thay đổi từ "ABC" thành, nói, "BCA". Vì vậy, bạn phải cập nhật tất cả các địa chỉ email của khách hàng phù hợp. Nhưng vấn đề là tên máy khách có độ dài khác nhau và đó là lý do tại sao bạn không thể chỉ định chính xác nơi tên công ty bắt đầu. Nói cách khác, bạn không biết cung cấp giá trị nào trong đối số start_num của hàm thay thế excel. Để tìm ra nó, hãy sử dụng chức năng Find Excel để xác định vị trí của char đầu tiên trong chuỗi "@ABC":
Và sau đó, cung cấp chức năng tìm trên trong đối số start_num của công thức thay thế của bạn:
Mẹo. Chúng tôi bao gồm "@" trong công thức tìm và thay thế excel của chúng tôi để tránh thay thế tình cờ trong tên một phần của địa chỉ email. Tất nhiên, có một cơ hội rất mỏng rằng các trận đấu như vậy sẽ xảy ra, và bạn vẫn có thể muốn ở bên an toàn. We include "@" in our Excel Find and Replace formula to avoid accidental replacements in the name part of email addresses. Of course, there's a very slim chance that such matches will occur, and still you may want to be on the safe side. Như bạn thấy trong ảnh chụp màn hình sau, công thức không có vấn đề gì với việc tìm và thay thế văn bản cũ bằng văn bản mới. Tuy nhiên, nếu không tìm thấy chuỗi văn bản được thay thế, công thức sẽ trả về #Value! lỗi: Và chúng tôi muốn công thức trả về địa chỉ email ban đầu thay vì lỗi. Vì vậy, hãy gửi kèm công thức tìm và thay thế của chúng tôi trong hàm iferror:
Và công thức cải tiến này hoạt động hoàn hảo, phải không? Một ứng dụng thực tế khác của chức năng thay thế là tận dụng chữ cái đầu tiên trong một ô. Bất cứ khi nào bạn xử lý một danh sách các tên, sản phẩm và tương tự, bạn có thể sử dụng công thức liên kết ở trên để thay đổi chữ cái đầu tiên thành chữ hoa. Mẹo. Nếu bạn muốn thực hiện các thay thế trong dữ liệu gốc, một cách dễ dàng hơn sẽ sử dụng hộp thoại tìm và thay thế Excel. If you want to make the replacements in the original data, an easier way would be using the Excel FIND and REPLACE dialog. Chức năng thay thế ExcelHàm thay thế trong Excel thay thế một hoặc nhiều trường hợp của một ký tự hoặc chuỗi văn bản nhất định với (các) ký tự được chỉ định. Cú pháp của hàm thay thế Excel như sau: Thay thế (văn bản, old_text, new_text, [instance_num]) Ba đối số đầu tiên được yêu cầu và câu cuối cùng là tùy chọn.
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ả về 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:
Trong thực tế, chức năng thay thế cũng được sử dụng để loại bỏ các ký tự không mong muốn khỏi các tế bào. Để biết ví dụ thực tế, vui lòng xem:
Ghi chú. Chức năng thay thế trong Excel là nhạy cảm trường hợp. Ví dụ: công thức sau đây thay thế tất cả các phiên bản của chữ "X" bằng "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". The SUBSTITUTE function in Excel is case-sensitive. For example, the following formula replaces all instances of the uppercase "X" with "Y" in cell A2, but it won't replace any instances of the lowercase "x". Thay thế nhiều giá trị bằng một công thức duy nhất (thay thế lồng nhau)Như trường hợp với chức năng thay thế Excel, bạn có thể tổ chức một số hàm thay thế trong một công thức duy nhất để thực hiện một số thay thế tại một thời điểm, tức là thay thế một số ký tự hoặc chuỗi con bằng một công thức duy nhất. 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" Ba mã có tên đầy đủ. Để đạt được điều này, bạn có thể viết 3 công thức thay thế khác nhau:
Và sau đó làm tổ chúng vào nhau:
Lưu ý rằng chúng tôi đã thêm một không gian ở cuối mỗi đối số new_text để có khả năng đọc tốt hơn. Để tìm hiểu các cách khác để thay thế nhiều giá trị tại một thời điểm, vui lòng xem cách thực hiện tìm và thay thế khối lượng trong Excel. Excel thay thế so với thay thế ExcelCác chức năng thay thế và thay thế Excel rất giống nhau ở chỗ cả hai được thiết kế để trao đổi chuỗi văn bản. Sự khác biệt giữa hai chức năng như sau:
Đây là cách bạn sử dụng các chức năng thay thế và thay thế các chức năng trong Excel. Hy vọng, những ví dụ này sẽ chứng minh hữu ích trong việc giải quyết các nhiệm vụ của bạn. Tôi cảm ơn bạn đã đọc và hy vọng sẽ thấy trên blog của chúng tôi vào tuần tới! Tải xuống Sổ làm việc thực hànhThay thế và thay thế các ví dụ công thức (tệp .xlsx) Bạn cũng có thể quan tâmChức năng nào trong Excel chúng ta có thể sử dụng để thay thế văn bản trong Excel?Thay thế thay thế một phần của chuỗi văn bản, dựa trên số lượng ký tự bạn chỉ định, bằng một chuỗi văn bản khác.Thay thếb thay thế một phần của chuỗi văn bản, dựa trên số byte bạn chỉ định, bằng một chuỗi văn bản khác.
Hàm tìm và thay thế trong excel là gì?Nhấn Ctrl + H. Tab thay thế của hộp thoại Tìm và thay thế xuất hiện.Nhập văn bản bạn muốn thay thế trong tìm hộp nào ... Thay thế tất cả: Thay thế mọi lần xuất hiện của mặt hàng .. Thay thế: Thay thế mục đã chọn .. Tìm tất cả: Tìm tất cả các trường hợp của mặt hàng .. Tìm tiếp theo: Tìm ví dụ tiếp theo của mục .. Công thức để thay thế một từ với một từ khác trong một ô?Hàm thay thế Excel thay thế văn bản trong một chuỗi đã cho bằng cách khớp.Ví dụ: thay thế ("952-455-7865", "-", "") trả về "9524557865";Dấu gạch ngang bị tước.=SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. |
