Hàm tìm kiếm chữ trong excel

     

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

Bạn đang xem: Hàm tìm kiếm chữ trong excel


Hàm FIND:

Hàm FIND vào Excel là hàm dò tìm ký tự trong excel được áp dụng để trả lại địa chỉ của một ký tự xuất xắc 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, )

Hai đối số trước tiên là bắt buộc, sau cuối là tùy chọn.

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

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

Ví dụ, công thức =FIND("d", "find") trả về 4 vày “d” là ký tự máy 4 trong trường đoản cú ” find “. Công thức =FIND("a", "find") trả về lỗi vì không có “a” trong ” find “.


*

*

*

Hàm FIND – những vấn đề cần nhớ!

Để sử dụng đúng chuẩn công thức FIND – hàm dò tìm ký kết tự vào excel, hãy ghi nhớ phần lớn điều dễ dàng và đơn giả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 đựng nhiều ký tự, hàm FIND vẫn trả về địa điểm của ký từ bỏ đầu tiên . Ví dụ, phương pháp FIND (“ap”, “happy”) trả về 2 bởi “a” là ký tự lắp thêm 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à địa chỉ của chữ “l” trước tiên trong từ bỏ “hello”.Nếu find_text là một chuỗi trống “”, cách làm FIND Excel trả về ký kết tự thứ nhất trong chuỗi search kiếm.Hàm FIND của Excel trả về #VALUE!  nếu xảy ra ngẫu nhiên trường thích 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ố trong những âm.

Hàm search trong Excel:

Hàm tìm kiếm trong Excel vô cùng giống với hàm FIND tại đoạn nó cũng trả về vị trí của một chuỗi phụ vào một chuỗi văn bản. Cú pháp và các đối số tương tự như như FIND:

SEARCH (find_text, within_text, )

Không hệt như FIND, hàm tìm kiếm không phân biệt chữ hoa chữ thường và có thể chấp nhận được sử dụng các ký tự núm thế.

Dưới đây là một số cách làm cơ phiên bản của SEARCH:

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

=SEARCH(“e”, “Excel”) Trả về 1 bởi vì “e” là cam kết tự thứ nhất 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 kiếm thấy quý giá của đối số find_text.Đối số start_num to hơn độ dài của within_text.Start_num bằng hoặc nhỏ dại hơn 0 (không).

*

So sánh FIND với SEARCH:

Như đã đề cập, hàm FIND và search trong Excel siêu giống nhau về cú pháp và giải pháp sử dụng. Tuy nhiên, chúng cũng có một vài sự khác biệt.

FIND biệt lập chữ Hoa, tìm kiếm không phân minh chữ hoa.

Sự khác biệt cơ bản nhất thân hàm search và FIND là tìm kiếm là không phân biệt chữ hoa chữ thường, trong những lúc FIND minh bạch chữ hoa chữ thường.

Ví dụ, tìm kiếm (“e”, “Excel”) trả về 1 vày nó nhấn “E” hoa , trong những khi FIND (“e”, “Excel”) trả về 4 do nó rành mạch E với e.

*

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

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

Dấu chấm hỏi (?) nuốm cho một ký kết tự, vàDấu hoa thị (*) nạm cho ngẫu nhiên chuỗi ký tự nào.

Xem thêm: Bột Kem Là Gì? Cách Làm Kem Bằng Bột Làm Kem Từ Bột Kem Béo Cực Ngon Mà Đơn Giản

Hãy coi nó hoạt động như nuốm nào trên dữ liệu thực tế:

*
Như chúng ta thấy trong hình ảnh chụp screen ở trên, công thức search (“functio*2013”, A2) trả về vị trí của ký kết tự thứ nhất (“f”) trong chuỗi phụ nếu như chuỗi văn bạn dạng được nói trong within_text tất cả chứa cả “function ” và “2013”, bất kể có bao nhiêu ký tự không giống ở giữa.

Mẹo. Để đích thực tìm một lốt hỏi (?) hoặc có thể dấu sao (*), gõ một dấu bửa (~) trước ký tự tương ứng.

Thủ thuật Excel nâng cao

Các lấy ví dụ như về cách làm FIND với SEARCH:

