VietNetwork.Vn

Biểu thức chính quy (hay REGEX) là các mẫu tìm kiếm có thể được sử dụng để kiểm tra xem một chuỗi văn bản có tuân thủ một mẫu nhất định hay không và trích xuất hoặc thay thế các chuỗi văn bản khớp với một mẫu nhất định. Do tính phức tạp của chúng, bài viết này cung cấp các bản tóm tắt hợp lý và ví dụ về cách sử dụng chúng trong Excel.


Các hàm REGEX có sẵn cho những người sử dụng Excel cho Microsoft 365 trên Windows hoặc Mac, cũng như những người sử dụng Excel trên web.

1. Cách sử dụng REGEXTEST

Hàm này kiểm tra xem chuỗi văn bản có khớp với một mẫu nhất định hay không, trả về TRUE hoặc FALSE dựa trên thử nghiệm này. Đây là một cách tuyệt vời để kiểm tra xem dữ liệu của bạn có tuân theo một mẫu nhất định hay không.

1.1. Cú pháp

REGEXTEST(a,b,c)
Trong đó:

    a (bắt buộc) là văn bản, giá trị hoặc tham chiếu ô chứa văn bản bạn muốn kiểm tra,
    b (bắt buộc) là mẫu được sử dụng để thực hiện thử nghiệm và
    c (tùy chọn) là 0 nếu bạn muốn bài kiểm tra phân biệt chữ hoa chữ thường hoặc là 1 nếu không.

1.2. Ví dụ về cách sử dụng REGEXTEST

Bảng tính này chứa danh sách các mã sản phẩm phải tuân theo một cấu trúc nghiêm ngặt.


Một mã hợp lệ bao gồm:

    Biểu diễn kích thước sản phẩm bằng chữ thường ("xs" cho kích thước cực nhỏ, "s" cho kích thước nhỏ, "m" cho kích thước trung bình, v.v.)
    Một số có một hoặc hai chữ số biểu thị vật liệu của sản phẩm,
    Ba chữ cái viết hoa biểu thị nơi sản xuất sản phẩm và
    Một dấu gạch ngang giữa mỗi phần trong ba phần được mô tả ở trên.

Tôi muốn kiểm tra xem tất cả mã sản phẩm có khớp với cấu trúc này không.

Vì vậy, trong ô B2, tôi sẽ nhập:

=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)
Trong đó:

    [@Code] là tham chiếu có cấu trúc đến cột nơi chứa các mã tôi muốn kiểm tra,
    [xs|s|m|l|xl] là phần đầu tiên của mã sản phẩm mà tôi muốn kiểm tra, với các đường thẳng đứng có nghĩa là "hoặc",
    [0-9]{1,2} là phần thứ hai của mã sản phẩm mà tôi muốn kiểm tra, trong đó [0-9] là bất kỳ chữ số đơn nào và {1,2} nghĩa là có thể có một hoặc hai chữ số đơn,
    [AZ]{3} là phần thứ ba của mã sản phẩm mà tôi muốn kiểm tra, trong đó [AZ] là bất kỳ chữ cái viết hoa nào và {3} nghĩa là cần phải có chính xác ba chữ cái này,
    Ba phần của mã mà tôi muốn kiểm tra được phân tách bằng dấu gạch ngang và
    0 là đối số cuối cùng trong công thức cho Excel biết rằng bài kiểm tra này phân biệt chữ hoa chữ thường.

Khi tôi nhấn Enter để áp dụng công thức này cho tất cả các hàng trong cột B, kết quả cho thấy chỉ có hai mã hợp lệ (ĐÚNG).


    m-2- Vương quốc Anh không hợp lệ (được chỉ ra bởi kết quả SAI) vì mã quốc gia chỉ chứa hai chữ cái viết hoa,
    xl- 714 -AUS không hợp lệ vì mã vật liệu chứa ba chữ số và
    S -5-USA không hợp lệ vì mã kích thước là chữ in hoa.

Ví dụ này bao gồm việc sử dụng các ký tự như [ ] và { }. Tuy nhiên, còn nhiều ký tự khác (còn được gọi là mã thông báo) cũng có thể được sử dụng để xác định mẫu được sử dụng để thực hiện thử nghiệm, một số trong số đó tôi sẽ sử dụng trong các ví dụ bên dưới.

2. REGEXEXTRACT: Tìm các đoạn văn bản cụ thể

Hàm này trả về các phần văn bản trong một ô theo một mẫu được chỉ định. Ví dụ, bạn có thể muốn tách số và văn bản.

2.1. Cú pháp

REGEXEXTRACT(d,e,f,g)
Trong đó:

    d (bắt buộc) là văn bản, giá trị hoặc tham chiếu ô chứa văn bản bạn muốn trích xuất từ đó,
    e (bắt buộc) là mẫu bạn muốn trích xuất,
    f (tùy chọn) là 0 nếu bạn chỉ muốn trích xuất kết quả khớp đầu tiên, 1 để trích xuất tất cả các kết quả khớp có thể áp dụng dưới dạng một mảng và 2 để trích xuất các nhóm từ kết quả khớp đầu tiên và
    g (tùy chọn) là 0 nếu bạn muốn trích xuất phân biệt chữ hoa chữ thường hoặc là 1 nếu không.

Vì các bảng Excel được định dạng không thể xử lý các mảng bị tràn, nếu bạn muốn trích xuất các kết quả khớp dưới dạng một mảng trong đối số f, hãy đảm bảo dữ liệu của bạn được định dạng rõ ràng.

2.2. Ví dụ về cách sử dụng REGEXTRACT

Trong ví dụ này, tôi muốn trích xuất tên, họ và số điện thoại của khách hàng thành ba cột riêng biệt.


