find n occurrence

Với bài viết này, Học Excel Online sẽ hướng dẫn này giải thích cú pháp của hàm FIND và SEARCH và đưa ra các ví dụ về công thức rất hữu ích trong công việc. Nào hãy cùng tìm hiểu nhé.

Hàm FIND:

Hàm FIND trong Excel là hàm dò tìm ký tự trong excel được sử dụng để trả lại vị trí của một ký tự hay chuỗi phụ trong một chuỗi văn bản.

Cú pháp của hàm Find như sau :

FIND (find_text, within_text, [start_num])

Hai đối số tiên phong là bắt buộc, ở đầu cuối là tùy chọn .

  • Find_text – ký tự hoặc chuỗi phụ bạn muốn tìm.
  • Within_text – chuỗi văn bản được tìm kiếm. Thông thường nó được xem như một ô tham chiếu, nhưng bạn cũng có thể gõ chuỗi trực tiếp vào công thức.
  • Start_num – một đối số tùy chọn xác định vị trí của ký tự mà bạn bắt đầu tìm kiếm. Nếu không nhập, Excel sẽ tìm kiếm bắt đầu từ ký tự thứ nhất của chuỗi Within_text.

Nếu hàm FIND không tìm thấy ký tự find_text, Excel sẽ trả về lỗi # VALUE ! .

Ví dụ, công thức =FIND("d", "find") trả về 4 vì “d” là ký tự thứ 4 trong từ ” find “. Công thức =FIND("a", "find") trả về lỗi vì không có “a” trong ” find “.

Excel FIND function

Hàm FIND – những điều cần nhớ!

Để sử dụng đúng chuẩn công thức FIND – hàm dò tìm ký tự trong excel, hãy ghi nhớ những điều đơn thuần sau đây :

  • Hàm FIND phân biệt chữ hoa chữ thường .
  • Hàm FIND trong Excel không cho phép sử dụng ký tự thay thế .
  • Nếu đối số find_text chứa nhiều ký tự, hàm FIND sẽ trả về vị trí của ký tự đầu tiên . Ví dụ, công thức FIND (“ap”, “happy”) trả về 2 vì “a” là ký tự thứ hai trong từ ” happy”.
  • Nếu trong phần within_text chứa nhiều lần xuất hiện của tệp tin find_text, lần xuất hiện đầu tiên sẽ được trả về. Ví dụ, FIND (“l”, “hello”) trả về 3, là vị trí của chữ “l” đầu tiên trong từ “hello”.
  • Nếu find_text là một chuỗi trống “”, công thức FIND Excel trả về ký tự đầu tiên trong chuỗi tìm kiếm.
  • Hàm FIND của Excel trả về #VALUE!  nếu xảy ra bất kỳ trường hợp nào sau đây:
    • Find_text không tồn tại trong within_text.
    • Start_num chứa nhiều ký tự hơn  within_text.
    • Start_num là 0 (không) hoặc một số âm.

Hàm SEARCH trong Excel:

Hàm SEARCH trong Excel rất giống với hàm FIND ở chỗ nó cũng trả về vị trí của một chuỗi phụ trong một chuỗi văn bản. Cú pháp và những đối số cũng tương tự như như FIND :

SEARCH (find_text, within_text, [start_num])

Không giống như FIND, hàm Search không phân biệt chữ hoa chữ thường và cho phép sử dụng các ký tự thay thế.

Dưới đây là 1 số ít công thức cơ bản của SEARCH :

=SEARCH(“market”, “supermarket”)Trả về 6 vì chuỗi “market ” bắt đầu từ ký tự thứ 6 của từ “supermarket”.

=SEARCH(“e”, “Excel”) Trả về 1 vì “e” là ký tự đầu tiên trong từ “Excel”, bất kể E hoa.

Giống như FIND, hàm SEARCH của Excel trả về # VALUE ! Lỗi nếu :

  • Không tìm thấy giá trị của đối số find_text.
  • Đối số start_num lớn hơn độ dài của within_text.
  • Start_num bằng hoặc nhỏ hơn 0 (không).

Excel SEARCH function

Xem ngay : Học Excel nâng cao ở đâu ?

So sánh FIND với SEARCH:

Như đã đề cập, hàm FIND và SEARCH trong Excel rất giống nhau về cú pháp và cách sử dụng. Tuy nhiên, chúng cũng có một vài sự độc lạ .

  1. FIND phân biệt chữ Hoa, SEARCH không phân biệt chữ hoa.

Sự độc lạ 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 .

excel case sensitive search

  1. Search với ký tự thay thế

