Trong thế giới xử lý dữ liệu, việc tìm kiếm và lọc thông tin trong Excel đôi khi có thể giống như một trò chơi “đoán mò” đầy thách thức, đặc biệt khi đối mặt với các chuỗi ký tự phức tạp hoặc cần trích xuất dữ liệu theo một mẫu cụ thể. Rất may, các hàm biểu thức chính quy (Regex) đã xuất hiện để thay đổi cuộc chơi này. Với Regex, bạn có thể xác định chính xác những gì mình cần – từ các mẫu phức tạp, so 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 hiệu quả và dễ dàng hơn bao giờ hết.
Bài viết này, được biên soạn bởi các chuyên gia của Thuthuatmobile.net, sẽ hướng dẫn bạn cách sử dụng Regex trong Excel để tối ưu hóa quy trình làm việc của mình, biến những tác vụ phức tạp thành các thao tác đơn giản và nhanh chóng.
Regex Là Gì?
Regex (Regular Expression – Biểu thức chính quy) là một loại mẫu được sử dụng để tìm kiếm một chuỗi văn bản hoặc một dãy ký tự theo một quy luật nhất định. Bạn có bao giờ tự hỏi làm thế nào các trang web có thể thông báo rằng mẫu email bạn nhập trên trang đăng nhập là không hợp lệ không? Đó chính là một ví dụ về việc một mẫu Regex đang hoạt động, sử dụng các quy tắc để nhận diện cấu trúc email.
Biểu thức chính quy không chỉ có mặt trong Excel mà còn được ứng 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 (môi trường phát triển tích hợp) và thậm chí là đối thủ của Excel, Google Sheets.
Nghe có vẻ phức tạp, và nó thực sự phức tạp nếu bạn muốn khai thác toàn bộ tiềm năng của nó, nhưng bạn không cần phải là một lập trình viên để sử dụng Regex một cách hiệu quả. Trong nhiều trường hợp, bạn chỉ cần nắm vững cách sử dụng một vài ký hiệu và mẫu cơ bản là đủ. Chúng tôi sẽ giữ hướng dẫn này càng đơn giản càng tốt để bạn có thể bắt đầu sử dụng chúng ngay lập tức.
Dưới đây là các ký hiệu 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 ký tự trong dấu ngoặc vuông. |
^ |
So khớp với phần bắt đầu của chuỗi. |
$ |
So khớp với phần kết thúc của chuỗi. |
. |
So khớp với bất kỳ ký tự nào khác ngoài ký tự xuống dòng mới. |
* |
So khớp 0 hoặc nhiều lần ký tự liền trước. |
+ |
So khớp 1 hoặc nhiều lần ký tự liền trước. |
() |
Nhóm các ký tự đã so khớp thành một nhóm. |
[] |
So khớp bất kỳ ký tự nào bên trong dấu ngoặc vuông. |
[^] |
So khớp bất kỳ ký tự nào không nằm bên trong dấu ngoặc vuông. |
{n} |
So khớp chính xác n lần xuất hiện của ký tự liền trước. |
{n,} |
So khớp n lần hoặc nhiều hơn của ký tự liền trước. |
Các mẫu Regex đơn giản bạn có thể xây dựng với các ký hiệu này bao gồm:
| Mẫu Regex | Mô tả |
|---|---|
[0-9] |
So khớp một chữ số từ 0 đến 9. |
[a-zA-Z0-9] |
Đây là một phạm vi kết hợp so khớp một ký tự đơn từ chữ thường a đến z, chữ hoa A đến Z, và từ 0 đến 9. |
^pro |
So khớp bất kỳ chuỗi nào bắt đầu bằng pro. |
[^$] |
So khớp bất kỳ ký tự nào không phải là $. |
(con) |
Nhóm mẫu con. |
a{3,} |
So khớp 3 hoặc nhiều hơn các lần xuất hiện của ký tự a (ví dụ: a, aa, hoặc aaa). |
Các hàm Regex là các công thức Excel được định nghĩa trước, có thể được sử dụng để xác định một mẫu nhằm tìm kiếm và thao tác cá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 biệt cũng như kết hợp với các hàm khác.
Tìm kiếm Mẫu với 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 dùng để tìm kiếm và một mẫu Regex, sau đó sử dụng mẫu Regex để tìm một sự trùng khớp trong chuỗi văn bản đó. Hàm sẽ trả về giá trị TRUE hoặc FALSE.
Cú pháp của hàm REGEXTEST như sau:
REGEXTEST(chuoi_can_tim_kiem, mau_regex_su_dung, [phan_biet_chu_hoa_chu_thuong])Hai tham số đầu tiên, chuoi_can_tim_kiem và mau_regex_su_dung, khá dễ hiểu. Tham số [phan_biet_chu_hoa_chu_thuong] là tùy chọn – bất kỳ thứ gì đặt trong dấu ngoặc vuông khi nói về cú pháp Excel đều là tùy chọn – và cho biết bạn có muốn tìm kiếm phân biệt chữ hoa chữ thường (0) hay không phân biệt chữ hoa chữ thường (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ệ chưa bằ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 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 tôi đặt công thức vào ô C3 và nhập [email protected] vào ô B3, nó sẽ trả về TRUE vì địa chỉ đó khớp với cấu trúc của một email.
Hình ảnh minh họa hàm REGEXTEST trong Excel kiểm tra định dạng email hợp lệ.
Trích xuất Dữ liệu với REGEXEXTRACT
Tiếp theo, hãy cùng xem 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(chuoi_can_tim_kiem, mau_regex_su_dung, [che_do_tra_ve], [phan_biet_chu_hoa_chu_thuong])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 của đị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.
Hình ảnh minh họa hàm REGEXEXTRACT trong Excel trích xuất phần tên người dùng từ địa chỉ email.
Tìm và Thay thế với REGEXREPLACE
Hàm Regex cuối cùng chúng ta sẽ tìm hiểu là REGEXREPLACE. Hàm này tương tự như hàm REPLACE của Excel, nhưng cũng hỗ trợ Regex. Nó nhận chuỗi văn bản bạn muốn sửa đổi và kiểm tra xem có 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ế chuỗi đó bằng chuỗi thay thế được cung cấp.
Cú pháp của hàm REGEXREPLACE như sau:
REGEXREPLACE(chuoi_can_sua_doi, mau_regex_su_dung, chuoi_thay_the, [so_lan_xuat_hien], [phan_biet_chu_hoa_chu_thuong])Dưới đây là các tham số quan trọng cần chú ý trong hàm này:
- chuoi_can_sua_doi: Chuỗi văn bản bạn muốn sửa đổi.
- chuoi_thay_the: Chuỗi để thay thế chuỗi con tìm thấy.
- so_lan_xuat_hien: Lần xuất hiện chính xác bạn muốn thay thế.
Đây là một 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 minh họa hàm REGEXREPLACE trong Excel thay thế phần tên người dùng của địa chỉ 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 trong Excel và hiển thị thông báo phù 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 và 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 là một hướng dẫn toàn diện về biểu thức chính quy – điều đó sẽ đòi hỏi nhiều bài viết riêng. Tuy nhiên, đây là một cách tốt để 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 bắt đầu khám phá và ứng dụng Regex để nâng tầm kỹ năng xử lý dữ liệu của bạn ngay hôm nay!
