Hàm tìm chuỗi ký tự trong excel

     

Với bài viết này, Học Excel Online sẽ giải đáp này giải thích cú pháp của hàm FIND với SEARCH và đưa ra những ví dụ về cách làm rất hữu í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 chuỗi ký tự trong excel


Hàm FIND:

Hàm FIND trong Excel là hàm dò tìm ký tự trong excel được thực hiện để trả lại vị trí 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ố thứ nhất là bắt buộc, sau cuối là tùy chọn.

Find_text – cam kết tự hoặc chuỗi phụ bạn có nhu cầu tìm.Within_text – chuỗi văn bản được tra cứu kiếm. Thông hay nó được coi như như một ô tham chiếu, nhưng chúng ta cũng có thể gõ chuỗi thẳng 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ý kết tự mà bạn bước đầu tìm kiếm. Nếu không nhập, Excel sẽ 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ý kết tự find_text, Excel vẫn trả về lỗi #VALUE!.

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


*

*

*

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

Để sử dụng chính xác công thức FIND – hàm dò tìm ký kết tự vào excel, hãy ghi nhớ hầu như điều đơn giản sau đây:

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

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

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

SEARCH (find_text, within_text, )

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

Dưới đấy là một số công thức cơ phiên bản của SEARCH:

=SEARCH(“market”, “supermarket”)Trả về 6 bởi chuỗi “market ” bước đầu từ ký tự máy 6 của từ “supermarket”.

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

*

So sánh FIND cùng với SEARCH:

Như sẽ đề cập, hàm FIND và tìm kiếm trong Excel rất giống nhau về cú pháp và bí quyết sử dụng. Tuy nhiên, chúng cũng đều có một vài sự không giống biệt.

FIND phân biệt chữ Hoa, tìm kiếm không khác nhau 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 riêng biệt chữ hoa chữ thường, trong những lúc FIND sáng tỏ chữ hoa chữ thường.

Ví dụ, tìm kiếm (“e”, “Excel”) trả về 1 vì nó dìm “E” hoa , trong những khi FIND (“e”, “Excel”) trả về 4 vày nó riêng biệt E và e.

*

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

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

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

Xem thêm: Top 9 Tranh Tô Màu Pokemon Hệ Nước Mới Nhất 2021, Tranh Tô Màu Pokemon Dễ Thương, Đáng Yêu Cho Bé

Hãy xem nó vận động như nạm nào trên dữ liệu thực tế:

*
Như các bạn thấy trong ảnh chụp screen ở trên, công thức tìm kiếm (“functio*2013”, A2) trả về địa điểm của ký kết tự đầu tiên (“f”) vào chuỗi phụ nếu chuỗi văn bản được nhắc trong within_text có chứa cả “function ” và “2013”, bất cứ có từng nào ký tự khác ở giữa.

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

Thủ thuật Excel nâng cao

Các lấy một ví dụ về bí quyết FIND cùng SEARCH:

Trong thực tế, những hàm FIND và tìm kiếm hiếm khi được sử dụng. Thông thường, các 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. Tra cứu một chuỗi trước hoặc theo sau một ký 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ự trong một chuỗi văn phiên bản ở phía bên trái hoặc bên nên của một ký kết tự cố gắng thể.

Giả sử chúng ta có một cột họ cùng tên (cột A) và bạn muốn kéo Tên với Họ sang những cột riêng.

Để đã đạt được tên, chúng ta 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ừ bên trái qua trong một chuỗi. Và bạn thực hiện hàm FIND nhằm xác định vị trí của khoảng chừng trắng (“”) để cho phép LEFT biết được tất cả bao nhiêu ký tự để trích xuất. Tại đó, chúng ta -1 từ vị trí của khoảng trắng vì chưng bạn không thích giá trị trả lại gồm 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 / search và LEN. Hàm LEN để nhận được tổng số ký kết tự vào chuỗi, từ bỏ đó bạn trừ đi địa điểm của khoả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. Tìm kiếm sự mở ra thứ N của một ký kết tự nhất mực 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, ví dụ điển hình một list SKU, và bạn có nhu cầu tìm địa chỉ của dấu gạch đồ vật hai (-) trong một chuỗi. Ta gồm công thức sau đây:

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

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