Trong thực tế, những hàm FIND và tìm kiếm hiếm lúc được sử dụng. Thông thường, bạn sẽ sử dụng chúng kết phù hợp với các hàm khác ví như MID, LEFT hoặc RIGHT,

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

Ví dụ này cho thấy cách bạn cũng có thể tìm cùng trích ra toàn bộ các ký kết tự vào một chuỗi văn bản ở bên trái hoặc bên buộc phải của một ký kết tự cầm cố thể.

Giả sử bạn có một cột họ và tên (cột A) và bạn có nhu cầu kéo Tên với Họ sang các cột riêng.

Để có được tên, chúng ta cũng có thể sử dụng hàm FIND (hoặc SEARCH) kết 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ự mặt từ phía trái qua vào một chuỗi. Và bạn áp dụng hàm FIND nhằm xác xác định trí của khoảng trắng (“”) để cho phép LEFT biết được có bao nhiêu cam kết tự để trích xuất. Tại đó, các bạn -1 từ địa điểm của khoảng tầm trắng vì chưng bạn không muốn giá trị trả lại tất cả cả khoảng tầm trắng.

Để trích xuất phần HỌ, sử dụng kết hợp các hàm RIGHT, FIND / tìm kiếm và LEN. Hàm LEN để nhận thấy tổng số ký kết tự trong chuỗi, tự đó chúng ta trừ đi địa điểm 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 sau minh họa kết quả:

*

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

Giả sử các bạn có một trong những chuỗi văn bản trong cột A, chẳng hạn một danh sách SKU, và bạn muốn tìm địa chỉ của vết gạch thiết bị hai (-) trong một chuỗi. Ta tất cả công thức sau đây:

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

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

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

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

*

Một giải pháp khác và rất có thể là một cách dễ dàng và đơn giản hơn nhằm tìm sự xuất hiện thứ N của một cam kết tự một mực là áp dụng hàm FIND kết hợp với CHAR và SUBSTITUTE:

=FIND(CHAR(1),SUBSTITUTE(A2,”-“,CHAR(1),3))

Trong kia “-” là ký kết tự được đề cập cùng “3” là lần xuất hiện thêm thứ N mà bạn có nhu cầu tìm.

Trong bí quyết trên, hàm SUBSTITUTE sẽ sửa chữa thay thế lần xuất hiện thứ 3 của vệt gạch (“-“) với CHAR (1), là ký tự “Bắt đầu của Tiêu đề” không đề nghị ký tự trong hệ thống ASCII. Thay vị CHAR (1), bạn cũng có thể sử dụng ngẫu nhiên ký tự không in được khác từ một đến 31. Cùng sau đó, hàm FIND trả về địa điểm của ký kết tự đó trong chuỗi văn bản. Vì vậy, cách làm chung là như sau:

