Trong thế giới của dữ liệu, việc tìm kiếm và lọc thông tin trong Excel đôi khi giống như mò kim đáy bể. Nhưng với sự ra đời của các hàm Regex (biểu thức chính quy), mọi thứ đã thay đổi. Giờ đây, bạn có thể “ghim” chính xác những gì mình cần – từ các mẫu phức tạp, các phần khớp một phần cho đến việc trích xuất dữ liệu có cấu trúc – một cách dễ dàng và hiệu quả. thuthuat360.net tự hào mang đến cho bạn hướng dẫn chi tiết về cách khai thác sức mạnh của Regex trong Excel, giúp bạn tối ưu hóa công việc và khẳng định vị thế chuyên gia trong lĩnh vực công nghệ.
Regex là gì?
Regex, hay Biểu thức chính quy (Regular Expression), là một chuỗi các ký tự định nghĩa một mẫu tìm kiếm. Bạn đã bao giờ tự hỏi làm thế nào các trang web có thể thông báo rằng định dạng email bạn nhập vào là không hợp lệ chưa? Đó chính là một ví dụ về mẫu Regex được sử dụng để kiểm tra chữ ký email.
Các biểu thức chính quy không chỉ có trong Excel mà còn được sử dụng rộng rãi trong nhiều trình soạn thảo văn bản, ngôn ngữ lập trình, công cụ dòng lệnh, IDE và ngay cả trong đối thủ của Excel là Google Sheets.
Mặc dù Regex nghe có vẻ phức tạp và thực tế nó có thể rất mạnh mẽ, bạn không cần phải là một lập trình viên để sử dụng chúng hiệu quả. Trong nhiều trường hợp, việc nắm vững một vài ký hiệu và mẫu cơ bản là đủ để bắt đầu. thuthuat360.net sẽ giữ hướng dẫn này càng đơn giản càng tốt để bạn có thể nhanh chóng ứng dụng chúng vào công việc.
Dưới đây là các ký hiệu Regex cơ bản chúng ta sẽ sử dụng trong hướng dẫn này:
| Ký hiệu | Mô tả |
|---|---|
| – | Chỉ định một phạm vi các ký tự trong dấu ngoặc vuông. |
| ^ | Khớp với phần bắt đầu của chuỗi. |
| $ | Khớp với phần kết thúc của chuỗi. |
| . | Khớp với bất kỳ ký tự nào ngoại trừ ký tự xuống dòng. |
| * | Khớp 0 hoặc nhiều lần ký tự đứng trước nó. |
| + | Khớp 1 hoặc nhiều lần ký tự đứng trước nó. |
| () | Nhóm các ký tự được khớp thành một. |
| [] | Khớp với bất kỳ ký tự nào bên trong dấu ngoặc vuông. |
| [^] | Khớp với bất kỳ ký tự nào không nằm bên trong dấu ngoặc vuông. |
| {n} | Khớp chính xác n lần xuất hiện của ký tự đứng trước. |
| {n,} | Khớp n hoặc nhiều lần xuất hiện của ký tự đứng trước. |
Dưới đây là một số mẫu Regex đơn giản bạn có thể xây dựng với các ký hiệu trên:
| Mẫu Regex | Mô tả |
|---|---|
| [0-9] | Khớp một chữ số duy nhất từ 0 đến 9. |
| [a-zA-z0-9] | Đây là một phạm vi kết hợp, khớp một ký tự duy nhất từ chữ thường a đến z, chữ hoa A đến Z và từ 0 đến 9. |
| ^pro | Khớp bất kỳ chuỗi nào bắt đầu bằng pro. |
| [^$] | Khớp bất kỳ ký tự nào không phải $. |
| (con) | Nhóm mẫu con. |
| a{3,} | Khớp 3 hoặc nhiều hơn các lần xuất hiện của chữ a (ví dụ: a, aa, hoặc aaa). |
Các hàm Regex là những công thức Excel được định nghĩa sẵn, có thể được sử dụng để định nghĩa một mẫu để tìm kiếm và thao tác chuỗi văn bản. Hiện tại có ba hàm Regex chính, và chúng ta sẽ tìm hiểu cách sử dụng chúng một cách riêng lẻ và kết hợp với các hàm khác.
Tìm kiếm mẫu với hàm REGEXTEST
Hàm đầu tiên chúng ta sẽ tìm hiểu là REGEXTEST. Hàm này nhận một chuỗi văn bản bạn muốn tìm kiếm và một mẫu Regex, sau đó sử dụng mẫu đó để tìm sự trùng khớp trong chuỗi. Hàm sẽ trả về giá trị TRUE hoặc FALSE.
Cú pháp của hàm REGEXTEST như sau:
REGEXTEST(string_to_search, regex_pattern_to_use, [case_sensitivity])Hai tham số đầu tiên, string_to_search (chuỗi cần tìm) và regex_pattern_to_use (mẫu Regex cần sử dụng), rất dễ hiểu. Tham số [case_sensitivity] là tùy chọn – bất cứ điều gì đặt trong dấu ngoặc vuông trong cú pháp Excel đều là tùy chọn – và cho biết bạn muốn tìm kiếm có phân biệt chữ hoa/chữ thường (0) hay không phân biệt (1). Mặc định là phân biệt chữ hoa/chữ thường.
Trong ví dụ của chúng ta, chúng ta sẽ sử dụng REGEXTEST để kiểm tra xem người dùng đã nhập địa chỉ email hợp lệ hay chưa, sử dụng công thức sau:
REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")Ở đây, chúng ta đang tìm kiếm trong ô B3 để xem liệu nó có chứa một địa chỉ email hay không, sử dụng mẫu Regex dưới đây:
^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$Nếu bạn đặt công thức này vào ô C3 và nhập [email protected] vào ô B3, nó sẽ trả về TRUE vì chuỗi đó khớp với định dạng của một email.
Ví dụ minh họa cách sử dụng hàm REGEXTEST trong Excel để kiểm tra tính hợp lệ của địa chỉ email.
Trích xuất dữ liệu bằng hàm REGEXEXTRACT
Tiếp theo, hãy cùng tìm hiểu hàm REGEXEXTRACT. Hàm này trả về một chuỗi con (một phần của chuỗi) khớp với mẫu Regex được cung cấp.
Cú pháp của hàm REGEXEXTRACT như sau:
REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_sensitivity])Tiếp tục với ví dụ về email, hãy thêm một công thức vào ô B4 để trích xuất tên người dùng từ địa chỉ email.
Công thức sẽ trông như thế này:
=REGEXEXTRACT(B3, "([^@]+)")Trong công thức này, chúng ta trích xuất mọi thứ trước ký hiệu @ trong địa chỉ email được nhập vào ô B3.
Ảnh chụp màn hình Excel minh họa hàm REGEXEXTRACT trích xuất tên người dùng từ địa chỉ email.
Tìm và thay thế với hàm REGEXREPLACE
Hàm Regex cuối cùng chúng ta sẽ xem xét là REGEXREPLACE. Hàm này tương tự như hàm REPLACE của Excel, nhưng có hỗ trợ RegEx. Hàm này nhận chuỗi văn bản bạn muốn sửa đổi và kiểm tra xem có một chuỗi con nào khớp với mẫu Regex đã định nghĩa hay không. Nếu tìm thấy, nó sẽ thay thế bằng chuỗi thay thế được cung cấp.
Cú pháp của hàm REGEXREPLACE như sau:
REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_sensitivity])Đây là các tham số quan trọng cần chú ý trong hàm này:
string_to_modify: Chuỗi văn bản bạn muốn sửa đổi.replacement_string: Chuỗi sẽ được dùng để thay thế chuỗi con tìm thấy.number_of_occurrences: Lần xuất hiện chính xác bạn muốn thay thế.
Dưới đây là ví dụ về việc sử dụng hàm để thay thế phần tên người dùng của email bằng một chuỗi văn bản khác:
=REGEXREPLACE(B3, "^[^@]+", "jane.doe")Giá trị của ô B3 là [email protected], và sau khi chúng ta nhập công thức trên vào ô C3, nó sẽ trả về [email protected].
Hình ảnh Excel cho thấy kết quả của hàm REGEXREPLACE khi thay thế phần tên người dùng trong email.
Kết hợp hàm Regex với các hàm Excel khác
Bạn cũng có thể kết hợp các hàm Regex với các hàm khác trong Excel. Ví dụ, bạn có thể kết hợp hàm REGEXTEST với một câu lệnh IF của Excel và hiển thị thông báo thích hợp dựa trên kết quả.
Đây là một ví dụ công thức:
=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"), "Đây là địa chỉ email hợp lệ!", "Địa chỉ email không hợp lệ!")Công thức này sử dụng câu lệnh IF để kiểm tra xem địa chỉ email được nhập vào ô B6 có hợp lệ hay không, sau đó hiển thị “Đây là địa chỉ email hợp lệ!” nếu nó là TRUE hoặc “Địa chỉ email không hợp lệ!” nếu nó là FALSE. Ngoài ra, bạn có thể kết hợp điều này với hàm FIND để nhanh chóng tìm dữ liệu trong Excel.
Bài viết này không nhằm mục đích trở thành một hướng dẫn toàn diện về biểu thức chính quy – điều đó sẽ cần nhiều bài viết riêng. Tuy nhiên, đây là một cách tuyệt vời để bạn bắt đầu sử dụng Regex trong Excel. Các trường hợp sử dụng và khả năng chỉ bị giới hạn bởi trí tưởng tượng của bạn. Hãy khám phá và biến Excel thành công cụ xử lý dữ liệu mạnh mẽ hơn bao giờ hết!