Để trả lại địa điểm của lần mở ra thứ 3 , bạn nhúng cách làm trên vào đối số start_num của một hàm FIND khác với thêm 2 vào cực hiếm trả về:

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

*

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

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

Trong đó “-” là cam kết tự được đề cập và “3” là lần xuất hiện 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 lần mở ra thứ 3 của lốt gạch (“-“) với CHAR (1), là ký tự “Bắt đầu của Tiêu đề” không phải ký trường đoản cú trong hệ thống ASCII. Thay vị CHAR (1), chúng ta cũng 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 cam kết tự kia trong chuỗi văn bản. Vì vậy, công thức chung là như sau:

= FIND (CHAR (1), SUBSTITUTE ( ô , ký tự , CHAR (1), lần vật dụng N xảy ra )

Thoạt nhìn, có vẻ như những công thức trên ít có giá trị thực tiễn, tuy thế ví dụ tiếp theo sau sẽ cho thấy thêm sự bổ ích của bọn chúng trong việc giải quyết các công việc thực sự.

Chú ý. Hãy nhớ là hàm FIND minh bạch chữ hoa chữ thường. Trong lấy ví dụ như của bọn chúng ta, điều này không có sự không giống biệt, nhưng nếu khách hàng đang thao tác làm việc với các chữ dòng và bạn có nhu cầu một chúng không khác nhau chữ hoa chữ thường , hãy sử dụng hàm tìm kiếm thay vày FIND.

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

Để tra cứu một chuỗi phụ gồm độ dài nhất mực trong bất kỳ chuỗi văn bạn dạng nào, áp dụng hàm FIND hoặc Excel search kết hợp với hàm MID.

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

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

=MID(A2, 4, 3)

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

*

Tuy nhiên, trong các trang tính thực tế, chuỗi phụ bạn phải trích xuất có thể bước đầu bất cứ đâu vào chuỗi văn bản. Trong lấy một ví dụ của chúng ta, chúng ta cũng có thể không biết từng nào ký trường đoản cú đứ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 để xác minh điểm phát xuất của chuỗi nhỏ 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 ngang như sau:

=FIND(“-“,A2)

Bởi vì bạn có nhu cầu bắt đầu với ký tự tức thì sau vệt gạch ngang, hãy thêm một vào quý hiếm trả về và nhúng hàm ngơi nghỉ 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 vừa lòng này, hàm SEARCH hoạt động tốt:

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

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

*

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

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

Tương tự như vậy, bạn có thể trả lại 3 ký kết 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ý tự giữa dấu gạch ốp ngang lắp thêm 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 bạn dạng giữa những dấu ngoặc đơn

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

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

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

Logic của phương pháp này tựa như với ngắn gọn xúc tích mà chúng ta đã bàn tới trong lấy ví dụ trước. Và tương tự như thế, phần phức hợ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 trong đối số num_chars khá dài:

Trước tiên, các bạn tìm thấy địa điểm 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à tính toán sự khác hoàn toàn giữa những vị trí của dấu đóng cùng mở ngoặc với trừ 1 từ con số đó, chính vì bạn không thích có hai dấu ngoặc 1-1 trong kết quả: SEARCH(“)”,A2)-SEARCH(“(“,A2))-1

*

Đương nhiên, không tồn tại gì bức tường ngăn bạn sử dụng hàm FIND thay do SEARCH, cũng chính vì không cần cân nhắc việc sáng tỏ chữ hoa với thường trong lấy một ví dụ này.

Để có thể ứng dụng giỏi Excel vào trong công việc, chúng ta không chỉ nắm vững được những hàm nhưng còn bắt buộc sử dụng giỏi cả những công thay của Excel. đông đảo hàm nâng cao giúp áp dụng giỏi vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những lý lẽ thường áp dụng là Data validation, Conditional formatting, Pivot table…

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