= FIND (CHAR (1), SUBSTITUTE ( ô , ký tự , CHAR (1), lần thứ N xảy ra )

Thoạt nhìn, dường như như các công thức trên ít có mức giá trị thực tiễn, mà lại ví dụ tiếp sau sẽ cho thấy sự hữu dụng của chúng trong việc giải quyết và xử lý các công việc thực sự.

Chú ý. Hãy nhớ là hàm FIND tách biệt chữ hoa chữ thường. Trong lấy ví dụ của bọn chúng ta, điều này không tồn tại sự khác biệt, nhưng nếu bạn đang làm việc với những chữ cái và bạn muốn một chúng không rõ ràng chữ hoa chữ thường , hãy áp dụng hàm search thay bởi FIND.

Ví dụ 3.Trích xuất N cam kết tự sau một ký tự tốt nhất định

Để search một chuỗi phụ tất cả độ dài nhất mực trong bất kỳ chuỗi văn phiên bản nào, sử dụng hàm FIND hoặc Excel tìm kiếm kết hợp với hàm MID.

Trong danh sách các SKU của bọn chúng ta, giả sử bạn muốn tìm 3 cam kết tự đầu tiên sau vết gạch đầu tiên và kéo chúng nó vào một cột khác.

Nếu nhóm những ký từ trước vết gạch thứ nhất luôn có cùng độ dài (ví dụ: 2 ký tự), trên đây sẽ là 1 trong những việc dễ. Bạn rất có thể sử dụng hàm MID để trả lại 3 ký tự xuất phát điểm từ một chuỗi, bước đầu từ địa chỉ 4 (bỏ qua 2 ký kết tự thứ nhất và vệt gạch ngang):

=MID(A2, 4, 3)

Công thức thức cho chuyển động như sau: “Tìm vào ô A2, ban đầu trích xuất từ bỏ ​​ký tự 4, cùng trả về 3 cam kết tự”.

*

Tuy nhiên, trong số trang tính thực tế, chuỗi phụ bạn cần trích xuất bao gồm thể bước đầu bất cứ đâu vào chuỗi văn bản. Trong lấy ví dụ của chúng ta, bạn cũng có thể không biết bao nhiêu ký tự đứng trước vệt gạch đầu tiên. Để giải quyết vấn đề này, hãy sử dụng hàm FIND để khẳng định điểm xuất xứ của chuỗi con mà bạn muốn truy xuất.

Công thức FIND để trả lại địa chỉ của đầu tiên của dấu gạch men ngang như sau:

=FIND(“-“,A2)

Bởi vì bạn có nhu cầu bắt đầu với ký kết tự tức thì sau dấu gạch ngang, hãy thêm một vào quý giá trả về với nhúng hàm sinh sống trên vào đối số sản phẩm hai (start_num) của hàm MID:

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

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

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

*
Thật giỏi vời, mà lại nếu nhóm các ký từ trước vệt gạch trước tiên có độ dài khác nhau? đây rất có thể là một vấn đề:

*

Như bạn thấy trong hình ảnh chụp screen ở trên, công thức vận động hoàn hảo mang đến hàng 1 và 2. Trong các hàng 4 cùng 5, nhóm đồ vật hai đựng 4 cam kết tự, tuy nhiên chỉ tất cả 3 ký kết tự đầu tiên được trả về. Trong các hàng 6 với 7, chỉ tất cả 2 ký kết tự trong nhóm trang bị hai và cho nên công thức Search của họ trả về một lốt gạch ngang nữa.

Tài liệu hữu ích: Hướng dẫn học Excel cơ bản

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

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

*

Để làm rõ hơn về bí quyết MID này, chúng ta hãy chu đáo từng phần tử của nó:

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

Tương tự như vậy, bạn có thể trả lại 3 cam kết tự sau dấu đồ vật hai:

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

Hoặc, trích xuất toàn bộ các cam kết tự giữa dấu gạch ốp ngang đồ vật 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)

*

Đăng kí tức thì khóa học Word Excel cơ bản

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

Giả sử chúng ta có một vài chuỗi văn phiên bản dài trong cột A và bạn có nhu cầu tìm cùng trích xuất văn bạn dạng nằm trong vệt ngoặc đơn.

Để làm cho điều này, bạn cần hàm MID để trả về số cam kết tự ước ao muốn từ một chuỗi, và cả hàm FIND hoặc search để xác định vị trí ban đầu và số cam kết tự phải trích xuất.

Xem thêm: Hướng Dẫn Cách Viết Dấu Trên Điện Thoại Android, Cách Gõ Chữ Có Dấu

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

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

Trước tiên, các bạn tìm thấy vị trí của vệt đóng ngoặc: SEARCH(“)”,A2)Sau đó chúng ta xác định vị trí của mở ngoặc: SEARCH(“(“,A2)Và sau đó, bạn đo lường và thống kê sự biệt lập giữa các vị trí của vết đóng với mở ngoặc và trừ 1 từ số lượng đó, cũng chính vì bạn không muốn có hai vết ngoặc đối chọi trong kết quả: SEARCH(“)”,A2)-SEARCH(“(“,A2))-1

*

Đương nhiên, không tồn tại gì rào cản bạn thực hiện hàm FIND thay do SEARCH, bởi vì không cần để ý đến việc rành mạch chữ hoa với thường trong ví dụ này.

Để rất có thể ứng dụng giỏi Excel vào trong công việc, bọn họ không chỉ nắm rõ được những hàm mà lại còn buộc phải sử dụng xuất sắc cả những công thay của Excel. Phần nhiều hàm nâng cấp giúp áp dụng giỏi vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những lao lý 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 các bạn đều hoàn toàn có thể học được trong khóa đào tạo và huấn luyện Excel cơ bạn dạng đến chuyên viên dành cho tất cả những người đi làm