Trước tiên, chúng ta hãy tập trung vào tên. Trong ô B2, tôi sẽ nhập:

=REGEXEXTRACT(A2,"[AZ][az]+",1)
Trong đó:

    A2 là ô chứa dữ liệu tôi muốn trích xuất,
    [AZ][az] + cho Excel biết rằng tôi muốn trích xuất bất kỳ từ nào bắt đầu bằng chữ in hoa theo sau là chữ thường, với dấu "+" cho biết rằng tôi muốn trả về một hoặc nhiều chữ thường trong mỗi mẫu và
    1 cho biết tôi muốn mỗi ví dụ của mẫu trên được tách thành các ô riêng lẻ dưới dạng một mảng (nói cách khác, tên đầu tiên trong ô B2 và tên thứ hai trong ô C2). Nếu tôi bỏ qua đối số này, Excel sẽ chỉ trả về kết quả khớp đầu tiên (tên đầu tiên) trong ô B2.

Khi tôi nhấn Enter, Excel thực hiện trích xuất thành công và thêm một đường màu xanh nhạt xung quanh ô C2 để nhắc tôi rằng đó là một mảng bị tràn.


Khi đã chọn ô B2, bây giờ tôi có thể sử dụng nút điền ở góc dưới bên phải của ô để sao chép công thức tương đối này vào các hàng chi tiết còn lại.


Bây giờ, tôi cần sử dụng công thức REGEXTRACT tương tự để trích xuất số điện thoại của khách hàng. Trong ô D2, tôi sẽ nhập:

=REGEXTRACT(A2,"[0-9()]+ [0-9-]+")
Trong đó:

    A2 là ô chứa dữ liệu tôi muốn trích xuất,
    [0-9()]+ trích xuất các chữ số từ 0 đến 9 nằm trong dấu ngoặc tròn, với dấu "+" trích xuất một hoặc nhiều chữ số theo mẫu này và
    [0-9-]+ trích xuất các chữ số còn lại từ chuỗi, với dấu "-" thứ hai biểu thị dấu gạch ngang ngăn cách hai phần của số điện thoại và dấu "+" cho Excel biết rằng tôi muốn trích xuất một hoặc nhiều chữ số nếu chuỗi có chứa các chữ số đó.

Vì chỉ có một trường hợp của mẫu này trong mỗi ô ở cột A, tôi không cần thêm bất kỳ đối số nào nữa. Một lần nữa, sau khi tôi đã kiểm tra rằng công thức này tạo ra kết quả mong đợi, tôi có thể sử dụng fill handle để sao chép nó vào các ô còn lại trong cột D.


Trong Excel còn có nhiều cách khác để trích xuất dữ liệu và đạt được kết quả tương tự, chẳng hạn như sử dụng hàm TEXTSPLIT hoặc công cụ Flash Fill của Excel.

3. Thao tác dữ liệu với REGEXREPLACE

Hàm này lấy văn bản trong một ô và tạo phiên bản mới của dữ liệu đó trong một ô khác. Mặc dù hàm này được gọi là REGEXREPLACE, nhưng nó không thực sự thay thế văn bản gốc ở vị trí ban đầu của nó.

3.1. Cú pháp

REGEXREPLACE( h, i, j, k, l )
Trong đó:

    h (bắt buộc) là văn bản, giá trị hoặc tham chiếu ô chứa văn bản bạn muốn thay thế,
    i (bắt buộc) là mẫu bạn muốn thay thế,
    j (bắt buộc) là sự thay thế bạn muốn tạo,
    k (tùy chọn) là sự xuất hiện của mẫu bạn muốn thay thế và
    l (tùy chọn) là 0 nếu bạn muốn thay thế phân biệt chữ hoa chữ thường hoặc là 1 nếu không.

3.2. Ví dụ về cách sử dụng REGEXREPLACE

Bên dưới, bạn có thể thấy danh sách tên trong cột A. Mục đích của tôi là tạo lại các tên này trong cột B, nhưng sử dụng định dạng "Họ, Tên", bao gồm dấu phẩy để phân tách các tên.


Trong ô B2, tôi sẽ nhập:

=REGEXREPLACE([@Client name],"([A-Z][a-z]+) ([A-Z][a-z]+)","$2, $1")
Trong đó:

    [@Tên khách hàng] tham chiếu đến cột chứa dữ liệu mà tôi muốn tác động,
    [AZ][az]+ được đưa vào công thức hai lần (và được phân tách bằng một khoảng trắng) cho Excel biết rằng tôi muốn lấy hai chuỗi văn bản có chứa một chữ cái viết hoa theo sau là một hoặc nhiều chữ cái viết thường và
    $2, $1 cho Excel biết rằng tôi muốn đảo ngược thứ tự của hai chuỗi văn bản đó, được phân tách bằng dấu phẩy và dấu cách. Nếu tôi không bao gồm các ký hiệu đô la, Excel sẽ chỉ trả về "2, 1" làm kết quả trong mỗi ô.

Tôi chưa đề cập đến đối số k và l trong công thức trên vì tôi muốn Excel thay thế tất cả các lần xuất hiện (mặc định cho đối số k ) và tôi muốn sự thay thế này phân biệt chữ hoa chữ thường (mặc định cho đối số l ).

Vì tôi đang sử dụng bảng được định dạng, nên khi tôi nhấn Enter, công thức sẽ áp dụng cho các ô còn lại trong cột B.


Biểu thức chính quy không chỉ dùng trong Excel. Trên thực tế, bạn có thể sử dụng REGEX để tự động hóa các tác vụ khác trên máy tính của mình, như sửa lỗi văn bản PDF đã sao chép-dán, đổi tên hàng loạt các tệp đã tải xuống, định dạng tiền tệ, xóa thẻ HTML, v.v.