Cách sử dụng hàm FIND trong Excel
Hàm FIND trong Excel là hàm tìm ký tự trong một chuỗi văn bản, cũng có mục đích như với các hàm tách chuỗi như hàm MID trong Excel hay hàm LEFT hay hàm RIGHT nhưng về cơ bản thì khác nhau hoàn toàn. Hàm FIND sẽ tự động tìm ra vị trí của một ký tự trong chuỗi văn bản còn hàm tách chuỗi trên thì người dùng cần xác định trước số ký tự và vị trí cần tách. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm FIND trong Excel cũng như một số ví dụ của hàm.
Hướng dẫn dùng hàm FIND trong Excel
Cú pháp hàm FIND: =FIND(find_text; within_text; [start_num]).
Trong đó:
- find_text: là ký tự hoặc một văn bản muốn tìm kiếm vị trí.
- within_text: văn bản mà bạn muốn tìm kiếm vị trí của find_text trong đó.
- [start_num]: chỉ định vị trí bắt đầu tìm kiếm trong within_text.
Lưu ý khi dùng hàm FIND:
- Hàm FIND sẽ phân biệt chữ hoa và thường trong văn bản, còn hàm SEARCH Excel không phân biệt.
- Hàm FIND không hỗ trợ tìm kiếm các ký tự như *find_text, find_text*, *find_text*…
- Dùng [start_num] để bỏ qua số ký tự đầu và bắt đầu tại vị trí được chỉ định, nếu bỏ qua [start_num] thì mặc định sẽ =1.
- Nếu start_num nhỏ hơn 0 hoặc lớn hơn độ dài của within_text thì hàm trả về giá trị lỗi.
1. Ví dụ hàm FIND trong Excel
1. Tìm vị trí của ký tự “h” trong ô B3 và có hàm thực hiện là =FIND (“h”,B3) rồi nhấn Enter. Kết quả sẽ bằng 5 vì ký tự “h” đầu tiên ở vị trí thứ 5 trong dãy văn bản, phân biệt với ký tự “H”.
2. Tìm vị trí của từ “Giang” trong ô B4 nên sẽ sử dụng tới find_text=Giang để tìm vị trí. Hàm sử dụng với lệnh =FIND(“Giang”,B4). Kết quả trả về vị trí ký tự đầu tiên của từ “Giang” nằm trong văn bản. Nếu trong dãy văn bản không có ký tự “Giang” sẽ báo lỗi #VALUES!.
3. Tìm vị trí của ký tự “a” trong dãy văn bản bắt đầu từ ký tự thứ 5. Với ví dụ này thì bạn sẽ cần dùng tới start_num là 5. Hàm có công thức là =FIND(“a”,B5,5) và kết quả là 9 cho vị trí ký tự “a” trong dãy văn bản, tính từ ký tự 5.
2. Ví dụ hàm FIND kết hợp hàm LEFT
Hàm FIND kết hợp với hàm LEFT dùng để tách chuỗi ký tự đầu trong văn bản họ và tên, mã vùng, ký hiệu…
Tìm ký tự đầu tiên trong ô B3 từ trái sang phải cho đến khi gặp ký tự “-” và không lấy ký tự “-” nên sẽ có -1 trong công thức hàm. Chúng ta sẽ có công thức hàm kết hợp là =LEFT(B3,FIND("-",B3)-1) và kết quả là Trần.
3. Ví dụ hàm FIND kết hợp hàm MID
Khi kết hợp 2 hàm này chúng ta sẽ tìm ký tự ở giữa trong dãy văn bản.
Tìm ký tự “-” đầu tiên ở ô B4 bằng hàm FIND, sau đó dùng hàm MID tách 3 ký tự ở trước và sau của ký tự “-” trong chuỗi văn bản, sẽ ra được ký tự ở giữa. Hàm sử dụng là =MID(B4,FIND("-",B4)+1, 3) rồi nhấn Enter. Kết quả là ký tự KYP.
4. Sửa lỗi hàm #VALUE! trong FIND
Báo lỗi #VALUE! trong hàm FIND có thể do 2 lỗi phổ biến.
Nguyên nhân 1: Không tìm được giá trị tham chiếu trong chuỗi văn bản.
Chẳng hạn với công thức thực hiện dưới đây của hàm FIND báo lỗi là do hàm FIND phân biệt chữ hoa với chữ thường. Vì vậy tại B5 tất cả đều là chữ hoa mà hàm tìm chữ thường sẽ báo lỗi. Hoặc trường hợp không có ký tự cần tìm trong chuỗi văn bản cũng báo #VALUE!.
Nguyên nhân 2: Lỗi [start_num] vượt quá ký tự trong dãy
Chẳng hạn tìm ký tự “a” ở vị trí nào tính từ ký tự 16 sẽ báo lỗi vì dãy văn bản có tất cả 15 ký tự. Lúc này bạn cần sửa lại [start_num] cho đúng.
![Lỗi [start_num]](https://st.quantrimang.com/photos/image/2020/06/03/Excel-ham-FIND-6.jpg)
Cách sử dụng hàm Search trong Excel
Trước hết, hàm Search trong Excel có công thức như sau:
SEARCH (find text, within text, start num)
Trong đó:
- SEARCH: tên hàm tìm kiếm.
- Find text: là ký tự, văn bản bạn muốn tìm kiếm.
- Within text: là văn bản, chuỗi ký tự cần tìm kiếm.
- Start num: vị trí bắt đầu tìm kiếm.
Bước 1:
Chúng ta sẽ bắt đầu với ví dụ dưới đây để bạn có thể hiểu về cách sử dụng hàm Search trong Excel. Chẳng hạn, tôi muốn tìm chữ "a" trong chữ "Trao thưởng".
Bước 2:
Chúng ta sẽ có công thức của hàm Search tương ứng với nội dung và yêu cầu trong Excel trên: =Search(B5,B4,C5)
Trong đó:
- SEARCH: tên hàm sử dụng.
- B5: Đối tượng cần tìm nằm ở cột B5, mà ở đây là chữ "a".
- B4: Nơi cần tìm kiếm ký tự trong chữ "Trao thưởng" nằm ở cột B4.
- C5: Vị trí tìm kiếm bằng 1, bắt đầu từ chữ cái đầu tiên, hoặc có thể bỏ qua bước này vì hệ thống sẽ mặc định là chữ cái đầu tiên, có thể thay C5 bằng con số vị trí tùy thích.
Bạn gõ lệnh Search ở bất cứ ô nào trong bảng rồi nhấn Enter như hình.
Bước 3:
Sau khi bạn nhấn Enter, kết quả sẽ trả về là thứ tự, vị trí của từ muốn tìm trong dãy ký tự. Có nghĩa là chữ "a" sẽ nằm ở vị trí thứ 3 trong "Trao thưởng".
Lưu ý với bạn đọc, với ký tự bao gồm cả dấu cách, chúng ta cần phải đếm cả ký tự dấu cách để cho ra đúng vị trí của ký tự cần tìm.
Chẳng hạn tôi muốn tìm chữ cái "n" trong chữ "Biểu diễn". Kết quả trả về sẽ là 9, nghĩa là chữ cái "n" ở nằm ở vị trí thứ 9 trong "Biểu diễn" bao gồm cả dấu cách trong từ.
Sự khác biệt cơ bản nhất giữa hàm SEARCH và FIND là SEARCH là không phân biệt chữ hoa chữ thường, trong khi FIND phân biệt chữ hoa chữ thường. Ví dụ, SEARCH (“e”, “Excel”) trả về 1 vì nó nhận “E” hoa , trong khi FIND (“e”, “Excel”) trả về 4 vì nó phân biệt E và e.
Trong Excel, người ta thường dùng hàm REPLACE để thay thế một phần của chuỗi bằng một chuỗi khác, dựa vào số ký tự được chỉ định.
Cú pháp:
= REPLACE(old_text,start_num,num_chars,new_text)
Trong đó:
- old_text: chuỗi văn bản cần được xử lý
- start_num: vị trí bắt đầu tìm cái sẽ thay thế, tính từ bên trái sang
- num_chars: là số ký tự trong old_text mà ta muốn hàm REPLACE thay bằng new_tex t
- new_text: là chuỗi mà ta muốn thay cho các ký tự trong old_text.
Tuy nhiên, cái cần lưu ý nhất khi sử dụng hàm REPLACE là làm thế nào đê rxác định start_num và num_chars.
- Chúng ta dùng hàm FIND() hoặc SEARCH() để xác định vị trí bắt đầu (start_num)
- Dùng hàm LEN() để xác định số ký tự của chuỗi sẽ được thay thế (num_chars)
Ví dụ:
Nếu muốn thay thế 2007 thành 2010 trong dòng HỌC EXCEL 2007 ta làm như sau:
Thay thế 2007 thành 2010 trong dòng HỌC EXCEL 2007
Dùng công thức: =REPLACE(C4; FIND(“2007”; C4); LEN(“2007”); “2010”)
Dùng công thức: =REPLACE để thay đổi
SUBSTITUTE (Hàm SUBSTITUTE)
Bài viết này mô tả cú pháp công thức và cách dùng hàm SUBSTITUTE trong Microsoft Excel.
Mô tả
Thay thế new_text cho old_text trong chuỗi văn bản. Hãy dùng hàm SUBSTITUTE khi bạn muốn thay thế văn bản cụ thể trong chuỗi văn bản; dùng hàm RREPLACE khi bạn muốn thay thế bất kỳ văn bản nào nằm ở một vị trí cụ thể trong chuỗi văn bản.
Cú pháp
SUBSTITUTE(text, old_text, new_text, [instance_num])
Cú pháp hàm SUBSTITUTE có các đối số sau đây:
Text Bắt buộc. Văn bản hoặc tham chiếu đến ô chứa văn bản mà bạn muốn thay thế các ký tự trong đó.
Old_text Bắt buộc. Văn bản mà bạn muốn được thay thế.
New_text Bắt buộc. Văn bản mà bạn muốn thay thế cho old_text.
Instance_num Tùy chọn. Xác định số lần xuất hiện của old_text mà bạn muốn thay bằng new_text. Nếu bạn xác định instance_num, thì số lần xuất hiện đó của old_text được thay thế. Nếu không, mọi lần xuất hiện của old_text trong văn bản được đổi thành new_text.
Ví dụ
Sao chép dữ liệu ví dụ trong bảng sau đây và dán vào ô A1 của một bảng tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.
Dữ liệu | ||
|---|---|---|
Dữ liệu Bán hàng | < | |
Quý 1, 2008 | < | |
Quý 1, 2011 | < | |
Công thức | Mô tả (Kết quả) | Kết quả |
=SUBSTITUTE(A2, "Bán hàng", "Chi phí") | Thay thế Bán hàng bằng Chi phí (Dữ liệu Chi phí) | Dữ liệu Chi phí |
=SUBSTITUTE(A3, "1", "2", 1) | Thay thế lần xuất hiện đầu tiên của "1" bằng "2" (Quý 2, 2008) | Quý 2, 2008 |
=SUBSTITUTE(A4, "1", "2", 3) | Thay thế lần xuất hiện thứ ba của "1" bằng "2" (Quý 1, 2012) |
Công thức:
SUBSTITUTE( text, old_text, new_text, [count] )Diễn giải:
- text: Chuỗi đối tượng.
- old_text: Tìm chuỗi ký tự
- new_text: Thay thế bằng
- [count]: optional - số lần được thực hiện tìm và thay thế.
2. Hàm Replace: sử dụng khi biết vị trí ký tự chuỗi cần thay thế.
Công thức:
REPLACE( old_text, start, number_of_chars, new_text )Diễn giải:
- old_text: Tìm chuỗi ký tự
- start: bắt đầu từ thứ tự ký tự (từ trái sang phải)
- numbers: số lượng ký tự từ ký tự thứ start
- new_text: Thay thế bằng
No comments:
Post a Comment