Không giống như hàm FIND, hàm SEARCH được cho phép sử dụng những ký tự đại diện thay mặt trong đối số find_text :

  • Dấu chấm hỏi (?) thay cho một ký tự, và
  • Dấu hoa thị (*) thay cho bất kỳ chuỗi ký tự nào.

Hãy xem nó hoạt động giải trí như thế nào trên tài liệu trong thực tiễn :

Search with wildcard characters in ExcelNhư bạn thấy trong ảnh chụp màn hình ở trên, công thức SEARCH (“functio*2013”, A2) trả về vị trí của ký tự đầu tiên (“f”) trong chuỗi phụ nếu chuỗi văn bản được đề cập trong within_text  có chứa cả “function ” Và “2013”, bất kể có bao nhiêu ký tự khác ở giữa.

Mẹo. Để thực sự tìm một dấu hỏi (?) hoặc dấu sao (*), gõ một dấu ngã (~) trước ký tự tương ứng.

Các ví dụ về công thức FIND và SEARCH:

Trong thực tiễn, những hàm FIND và SEARCH hiếm khi được sử dụng. Thông thường, bạn sẽ sử dụng chúng phối hợp với những hàm khác như MID, LEFT hoặc RIGHT ,

Ví dụ 1. Tìm một chuỗi trước hoặc theo sau một ký tự nhất định

Ví dụ này cho thấy cách bạn hoàn toàn có thể tìm và trích ra tổng thể những ký tự trong một chuỗi văn bản ở bên trái hoặc bên phải của một ký tự đơn cử .
Giả sử bạn có một cột họ và tên ( cột A ) và bạn muốn kéo Tên và Họ sang những cột riêng .
Để có được tên, bạn hoàn toàn có thể sử dụng hàm FIND ( hoặc SEARCH ) tích hợp với hàm LEFT :

=LEFT(A2, FIND(” “, A2)-1)

hoặc là

=LEFT(A2, SEARCH(” “, A2)-1)

Hàm Excel LEFT trả về số ký tự bên từ bên trái qua trong một chuỗi. Và bạn sử dụng hàm FIND để xác lập vị trí của khoảng chừng trắng ( “ ” ) để cho phép LEFT biết được có bao nhiêu ký tự để trích xuất. Tại đó, bạn – 1 từ vị trí của khoảng chừng trắng vì bạn không muốn giá trị trả lại gồm cả khoảng chừng trắng .
Để trích xuất phần HỌ, sử dụng phối hợp những hàm RIGHT, FIND / SEARCH và LEN. Hàm LEN để nhận được tổng số ký tự trong chuỗi, từ đó bạn trừ đi vị trí của khoảng chừng trắng :

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

hoặc là

=RIGHT(A2,LEN(A2)-SEARCH(” “,A2))

Ảnh chụp màn hình hiển thị sau minh họa hiệu quả :

Splitting the first name and last names into separate columns.

Ví dụ 2. Tìm sự xuất hiện thứ N của một ký tự nhất định trong một chuỗi văn bản

Giả sử bạn có 1 số ít chuỗi văn bản trong cột A, ví dụ điển hình một list SKU, và bạn muốn tìm vị trí của dấu gạch thứ hai ( – ) trong một chuỗi. Ta có công thức sau đây :

=FIND(“-“, A2, FIND(“-“,A2)+1)

Hai đối số đầu tiên xác định dấu gạch ngang (“-“) trong ô A2. Số thứ ba (start_num), bạn nhúng một hàm FIND để Excel để bắt đầu tìm kiếm từ Lần thứ 2 xuất hiện của dấu gạch ngang (FIND ( “-“, A2) +1).

Để trả lại vị trí của lần xuất hiện thứ 3 , bạn nhúng công thức trên vào đối số start_num của một hàm FIND khác và thêm 2 vào giá trị trả về:

=FIND(“-“,A2, FIND(“-“, A2, FIND(“-“,A2)+1) +2)

