Hướng dẫn Regex trong Excel: Thành thạo đối sánh mẫu với Biểu thức chính quy

Sử dụng RegEx trong Excel

Dù Excel không hỗ trợ RegEx một cách nguyên bản, bạn có thể bật nó bằng cách tự viết hàm tùy chỉnh bằng script VBA (trên Windows) hoặc bật các add-in (trên Mac). Hãy cùng xem cách bật một hàm regex để trích xuất văn bản từ một cột trong Excel.

Cho Windows

Để bắt đầu, nhấn Alt+F11 để mở Visual Basic Editor. Bạn cũng có thể đi tới đó qua Tools > Macro > Visual Basic Editor. Sau đó, nhấp Insert > Module.

Dán hàm sau vào module:

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

Lưu hàm và quay lại bảng tính. Bây giờ, hãy lưu sổ làm việc dưới dạng hỗ trợ macro, với phần mở rộng tệp .xlsm.

Sử dụng hàm trong trang tính Excel

Bằng cách bật macro trong sổ làm việc Excel, bạn có thể dùng hàm CustomRegExpExtract như một hàm thông thường trong Excel để phân tích các mẫu chuỗi phức tạp, trong đó:

• inputText là chuỗi gốc cần phân tích;

• regexPattern là mẫu biểu thức chính quy dùng để phân tích inputText;

• instanceNumber theo mặc định trả về tất cả các lần xuất hiện của mẫu, nhưng có thể nhập một số nguyên để chỉ định số lần xuất hiện cần trả về; và

• matchCase chỉ định có phân biệt chữ hoa/thường khi khớp hay không (TRUE hoặc bỏ trống) hoặc bỏ qua (FALSE).

Giả sử chúng ta muốn trích xuất số điện thoại từ một trường. Ta có thể nhập mẫu regex vào bất kỳ ô nào (trong ví dụ dưới đây, nó nằm ở ô A2). Ở ô A6 là chuỗi tự do ví dụ, "Sophia, 1111111111". Ở ô B6, ta nhập =CustomRegExpExtract(A6,$A$2). Hàm sẽ trích xuất số điện thoại bằng cách đối sánh chuỗi với mẫu regex ở ô A2 và trả về "1111111111", như mong đợi.

Cho Mac OS

Đáng tiếc, Mac OS không nhận diện thư viện MS VBScript Regular Expressions 5.5. Để bật đối sánh mẫu phức tạp trong Excel trên Mac, có hai lựa chọn: dùng bộ lọc nâng cao hoặc cài đặt gói mở rộng.

Sử dụng bộ lọc nâng cao

Bộ lọc nâng cao trong Excel giúp thực hiện các thao tác lọc dữ liệu phức tạp dựa trên tập hợp nhiều tiêu chí. Tính năng này đặc biệt hữu ích khi làm việc với các tập dữ liệu lớn. Cách dùng bộ lọc nâng cao trong Excel như sau:

• Chuẩn bị dữ liệu: Đảm bảo dữ liệu của bạn ở dạng bảng với tiêu đề rõ ràng.

• Tạo Vùng Tiêu chí (Criteria Range): Thiết lập một khu vực riêng trên trang tính để xác định tiêu chí lọc. Khu vực này nên có cùng tiêu đề với các cột bạn muốn lọc. Bên dưới các tiêu đề này, chỉ định tiêu chí lọc. Bạn có thể nhập nhiều tiêu chí dưới cùng một tiêu đề để áp dụng logic "OR", hoặc ở các hàng riêng để áp dụng logic "AND".

• Chọn Dữ liệu: Nhấp vào một ô trong tập dữ liệu của bạn hoặc chọn toàn bộ phạm vi muốn lọc.

• Thiết lập Bộ lọc: Nhấp vào thẻ Data trên ribbon của Excel và đi tới tùy chọn Advanced Filter. Nhấp Advanced trong nhóm Sort & Filter. Trong hộp thoại Advanced Filter, chọn bạn muốn lọc ngay tại chỗ hay sao chép kết quả sang vị trí khác. Chỉ định List range (dữ liệu của bạn). Chỉ định Criteria range (nơi bạn thiết lập điều kiện lọc). Nhấp OK để áp dụng bộ lọc.

Giả sử bạn có tập dữ liệu với các cột Name, Age, City. Bạn muốn lọc để hiển thị những người trên 30 tuổi ở New York hoặc bất kỳ ai ở Los Angeles. Vùng tiêu chí của bạn có thể trông như sau:

Cài đặt add-in

• Kutools for Excel: Kutools là một add-in toàn diện bao gồm nhiều tính năng, trong đó có khả năng tìm và thay thế dữ liệu bằng regex. Nó thêm một thẻ mới vào ribbon của Excel với nhiều công cụ, bao gồm các chức năng regex.

• RegEx Find/Replace: Đây là một add-in cho Excel cho phép bạn tìm và thay thế văn bản bằng biểu thức chính quy. Nó tích hợp vào Excel và cung cấp chức năng tìm/thay thế nâng cao hơn so với tùy chọn tích hợp sẵn.

• PowerGREP: Dù không phải add-in Excel trực tiếp, PowerGREP có thể làm việc với tệp Excel. Đây là công cụ grep mạnh mẽ cho Windows cho phép thao tác regex phức tạp trên tệp văn bản và nhị phân. Nó thiên về ứng dụng độc lập nhưng có thể dùng kết hợp với Excel để xử lý regex nâng cao. Tuy nhiên, không hề rẻ. Giấy phép cho một người dùng có giá 159 đô la và giá tăng lên 5.300 đô la cho 100 người dùng.

Một số add-in có thể tìm trực tiếp trong Excel qua thẻ Insert > Get Add-ins.

Số khác cần tải từ nguồn bên ngoài và làm theo hướng dẫn cài đặt của họ.

Một vài lưu ý:

• Tương thích: Đảm bảo add-in tương thích với phiên bản Excel và hệ điều hành của bạn.
• Bảo mật: Chỉ tải và cài add-in từ nguồn uy tín.
• Nhu cầu chức năng: Cân nhắc các tác vụ regex cụ thể bạn cần thực hiện và chọn add-in phù hợp nhất.

Kết luận

Trong hầu hết trường hợp, các hàm gốc như FIND, SEARCH và REPLACE sẽ đáp ứng 90% nhu cầu xử lý văn bản của bạn trong Excel. Tuy nhiên, với 10% còn lại, trong những tình huống liên quan đến mẫu chuỗi phức tạp, regex có thể hỗ trợ rất nhiều. Để tìm hiểu thêm về việc áp dụng biểu thức chính quy bằng các ngôn ngữ lập trình phổ biến, hãy xem các khóa học Regular Expressions in Python và Intermediate Regular Expressions in R.

Để bắt kịp mọi thứ về Excel, hãy xem lộ trình kỹ năng Excel Fundamentals, hướng dẫn bạn qua những kỹ năng cốt lõi để thành thạo công cụ này.