Kim có một cột dữ liệu được sử dụng để chứa mã vị trí. Mã này bao gồm một chữ cái duy nhất theo sau là hai chữ số, chẳng hạn như A03 hoặc B12.

Kim muốn định dạng cột có điều kiện để bất kỳ thứ gì được nhập vào cột không sử dụng mẫu này đều được đánh dấu theo một cách nào đó.

Có nhiều cách để tiếp cận vấn đề này. Mỗi cách tiếp cận phụ thuộc vào việc phát triển một công thức có thể được sử dụng trong quy tắc định dạng có điều kiện để trả về True hoặc False và kích hoạt định dạng có điều kiện. (Mẹo này sẽ không đi sâu vào cách tạo quy tắc định dạng có điều kiện, mà thay vào đó sẽ tập trung vào các công thức khác nhau có thể được sử dụng trong quy tắc. Cách bạn tạo quy tắc định dạng có điều kiện được đề cập trong ExcelTips.)

Bất kỳ công thức nào được kết hợp với nhau đều phải kiểm tra ba điều:

  • Có đúng ba ký tự trong chuỗi.

  • Ký tự đầu tiên là một chữ cái.

  • Ký tự thứ hai và thứ ba là chữ số.

Tìm hiểu xem văn bản trong một ô chỉ có ba ký tự khá dễ dàng; bạn có thể sử dụng hàm LEN để làm điều đó:

=LEN(A1)=3

Tìm hiểu xem ký tự đầu tiên có phải là một chữ cái hay không cũng khá dễ dàng. Trên thực tế, có một số cách có thể được thực hiện. Bất kỳ ký tự nào sau đây sẽ trả về True nếu ký tự đầu tiên là một chữ cái:

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

Những kiểm tra này để đảm bảo rằng chỉ có một chữ cái viết hoa ở vị trí đầu tiên. Nếu bạn cũng muốn chấp nhận các chữ cái thường, thì bạn có thể sử dụng một biến thể của thử nghiệm thứ hai:

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

Nếu cả chữ hoa và chữ thường đều được chấp nhận (cùng với hầu như bất kỳ ký hiệu nào khác), thì bạn có thể cân nhắc sử dụng bài kiểm tra sau:

=NOT(ISNUMBER(LEFT(A1,1)+0))

Dưới đây là một số cách bạn có thể áp dụng kiểm tra thứ ba – liệu ký tự thứ hai và thứ ba có phải là chữ số hay không:

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

Lưu ý rằng các phương pháp này xử lý hai ký tự cuối cùng với nhau. Điều này có nghĩa là “1”, “11” và “111” đều sẽ vượt qua bài kiểm tra — chúng được kiểm tra thành công dưới dạng số. Nếu công thức của bạn chỉ kiểm tra hai chữ số cuối cùng, đây có thể là một vấn đề, nhưng thực tế là bạn cũng sẽ bao gồm kiểm tra đầu tiên (cho độ dài tổng thể của chuỗi trong ô và nó phải là 3), thì nó không không có một vấn đề nào cả.

Bí quyết bây giờ là kết hợp phương pháp tiếp cận lựa chọn của bạn cho mỗi trong ba bài kiểm tra thành một công thức duy nhất. Điều này có thể được thực hiện bằng cách sử dụng hàm AND. Tôi sẽ chỉ chọn đoạn ngắn nhất từ ​​mỗi bài kiểm tra và kết hợp chúng theo cách này:

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

Như đã viết, công thức này trả về True nếu tất cả các thử nghiệm đều được vượt qua, có nghĩa là ô chứa mã vị trí có mẫu hợp lệ. Điều này sẽ hoạt động tốt như một định dạng có điều kiện nếu Kim định dạng cột dưới dạng màu (giả sử, màu xanh lá cây) và sau đó sử dụng định dạng có điều kiện để loại bỏ màu xanh lục. Điều này có vẻ lạc hậu và bạn thực sự có thể chỉ muốn áp dụng một định dạng nếu mẫu đó không được đáp ứng. Nếu đúng như vậy, bạn chỉ cần điền công thức vào hàm NOT để đảo ngược giá trị Đúng / Sai được trả về:

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

Như bạn có thể nói, sử dụng một công thức như thế này có thể hơi phức tạp. Nếu muốn, bạn có thể tạo một UDF (hàm do người dùng xác định) sẽ làm cho quy tắc định dạng có điều kiện ngắn hơn một chút. Macro sau đây là một cách tốt để thực hiện:

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

Để sử dụng UDF trong quy tắc định dạng có điều kiện, tất cả những gì bạn cần làm là sử dụng công thức sau:

=IsBadPattern(A1)

Kết quả của UDF sẽ là TRUE nếu chuỗi trong ô được tham chiếu không khớp với mẫu bạn muốn. Như đã viết, nó sẽ không cho phép sử dụng chữ thường ở vị trí ký tự đầu tiên. Nếu bạn cần cho phép các chữ cái thường, bạn không cần thay đổi UDF. Thay vào đó, hãy thay đổi công thức thành sau:

=IsBadPattern(UPPER(A1))

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

Mẹo này (9976) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.