Bạn đang xem bài viết Cách Thay Thế Văn Bản Bằng Hàm Replace, Substitute 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.
Tiếp theo chuỗi bài viết về những thủ thuật và cách sử dụng hàm Excel thì hôm nay mình sẽ chia sẻ với các bạn cách để sử dụng hàm thay thế văn bảng trong Excel là hàm REPLACE và SUBSTITUTE.
Dùng luôn công cụ Find and Replace để thay thế văn bản cho nhanh, dùng hàm REPLACE và SUBSTITUTE làm gì? Thì công cụ Find and Replace có thể giúp bạn tìm kiếm và thay thế văn bản rất nhanh, nhưng nó cũng có rất nhiều hạn chế trong nhiều tình huống. Ví dụ như: mình cần thay thế vào vị trí xác định trong 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ì công cụ Find and Replace sẽ không giúp được. Thay vào đó thì hàm REPLACE và SUBSTITUTE sẽ giúp mình thực hiện những việc đó và cách thực hiện như sau:
Cách sử dụng hàm REPLACE
Mô tả:
Hàm REPLACE là hàm thay thế văn bản, có chức năng là thay thế một phần của văn bản bằng văn bản khác vào vị trí được bạn chỉ định.
Cú pháp:
=REPLACE(old_text, start_num, num_chars, new_text)
➤ Trong đó:
old_text: là văn bản hoặc tham chiếu đến một Cells.
start_num: vị trí của ký tự bắt đầu trong văn bản cũ mà bạn muốn thay thế bằng văn bản mới.
num_chars: số lượng ký tự mà bạn muốn thay thế bằng văn bản mới.
new_text: văn bản sẽ thay thế phần văn bản trong old_text.
Ví dụ:
REPLACE(A2;12;2;”26″): sẽ thay thế 26 vào văn bản trong Cells A2 ở vị trí bắt đầu là 12 và thay 2 ký tự. Kết quả 26 sẽ thay thế vào 25.
REPLACE(A3;1;2;26): thay thế 26 vào vị trí bắt đầu là 1 và thay 2 ký tự. Kết quả 25 sẽ được thay thế bằng 26.
REPLACE(REPLACE(B5;3;0;”/”);6;0;”/”): tương tự như ví dụ thứ 3. Tuy nhiên trong ví dụ này, ởnum_chars mình để là 0, điều này có nghĩa là sẽ thêm new_text vào old_text theo vị trí xác định tại start_num.
Lưu ý: khi sử dụng cú pháp này thì REPLACE(B6;FIND(“@gmail”;B6);6;”******”): kết hợp sử dụng thêm hàm FIND đểtìm kiếm tất cả các văn bản được chỉ định trong hàm . new_text sẽ thay thế vào tất cả vị trí mà hàm FIND trả về.
DATEVALUE(REPLACE(TEXT(B7;”dd-mm-yyyy”);4;2;5)): và ở ví dụ này, thì mình sử dụng thêm hàm TEXT và hàm DATEVALUE để thay đổi một trong các chỉ số thời gian như ngày tháng hoặc năm.
Cách sử dụng hàm SUBSTITUDE
Mô tả:
Hàm REPLACE sẽ thay thế văn bản mới vào văn bản cũ theo vị trí được chỉ định trong hàm. Còn hàm SUBSTITUDE cũng sẽ thay thế văn bản mới vào văn bản cũ nhưng sẽ thay thế vào vị trí có văn bản giống với văn bản được so sánh.
Cú pháp:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
➤ Trong đó:
text: là văn bản hay tham chiếu đến một Cells
old_text: là văn bản trong văn bản cũ mà bạn muốn thay thế.
new_text: văn bản mới mà bạn muốn thay thế vào old_text
Ví dụ:
Cả 2 hàm REPLACE và SUBSTITUDE đều có chức năng là thay thế văn bản trong Excel. Tuy vậy, mỗi hàm lại có những đặc trưng riêng và sẽ phù hợp với từng tình huống khác nhau. Thông qua các ví dụ mình nêu ra, hy vọng sẽ giúp các bạn giải quyết được tình huống đang gặp phải.
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.
Các Cách Thay Thế Lỗi Bằng Hàm Isna Trong Excel
EXCEL vô cùng hữu ích trong việc điền thông tin mà không tốn nhiều thời gian bằng việc sử dụng hàm công thức. Tuy nhiên, sẽ có những trường hợp hàm trong EXCEL chạy ra kết quả bị lỗi (#N/A) do dữ liệu bị hạn chế. Thay vào đó, bạn sẽ cần đổi lỗi #N/A bằng một dữ liệu khác. Vậy phải làm thế nào để thực hiện công việc này một cách nhanh gọn nhất. Đừng lo, https://dobabiesmatter.com/ sẽ giúp bạn giải quyết vấn đề này bằng cách sử dụng hàm ISNA trong EXCEL.
Hàm ISNA trong EXCEL là gì?
Trước khi đi vào sử dụng ISNA, mời bạn đọc cùng chúng tôi tìm hiểu ý nghĩa của hàm này trong EXCEL. Thông thường, để điền thông tin tham chiếu lên một bảng có sẵn, bạn sẽ cần dùng đến hàm Vlookup, Index,…. Tuy nhiên, hàm Vlookup, Index,…. có thể cho ra kết quả #N/A nếu phát hiện một số mã ngạch nào đó không tồn tại trên bảng tham chiếu.
Khi đó, kết quả cần nhập trên dòng #N/A là một dữ liệu nào đó như: khác, để trống,… Để thay thế dòng #N/A bằng nội dung theo ý muốn, bạn sẽ cần phải điền tay từng dòng trên bảng mẫu. Như vậy, người dùng sẽ phải tốn rất nhiều thời gian. Điều này làm mất đi tính năng tiện lợi của EXCEL.
Nhằm tránh phải mất thời gian nhập từng dòng bị #N/A, bạn có thể sử dụng hàm ISNA trong EXCEL để hỗ trợ. Trong tên gọi của hàm ISNA gồm có 2 phần, đó là “IS” và “NA”. Mỗi thành phần có một ý nghĩa riêng, cụ thể như sau:
IS là một ký tự có chức năng kiểm tra kết quả đúng/sai hoặc có/không.
NA ký hiệu cho lỗi #N/A trong EXCEL.
Hiểu một cách đơn giản hơn, hàm ISNA dùng để phát hiện ra lỗi sai (#N/A) từ kết quả của các hàm. Tuy nhiên, khi dùng hàm ISNA trong EXCEL, bạn chỉ có thể tìm ra các lỗi sai chứ không thể điền thông tin. Để có thể điền được thông tin theo ý muốn vào các dòng #N/A, bạn sẽ cần kết hợp thêm hàm IF.
Cách thay thế lỗi bằng hàm ISNA trong EXCEL
Để hoàn tất việc điền dữ liệu tham chiếu, bạn sẽ cần thực hiện 2 bước. Bước đầu tiên là phát hiện lỗi #N/A. Bước tiếp theo là thay thế các lỗi đó bằng dữ liệu cần điền. Nội dung cụ thể của từng bước như sau.
Bước 1 – Phát hiện lỗi #N/A
Tương tự như các hàm khác, để sử dụng ISNA bạn sẽ cần nhập biểu thức vào sau dấu “=”. Cụ thể, cách viết như sau:
= ISNA ( value )
Trong đó, “value” có thể ở nhiều dạng khác nhau như: giá trị, hàm, công thức,… Thực chất, hàm ISNA là một biểu thức dùng để trả lời kết quả TRUE hoặc FALSE.
Nếu “value” có lỗi #N/A, hàm ISNA sẽ báo kết quả là TRUE.
Nếu “value” không có lỗi #N/A, hàm ISNA sẽ báo kết quả là FALSE.
Bước 1 – Thay thế lỗi #N/A
Trong bước này, chúng tôi sẽ hướng dẫn bạn dùng hàm IF kết hợp hàm ISNA trong EXCEL để hoàn chỉnh việc điền thông tin. Bạn chỉ cần sử dụng hàm IF với nguyên tắc như sau:
Nếu kết quả của ISNA là TRUE, IF sẽ cho ra kết quả là thông tin bạn muốn nhập.
Nếu kết quả của ISNA là FALSE, IF sẽ cho ra kết quả bằng với “value”.
Công thức căn bản của hàm IF như sau:
= IF (logical_test ; [value_if_true] ; [value_if_false])
Trong đó, ý nghĩa của từng vị trí như sau:
logical_test: Đây là công thức cần tính, công thức này sẽ cho ra kết quả đúng (TRUE) hoặc sai (FALSE).
[value_if_true]: Đây sẽ là kết quả hiển thị trên EXCEL nếu kết quả của logical_test là đúng (TRUE).
[value_if_false]: Đây sẽ là kết quả hiển thị trên EXCEL nếu kết quả của logical_test là sai (FALSE).
Lúc này, cách viết để kết hợp ISNA và IF như sau:
= IF ( ISNA (value) ; “thông tin cần nhập” ; value )
Lưu ý khi dùng hàm ISNA trong EXCEL
Việc dùng ISNA trong EXCEL không quá khó khăn nhưng rất dễ nhầm lẫn.
Kết luận
Có thể thấy, việc sử dụng hàm ISNA trong EXCEL vô cùng hữu ích trong việc điền dữ liệu tham chiếu. Nhờ có hàm này, thời gian điền dữ liệu được rút ngắn gấp 6 lần bình thường. Hy vọng qua bài viết này, bạn đã có thêm một mẹo mới trong việc sử dụng EXCEL. Nếu còn bất cứ thắc mắc gì, bạn có thể đặt câu hỏi với chúng tôi Chúng tôi rất hân hạnh được giải đáp tất cả các thắc mắc của bạn.
Cách Tìm Chuỗi Văn Bản Trong Một Chuỗi Khác Bằng Hàm Find, Findb Trong Excel
=FIND(find_text, within_text, [start_num])
=FINDB(find_text, within_text, [start_num])
Trong đó:
FIND là tên hàm dùng để định vị một chuỗi văn bản nằm trong chuỗi văn bản thứ hai và trả về số của vị trí bắt đầu của chuỗi văn bản thứ nhất tính từ ký tự thứ nhất của chuỗi văn bản thứ hai.
FINDB là hàm FIND dùng với những ngôn ngữ sử dụng bộ ký tự byte kép (DBCS)
Find_text: Là chuỗi văn bản cần tìm. Đây là giá trị bắt buộc.
Within_text: Là chuỗi văn bản chứa chuỗi văn bản cần tìm. Đây là giá trị bắt buộc.
Start_num: Là vị trí ký tự bắt đầu tìm trong chuỗi văn bản within_text. Ký tự thứ nhất trong chuỗi within_text là ký tự số 1. Nếu bỏ qua giá trị start_num thì mặc định nó sẽ lấy là 1.
Ví dụ và cách sử dụng hàm FIND và FINDB
Ví dụ 1
Ảnh dưới là ví dụ mô tả công thức FIND tìm ký tự M đầu tiên trong chuỗi và trả về thứ tự của ký tự đó trong chuỗi.
Ví dụ 2
Với chuỗi văn bản tham chiếu như trên, bạn cần tìm vị trí của ký tự “n” từ ký tự thứ 5 của chuỗi, công thức hàm sẽ như sau:
Ví dụ 3: Sử dụng kết hợp hàm FIND và hàm MID/LEFT/RIGHT
Bạn cần trích xuất n ký tự từ chuỗi văn bản gốc miễn sao trước dấu thăng # của chuỗi để ra chủng loại sản phẩm.
Trước tiên dùng hàm FIND để xác định thứ tự của giá trị # trong chuỗi dữ liệu gốc.
Tiếp theo dùng hàm LEFT để lấy các ký tự từ đầu cho đến trước dấu # bằng cách trừ thứ tự của # tìm đc ở trên cho 1.
Công thức để lấy thông tin cho cột Sản phẩm sẽ như sau: B4 =LEFT(A4,FIND(“#”,A4)-1). Sao chép công thức cho toàn bộ ô trong cột bạn thu được kết quả:
Bạn sao chép công thức cho các ô còn lại và được kết quả:
Lưu ý
Nếu như đối số Within_text và Find_text là dạng chữ, bạn phải đặt vào dấu nháy kép “”.
FIND và FINDB phân biệt chữ hoa chữ thường và không cho phép dùng ký tự đại diện.
Nếu find_text không xuất hiện trong within_text, thì hàm FIND và FINDB trả về giá trị lỗi #VALUE!.
Nếu start_num không lớn hơn không, thì hàm FIND và FINDB trả về giá trị lỗi #VALUE!.
Nếu start_num lớn hơn độ dài của within_text, thì hàm FIND và FINDB trả về giá trị lỗi #VALUE!.
Cập nhật thông tin chi tiết về Cách Thay Thế Văn Bản Bằng Hàm Replace, Substitute 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!