TÌM KIẾM CHUỖI TRONG EXCEL

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

Bạn đang xem: Tìm kiếm chuỗi trong excel


Hàm FIND:

Hàm FIND vào Excel là hàm dò tìm cam kết tự trong excel được thực hiện để trả lại địa điểm của một ký tự tốt chuỗi phụ vào 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ố thứ nhất là bắt buộc, ở đầu cuối là tùy chọn.

Find_text – cam kết tự hoặc chuỗi phụ bạn muốn tìm.Within_text – chuỗi văn bạn dạng được tra cứu kiếm. Thông thường xuyên nó được coi như 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 xác định trí của ký tự mà lại bạn bắt đầu tìm kiếm. Nếu ko nhập, Excel vẫn 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 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ì chưng “d” là ký tự đồ vật 4 trong tự ” find “. Công thức =FIND("a", "find") trả về lỗi vì không tồn tại “a” vào ” find “.


*

*

*

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ớ hầu hết điều đơn giản sau đây:

Hàm FIND phân biệt chữ hoa chữ thường .Hàm FIND trong Excel không được cho phép sử dụng ký từ thay thế .Nếu đối số find_text đựng nhiều ký tự, hàm FIND đang trả về vị trí của ký từ đầu tiên . Ví dụ, cách làm FIND (“ap”, “happy”) trả về 2 vày “a” là cam kết tự vật dụng hai trong từ ” happy”.Nếu vào 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” đầu 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ự trước tiên trong chuỗi tìm kiếm kiếm.Hàm FIND của Excel trả về #VALUE!  trường hợp xảy ra bất kỳ trường phù hợp nào sau đây:Find_text không tồn tại trong within_text.Start_num đựng nhiều ký tự rộng within_text.Start_num là 0 (không) hoặc một số trong những âm.

Hàm tìm kiếm trong Excel:

Hàm tìm kiếm trong Excel vô cùng giống cùng với hàm FIND ở vị trí 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à các đối số tương tự như như FIND:

SEARCH (find_text, within_text, )

Không giống hệt như FIND, hàm tìm kiếm không riêng biệt chữ hoa chữ thường và được cho phép sử dụng các ký tự cầm cố thế.

Dưới đó là một số công thức cơ bạn dạng của SEARCH:

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

=SEARCH(“e”, “Excel”) Trả về 1 bởi “e” là ký tự trước tiên trong từ bỏ “Excel”, bất kỳ E hoa.

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

Không kiếm tìm thấy cực hiếm của đối số find_text.Đối số start_num to hơn độ dài của within_text.Start_num bởi hoặc nhỏ hơn 0 (không).

*

So sánh FIND với SEARCH:

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

FIND phân biệt chữ Hoa, tìm kiếm không sáng tỏ chữ hoa.

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

Ví dụ, tìm kiếm (“e”, “Excel”) trả về 1 vày nó dấn “E” hoa , trong những lúc FIND (“e”, “Excel”) trả về 4 vì nó phân minh E và e.

*

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

Không giống hệt như hàm FIND, hàm SEARCH chất nhận được sử dụng các ký tự thay mặt đại diện trong đối số find_text:

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

Xem thêm: Máy Tính Đồng Bộ Dell Core I3, I5, I7 Cũ Nhập Khẩu Giá Rẻ, Máy Tính Đồng Bộ Bộ Dell

Hãy xem nó chuyển động như vậy nào trên tài liệu thực tế:

*
Như 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 cam kết tự trước tiên (“f”) trong chuỗi phụ ví như chuỗi văn phiên bản được đề cập trong within_text gồm chứa cả “function ” và “2013”, bất kỳ có bao nhiêu ký tự khác ở giữa.

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

Thủ thuật Excel nâng cao

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

Trong thực tế, các 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 hợp với các hàm khác ví như MID, LEFT hoặc RIGHT,

Ví dụ 1. Kiếm 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 thêm cách chúng ta cũng có thể tìm cùng trích ra tất cả các cam kết tự vào một chuỗi văn phiên bản ở phía bên trái hoặc bên cần của một ký tự cố thể.

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

Để đạt được tên, bạn 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ố cam kết tự bên từ phía bên trái qua vào một chuỗi. Và bạn sử dụng hàm FIND để xác xác định trí của khoảng tầm trắng (“”) để chất nhận được LEFT biết được gồm bao nhiêu ký kết tự để trích xuất. Tại đó, chúng ta -1 từ vị trí của khoảng tầm trắng vày bạn không thích giá trị trả lại tất cả cả khoảng chừng 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 để cảm nhận tổng số cam kết tự trong chuỗi, từ bỏ đó bạn trừ đi địa chỉ của khoảng tầm trắng:

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

hoặc là

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

Ảnh chụp screen sau minh họa kết quả:

*

Ví dụ 2. Tra cứu sự mở ra thứ N của một ký kết tự nhất thiết trong một chuỗi văn bản

Giả sử chúng ta có một số chuỗi văn phiên bản trong cột A, ví dụ điển hình một danh sách SKU, và bạn muốn tìm vị trí của lốt gạch máy hai (-) vào một chuỗi. Ta có công thức sau đây:

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

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

