Vishwajeet có một loạt các số bộ phận trong một cột. Các số bộ phận này phải theo một mẫu cụ thể (2 số, 5 chữ cái, 4 số, 1 chữ cái, 1 số, 1 chữ cái và 1 số). Anh ấy tự hỏi liệu có cách nào để dễ dàng xác định ô nào trong cột khác với mẫu này không.

Có một số cách bạn có thể tiếp cận nhiệm vụ này, tùy thuộc vào bản chất thực sự của dữ liệu của bạn. Ví dụ: bạn có thể sử dụng công thức như thế này trong cột trợ giúp:

=AND(LEN(A1)=15,ISNUMBER(--LEFT(A1,2)),ISTEXT(MID(A1,3,5)), ISNUMBER(--MID(A1,8,4)),ISTEXT(MID(A1,12,1)),ISNUMBER(-- MID(A1,13,1)),ISTEXT(MID(A1,14,1)),ISNUMBER(--RIGHT(A1,1)))

Công thức (khá dài) trả về True hoặc False, tùy thuộc vào việc mẫu có đúng hay không. Tuy nhiên, có một vấn đề với công thức. Nó sẽ không bắt các ký hiệu được sử dụng thay cho các chữ cái (chẳng hạn như ký hiệu đô la hoặc dấu hoa thị) và nó sẽ không bắt một số ký hiệu được sử dụng thay cho số (chẳng hạn như dấu chấm hoặc dấu phần trăm). Lý do là hàm ISTEXT coi các biểu tượng là văn bản và hàm ISNUMBER phân tích cú pháp như “1.23” dưới dạng số.

Nếu bạn muốn biết việc sử dụng ký hiệu không hợp lý này, có thể sử dụng công thức sau:

=AND(LEN(A1)=15,ISNUMBER(SUM(SEARCH(MID(A1,{1,2,8,9,10,11, 13,15},1),"0123456789"),SEARCH(MID(A1,{3,4,5,6,7,12,14},1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

Cái này hoạt động vì nó sử dụng hàm TÌM KIẾM để kiểm tra hiệu quả mọi ký tự trong số bộ phận. Người ta sẽ nghĩ rằng bạn có thể cần nhập công thức dưới dạng công thức mảng (kết thúc nó bằng Ctrl + Shift + Enter), nhưng thú vị là, tôi không nhận được sự khác biệt nào về kết quả khi tôi sử dụng nó như một công thức thông thường so với một công thức mảng.

Nếu bạn cần kiểm tra các mẫu số bộ phận khá nhiều, bạn có thể cân nhắc sử dụng macro để kiểm tra. Sau đây là một hàm ngắn do người dùng định nghĩa sử dụng toán tử Like để xem liệu mẫu có được tuân theo hay không.

Function CheckPattern(rCell As Range) As Boolean     Dim sPattern As String

sPattern = "##[A-Z][A-Z][A-Z][A-Z][A-Z]####[A-Z]#[A-Z]#"



CheckPattern = rCell.Value Like sPattern End Function

Lưu ý việc sử dụng biến sPattern. Đây là mô hình cần tuân theo khi toán tử Like thực hiện so sánh. Mỗi lần xuất hiện ký hiệu # có nghĩa là bất kỳ chữ số nào cũng có thể ở vị trí này. Mỗi lần xuất hiện [A-Z] có nghĩa là vị trí có thể là một chữ cái trong phạm vi từ A đến Z.

Bạn có thể tìm thêm về ý nghĩa của các ký tự mà bạn có thể đưa vào mẫu bằng cách truy cập trang này tại một trong các trang của Microsoft:

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator

Trang web này thực sự dành cho Visual Basic, không phải VBA, nhưng trang thông tin cụ thể này sẽ hoạt động tốt trong VBA.

Để sử dụng hàm CheckPattern do người dùng định nghĩa, bạn có thể đặt phần sau vào bất kỳ ô nào trong trang tính của mình:

=CheckPattern(A1)

Điều này giả định rằng số bộ phận nằm trong ô A1, cũng như tất cả các công thức khác được trình bày trong mẹo này.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3391) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.