FIND formulas to find the position of 2<sup>nd</sup> and 3<sup>rd</sup> occurrences of a specific character in a string” class=”imgl2 aligncenter” src=”https://blog.hocexcel.online/wp-content/uploads/img-blog/find-n-occurrence.png” title=”FIND formulas to find the position of 2<sup>nd</sup> and 3<sup>rd</sup> occurrences of a specific character in a string”/></p>
<p>Một cách khác và hoàn toàn có thể là một cách đơn thuần hơn để tìm sự Open thứ N của một ký tự nhất định là sử dụng hàm FIND tích hợp với CHAR và SUBSTITUTE :</p>
<p><strong>=FIND(CHAR(1),SUBSTITUTE(A2,”-“,CHAR(1),3))</strong></p>
<p>Trong đó “ – ” là ký tự được đề cập và “ 3 ” là lần Open thứ N mà bạn muốn tìm .<br />
Trong công thức trên, hàm SUBSTITUTE sẽ sửa chữa thay thế lần Open thứ 3 của dấu gạch ( “ – “ ) với CHAR ( 1 ), là ký tự “ Bắt đầu của Tiêu đề ” không bắt buộc ký tự trong mạng lưới hệ thống ASCII. Thay vì CHAR ( 1 ), bạn hoàn toàn có thể sử dụng bất kể ký tự không in được khác từ 1 đến 31. Và sau đó, hàm FIND trả về vị trí của ký tự đó trong chuỗi văn bản. Vì vậy, công thức chung là như sau :</p>
<p><strong>= FIND (CHAR (1), SUBSTITUTE ( ô , ký tự , CHAR (1), lần thứ N xảy ra )</strong></p>
<p>Thoạt nhìn, có vẻ như như những công thức trên ít có giá trị thực tiễn, nhưng ví dụ tiếp theo sẽ cho thấy sự hữu dụng của chúng trong việc xử lý những việc làm thực sự .</p>
<p><strong>Chú ý. </strong>Hãy nhớ rằng hàm FIND phân biệt chữ hoa chữ thường. Trong ví dụ của chúng ta, điều này không có sự khác biệt, nhưng nếu bạn đang làm việc với các chữ cái và bạn muốn một chúng <strong>không phân biệt chữ hoa chữ thường</strong> , hãy sử dụng hàm SEARCH  thay vì FIND.</p>
<p><strong>Ví dụ 3.Trích xuất N ký tự sau một ký tự nhất định</strong></p>
<p>Để tìm một chuỗi phụ có độ dài nhất định trong bất kể chuỗi văn bản nào, sử dụng hàm FIND hoặc Excel SEARCH phối hợp với hàm MID .<br />
Trong list những SKU của tất cả chúng ta, giả sử bạn muốn tìm 3 ký tự tiên phong sau dấu gạch tiên phong và kéo chúng vào một cột khác .<br />
Nếu nhóm những ký tự trước dấu gạch tiên phong luôn có cùng độ dài ( ví dụ : 2 ký tự ), đây sẽ là một việc dễ. Bạn hoàn toàn có thể sử dụng hàm MID để trả lại 3 ký tự từ một chuỗi, khởi đầu từ vị trí 4 ( bỏ lỡ 2 ký tự tiên phong và dấu gạch ngang ) :</p>
<p><strong>=MID(A2, 4, 3)</strong></p>
<p>Công thức thức cho hoạt động giải trí như sau : “ Tìm trong ô A2, khởi đầu trích xuất từ ​ ​ ký tự 4, và trả về 3 ký tự ” .</p>
<p><img alt=

Tuy nhiên, trong những trang tính thực tiễn, chuỗi phụ bạn cần trích xuất hoàn toàn có thể mở màn bất kể đâu trong chuỗi văn bản. Trong ví dụ của tất cả chúng ta, bạn hoàn toàn có thể không biết bao nhiêu ký tự đứng trước dấu gạch tiên phong. Để xử lý yếu tố này, hãy sử dụng hàm FIND để xác lập điểm xuất phát của chuỗi con mà bạn muốn truy xuất .
Công thức FIND để trả lại vị trí của tiên phong của dấu gạch ngang như sau :

=FIND(“-“,A2)

Bởi vì bạn muốn mở màn với ký tự ngay sau dấu gạch ngang, hãy thêm 1 vào giá trị trả về và nhúng hàm ở trên trong đối số thứ hai ( start_num ) của hàm MID :

=MID(A2, FIND(“-“,A2)+1, 3)

Trong trường hợp này, hàm SEARCH hoạt động giải trí tốt :

=MID(A2, SEARCH(“-“,A2)+1, 3)

Use the FIND function to determine the starting point of the substring you want to extract.Thật tuyệt vời, nhưng nếu nhóm các ký tự trước dấu gạch đầu tiên có độ dài khác nhau? đây có thể là một vấn đề:

The group of chars preceding the first dash contains a different number of characters

Như bạn thấy trong ảnh chụp màn hình hiển thị ở trên, công thức hoạt động giải trí tuyệt vời và hoàn hảo nhất cho hàng 1 và 2. Trong những hàng 4 và 5, nhóm thứ hai chứa 4 ký tự, nhưng chỉ có 3 ký tự tiên phong được trả về. Trong những hàng 6 và 7, chỉ có 2 ký tự trong nhóm thứ hai và do đó công thức Search của tất cả chúng ta trả về một dấu gạch ngang nữa .

Nếu bạn muốn trả về tất cả các ký tự giữa 2 lần xuất hiện của một ký tự nào đó (trong ví dụ này là dấu gạch ngang)? Thì đây là câu trả lời:

=MID(A2, FIND(“-“,A2)+1, FIND(“-“, A2, FIND(“-“,A2)+1) – FIND(“-“,A2)-1)

The FIND formula to return all characters between the first and second occurrences of a specific character

Để hiểu rõ hơn về công thức MID này, tất cả chúng ta hãy xem xét từng thành phần của nó :

  • Phần đầu tiên (văn bản). Đó là chuỗi văn bản chứa các ký tự mà bạn muốn trích xuất, trong ví dụ này là ô A2.
  • Phần tử thứ hai (vị trí bắt đầu). Chỉ định vị trí của ký tự đầu tiên bạn muốn trích xuất. Bạn sử dụng hàm FIND để tìm dấu đầu tiên trong chuỗi và thêm 1 vào giá trị đó bởi vì bạn muốn bắt đầu với ký tự đi sau dấu gạch ngang: FIND (“-“, A2) +1.
  • Phần tử thứ ba (num_chars). Chỉ định số ký tự bạn muốn trả về. Trong công thức của chúng ta, đây là phần khó khăn nhất. Bạn sử dụng hai hàm FIND (hoặc SEARCH), một sẽ xác định vị trí của dấu gạch đầu tiên: FIND (“-“, A2). Và hàm thứ hai trả về vị trí của dấu gạch ngang thứ hai: FIND (“-“, A2, SEARCH (“-“, A2) +1). Sau đó, bạn trừ cái trước đến sau, rồi sau đó trừ 1 vì bạn không muốn lấy cả dấu gạch ngang. Kết quả là, bạn sẽ nhận được số ký tự giữa dấu gạch ngang thứ nhất và thứ hai, đó là chính xác những gì chúng ta đang tìm kiếm. Vì vậy, bạn đưa giá trị đó cho đối số num_chars của hàm MID.

Tương tự như vậy, bạn hoàn toàn có thể trả lại 3 ký tự sau dấu thứ hai :

=MID(A2, FIND(“-“,A2, FIND(“-“, A2, FIND(“-“,A2)+1) +2), 3)

Hoặc, trích xuất tổng thể những ký tự giữa dấu gạch ngang thứ hai và thứ ba :

=MID(A2, FIND(“-“, A2, FIND(“-“,A2)+1)+1, FIND(“-“,A2, FIND(“-“, A2, FIND(“-“,A2)+1) +2) – FIND(“-“, A2, FIND(“-“,A2)+1)-1)

6 3

Ví dụ 4. Tìm văn bản giữa các dấu ngoặc đơn

Giả sử bạn có một số ít chuỗi văn bản dài trong cột A và bạn muốn tìm và trích xuất văn bản nằm trong dấu ngoặc đơn .
Để làm điều này, bạn cần hàm MID để trả về số ký tự mong ước từ một chuỗi, và cả hàm FIND hoặc SEARCH để xác lập vị trí mở màn và số ký tự cần trích xuất .

=MID(A2,SEARCH(“(“,A2)+1, SEARCH(“)”,A2)-SEARCH(“(“,A2)-1)

Logic của công thức này tựa như với logic mà tất cả chúng ta đã bàn tới trong ví dụ trước. Và cũng như thế, phần phức tạp nhất là đối số sau cuối cho biết công thức có bao nhiêu ký tự được trả lại. Biểu thức trong đối số num_chars khá dài :

  • Trước tiên, bạn tìm thấy vị trí của dấu đóng ngoặc: SEARCH(“)”,A2)
  • Sau đó bạn xác định vị trí của mở ngoặc: SEARCH(“(“,A2)
  • Và sau đó, bạn tính toán sự khác biệt giữa các vị trí của dấu đóng và mở ngoặc  và trừ 1 từ con số đó, bởi vì bạn không muốn có hai dấu ngoặc đơn trong kết quả: SEARCH(“)”,A2)-SEARCH(“(“,A2))-1

Excel SEARCH formula to find and extract text between parentheses

Đương nhiên, không có gì ngăn cản bạn sử dụng hàm FIND thay vì SEARCH, chính do không cần chăm sóc đến việc phân biệt chữ hoa và thường trong ví dụ này .
Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table …
Toàn bộ những kỹ năng và kiến thức này những bạn đều hoàn toàn có thể học được trong khóa học Excel cơ bản đến chuyên viên dành cho người đi làm

Leave a Reply

Email của bạn sẽ không được hiển thị công khai.