excel online - excel vlookup



truy Lượt Xem:64

Tìm sự xuất hiện thứ N của một ký tự đã cho trong một chuỗi văn bản

Giả sử bạn có một số chuỗi văn bản trong cột A, hãy nói một danh sách các SKU và bạn muốn tìm vị trí của dấu gạch ngang thứ hai (-) trong một chuỗi. Công thức sau đây sẽ xử lý:
 
=FIND("-", A2, FIND("-",A2)+1)
 
Hai đối số đầu tiên rất dễ diễn giải: định vị dấu gạch ngang ("-") trong ô A2. Trong số thứ ba (start_num), bạn nhúng một hàm FIND mà nói với Excel để bắt đầu tìm kiếm từ 2 nd xảy ra các dấu gạch ngang (FIND ( "-", A2) +1).
 
Để trả về 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 hàm FIND khác và thêm 2 vào giá trị trả về:
 
=FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2)
 
 
Một cách khác và có thể là cách đơn giản hơn để tìm ra sự xuất hiện thứ N của một ký tự đã cho là sử dụng hàm FIND của Excel kết hợp với CHAR và SUBSTITUTE:
=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3))
 
Trong đó "-" là ký tự được đề cập và "3" là lần xuất hiện thứ N bạn muốn tìm.
 
Trong công thức trên, hàm SUBSTITUTE thay thế lần xuất hiện thứ ba của dấu gạch ngang ("-") bằng CHAR (1), là ký tự "Bắt đầu tiêu đề" không thể in được trong hệ thống ASCII. Thay vì CHAR (1) bạn có thể sử dụng bất kỳ ký tự không thể in nào 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 như sau:
 
Hàm FIND (CHAR (1), SUBSTITUTE ( ô , ký tự , CHAR (1), lần xuất hiện thứ N ))
 
Ngay từ cái nhìn đầu tiên, có vẻ như các công thức trên có ít giá trị thực tế, nhưng ví dụ tiếp theo sẽ cho thấy chúng hữu ích như thế nào trong việc giải quyết các nhiệm vụ thực tế.
 
Chú thích. Hãy nhớ rằng hàm FIND của Excel phân biệt chữ hoa chữ thường. Trong ví dụ của chúng tôi, đ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 kết quả phân biệt chữ hoa chữ thường , hãy sử dụng hàm SEARCH thay vì FIND.

Trích xuất các ký tự N theo một ký tự nhất định

Để tìm chuỗi con có độ dài đã cho trong bất kỳ chuỗi văn bản nào, hãy sử dụng TÌM KIẾM Excel hoặc TÌM KIẾM Excel kết hợp với hàm MID. Ví dụ sau đây minh họa cách bạn có thể sử dụng các công thức như vậy trong thực tế.
 
Trong danh sách SKU của chúng tôi, giả sử bạn muốn tìm 3 ký tự đầu tiên theo dấu gạch ngang đầu tiên và kéo chúng vào cột khác.

 

Nếu nhóm ký tự đứng trước dấu gạch ngang đầu tiên luôn chứa cùng một số mục (ví dụ 2 ký tự) thì đây sẽ là một nhiệm vụ tầm thường. Bạn có thể sử dụng hàm MID để trả về 3 ký tự từ một chuỗi, bắt đầu từ vị trí 4 (bỏ qua 2 ký tự đầu tiên và dấu gạch ngang):
 
=MID(A2, 4, 3)
 
Được dịch sang tiếng Anh, công thức cho biết: "Tìm trong ô A2, bắt đầu trích xuất từ ​​ký tự 4 và trả về 3 ký tự".
 
 
Tuy nhiên, trong bảng tính thực tế, chuỗi con bạn cần trích xuất có thể bắt đầu ở bất kỳ đâu trong chuỗi văn bản. Trong ví dụ của chúng tôi, bạn có thể không biết có bao nhiêu ký tự đứng trước dấu gạch ngang đầu tiên. Để đối phó với thử thách này, hãy sử dụng hàm FIND để xác định điểm bắt đầu của chuỗi con mà bạn muốn truy xuất.
 
Công thức FIND để trả về vị trí của dấu gạch ngang thứ nhất như sau:
 
=FIND("-",A2)
 
Vì bạn muốn bắt đầu với ký tự theo dấu gạch ngang, hãy thêm 1 vào giá trị trả về và nhúng hàm trên vào đố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 TÌM KIẾM của Excel hoạt động tốt như nhau:
 
=MID(A2, SEARCH("-",A2)+1, 3)
 
 
Thật tuyệt vời, nhưng nếu nhóm ký tự theo sau dấu gạch ngang đầu tiên chứa một số ký tự khác thì sao? Hmm ... điều này có thể là một vấn đề:
 
 
Như bạn thấy trong ảnh chụp màn hình ở trên, công thức hoạt động hoàn hảo cho các hàng 1 và 2. Trong hàng 4 và 5, nhóm thứ hai chứa 4 ký tự, nhưng chỉ có 3 ký tự đầu tiên được trả về. Trong hàng 6 và 7, chỉ có 2 ký tự trong nhóm thứ hai, và do đó công thức Excel Search của chúng tôi trả về dấu gạch ngang theo sau chúng.
 
Nếu bạn muốn quay trở lại tất cả các ký tự giữa 1 st và 2 nd lần xuất hiện của một nhân vật nào đó (dấu gạch ngang trong ví dụ này), làm thế nào bạn sẽ tiến hành? Đâ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, hãy kiểm tra từng đối số của nó:
 
1 st luận (văn bản). Đó là chuỗi văn bản chứa các ký tự bạn muốn trích xuất, ô A2 trong ví dụ này.
 
2 nd luận (START_POSITION). 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 để xác định vị trí dấu gạch ngang đầu tiên trong chuỗi và thêm 1 vào giá trị đó bởi vì bạn muốn bắt đầu bằng ký tự theo dấu gạch ngang: FIND ("-", A2) +1.
Đối số thứ 3 (num_chars). Chỉ định số ký tự bạn muốn trả về. Trong công thức của chúng tôi, đây là phần khó nhất. Bạn sử dụng hai hàm FIND (hoặc SEARCH), một hàm xác định vị trí của dấu gạch ngang đầu tiên: FIND ("-", A2). Và cột kia trả về vị trí của dấu gạch ngang thứ hai: FIND ("-", A2, FIND ("-", A2) +1). Sau đó, bạn trừ phần tử thứ hai từ thứ hai, và sau đó trừ 1 vì bạn không muốn bao gồm dấu gạch ngang. Kết quả là, bạn sẽ nhận được số ký tự giữa 1 st và 2 nd dấu gạch ngang, đó là chính xác những gì chúng tôi đang tìm kiếm. Vì vậy, bạn nạp giá trị đó vào đối số num_chars của hàm MID.
Theo cách tương tự, bạn có thể trả lại 3 ký tự sau dấu gạch ngang thứ 2 :
 
 
=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 ngang thứ 2 và thứ 3 :
 
=MID(A2, FIND("-", A2, FIND("-",A2)+1)+1, FIND("-",A2, FIND("-", A2, FIND("-",A2)+1) +2) - FIND("-", A2, FIND("-",A2)+1)-1)
 
 

 


Tags:
Bình luận

Bình luận

Các bài viết mới

Các tin cũ hơn