Để trả lại vị trí của lần lộ diện thứ 3 , bạn nhúng phương pháp 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ý giá trả về:

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

*

Một bí quyết khác và hoàn toàn có thể là một cách dễ dàng và đơn giản hơn để tìm sự xuất hiện thứ N của một ký tự khăng khăng là áp dụng hàm FIND kết phù hợp với CHAR và SUBSTITUTE:

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

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

Trong phương pháp trên, hàm SUBSTITUTE sẽ sửa chữa lần lộ diện thứ 3 của vết gạch (“-“) cùng với CHAR (1), là ký kết tự “Bắt đầu của Tiêu đề” không phải ký từ trong khối hệ thống ASCII. Thay bởi CHAR (1), chúng ta cũng có thể sử dụng bất kỳ ký tự không in được khác từ là 1 đến 31. Và sau đó, hàm FIND trả về vị trí của cam 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 sản phẩm N xảy ra )

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

Chú ý. Hãy nhớ rằng hàm FIND minh bạch chữ hoa chữ thường. Trong ví dụ của bọn 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ữ loại và bạn muốn một chúng không phân biệt chữ hoa chữ thường , hãy thực hiện hàm search thay bởi vì FIND.

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

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

Trong danh sách các SKU của chúng ta, mang sử bạn có nhu cầu tìm 3 cam kết tự đầu tiên sau vết gạch thứ nhất và kéo nó vào một cột khác.

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

=MID(A2, 4, 3)

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

*

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

Công thức FIND nhằm trả lại địa chỉ của thứ nhất của dấu gạch men ngang như sau:

=FIND(“-“,A2)

Bởi vì bạn muốn bắt đầu với ký tự ngay sau lốt gạch ngang, hãy thêm một vào giá trị trả về cùng nhúng hàm nghỉ ngơi trên vào đối số đồ vật hai (start_num) của hàm MID:

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

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

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

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

*

Như chúng ta thấy trong ảnh chụp màn hình hiển thị ở trên, công thức vận động hoàn hảo mang đến hàng 1 cùng 2. Trong những hàng 4 và 5, nhóm đồ vật hai chứa 4 ký tự, tuy nhiên chỉ tất cả 3 cam kết tự trước tiên được trả về. Trong những hàng 6 và 7, chỉ bao gồm 2 cam kết tự vào nhóm trang bị hai và vì vậy công thức Search của bọn họ trả về một vế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ả các ký từ bỏ giữa gấp đôi xuất hiện của một ký kết tự nào đó (trong lấy ví dụ như này là lố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)

*

Để hiểu rõ hơn về công thức MID này, chúng ta hãy để ý từng phần tử của nó:

Phần trước tiên (văn bản). Đó là chuỗi văn bản chứa các ký trường đoản cú mà bạn muốn trích xuất, trong lấy ví dụ như này là ô A2.Phần tử vật dụng hai (vị trí bắt đầu). Chỉ định vị trí của cam kết 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 quý hiếm đó vì chưng vì bạn có nhu cầu bắt đầu với ký tự đi sau lốt gạch ngang: FIND (“-“, A2) +1.Phần tử thứ bố (num_chars). Chỉ định số ký kết tự bạn có nhu cầu trả về. Trong phương pháp của bọn 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 vẫn xác xác định trí của lốt gạch đầu tiên: FIND (“-“, A2). Và hàm thứ hai trả về địa chỉ của vệt gạch ngang vật dụng hai: FIND (“-“, A2, search (“-“, A2) +1). Sau đó, bạn trừ loại trước mang đến sau, rồi kế tiếp trừ 1 vị bạn không thích lấy cả vết gạch ngang. Kết quả là, các bạn sẽ nhận được số ký tự giữa dấu gạch ốp ngang đầu tiên và lắp thêm hai, đó là đúng đắn những gì bọn họ đang search kiếm. Vì vậy, bạn đư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 ký tự sau dấu vật dụng hai:

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

Hoặc, trích xuất tất cả các ký kết tự giữa dấu gạch ngang thiết bị 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. Kiếm tìm văn bản giữa các dấu ngoặc đơn

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

Để làm điều này, bạn phải hàm MID nhằm trả về số ký kết tự mong mỏi muốn xuất phát từ 1 chuỗi, với cả hàm FIND hoặc tìm kiếm để xác định vị trí bắt đầu và số cam kết tự nên trích xuất.

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

Logic của bí quyết này giống như với xúc tích mà chúng ta đã bàn tới trong lấy 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 thấy công thức tất cả bao nhiêu cam kết tự được trả lại. Biểu thức vào đối số num_chars tương đối dài:

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

*

Đương nhiên, không có gì ngăn cản bạn thực hiện hàm FIND thay vì chưng SEARCH, chính vì không cần suy xét việc khác nhau chữ hoa với thường trong lấy ví dụ này.

Để có thể ứng dụng xuất sắc Excel vào vào công việc, họ không chỉ nắm rõ được các hàm nhưng mà còn buộc phải sử dụng giỏi cả những công vậy của Excel. Phần lớn hàm cải thiện giúp áp dụng xuất sắc vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những biện pháp thường thực hiện là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa huấn luyện và đào tạo Excel cơ bản đến chuyên gia dành cho người đi làm