Trong quá trình làm việc với dữ liệu trên Excel, việc tìm kiếm và lọc thông tin có thể trở thành một thách thức, đặc biệt khi bạn cần xử lý các mẫu chuỗi phức tạp hoặc trích xuất dữ liệu không theo khuôn mẫu cố định. Các chức năng lọc và tìm kiếm thông thường của Excel đôi khi không đủ linh hoạt. Đây chính là lúc Regex trong Excel trở thành một công cụ thay đổi cuộc chơi, giúp bạn xác định chính xác những gì mình cần – dù là các mẫu phức tạp, các phần khớp một phần hay trích xuất dữ liệu có cấu trúc – một cách dễ dàng và hiệu quả hơn bao giờ hết.
Regex là gì? Sức Mạnh Đằng Sau Biểu Thức Chính Quy
Regex (viết tắt của Regular Expression, hay còn gọi là biểu thức chính quy) là một loại mẫu được sử dụng để tìm kiếm sự trùng khớp trong một chuỗi văn bản hoặc một chuỗi ký tự. Bạn đã bao giờ tự hỏi làm thế nào các trang web có thể thông báo rằng địa chỉ email bạn nhập trên trang đăng nhập là không hợp lệ chưa? Đó là một ví dụ về một mẫu Regex đang hoạt động, sử dụng các quy tắc để xác định định dạng email hợp lệ.
Biểu thức chính quy không chỉ có mặt trong Excel mà còn phổ biến 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à thậm chí cả đối thủ của Excel là Google Sheets.
Thoạt nghe, Regex có vẻ phức tạp, và đúng là nó có thể rất phức tạp nếu bạn muốn khai thác toàn bộ tiềm năng của nó. Tuy nhiên, 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à các mẫu cơ bản. Thuthuatonline.net sẽ trình bày hướng dẫn này một cách đơn giản nhất để bạn có thể bắt đầu ứng dụng ngay.
Dưới đây là các ký hiệu Regex cơ bản chúng ta sẽ sử dụng trong bài viết này:
| Ký hiệu | Mô tả |
|---|---|
- |
Chỉ định một phạm vi ký tự trong dấu ngoặc vuông. |
^ |
Khớp với phần đầu của một chuỗi. |
$ |
Khớp với phần cuối của một 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 của ký tự đứng trước. |
+ |
Khớp 1 hoặc nhiều lần của ký tự đứng trước. |
() |
Nhóm các ký tự được khớp thành một nhóm. |
[] |
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 trong dấu ngoặc vuông. |
{n} |
Khớp chính xác n lần của ký tự đứng trước. |
{n,} |
Khớp n hoặc nhiều hơn n 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ố từ 0 đến 9. |
[a-zA-z0-9] |
Đây là sự kết hợp phạm vi, 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 |
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 là $. |
(con) |
Nhóm mẫu con. |
a{3,} |
Khớp 3 hoặc nhiều hơn 3 lần xuất hiện của chữ a (ví dụ: a, aa, hoặc aaa). |
Các hàm Regex là các công thức được định nghĩa trước trong Excel, có thể được sử dụng để xác định một mẫu nhằm tìm kiếm và thao tác với các chuỗi văn bản. Hiện tại có ba hàm Regex chính mà chúng ta sẽ cùng 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.
REGEXTEST: Kiểm Tra Mẫu Chuỗi Dữ Liệu Trong Excel
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 mà 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. Kết quả trả về của hàm sẽ là TRUE hoặc FALSE.
Cú pháp của hàm REGEXTEST như sau:
REGEXTEST(chuoi_can_kiem_tra, mau_regex_can_su_dung, [phan_biet_chu_hoa_chu_thuong])Hai tham số đầu tiên, chuoi_can_kiem_tra và mau_regex_can_su_dung, khá dễ hiểu. Tham số [phan_biet_chu_hoa_chu_thuong] là tùy chọn – bất cứ thứ gì đặt trong dấu ngoặc vuông trong cú pháp Excel đều là tùy chọn – và nó chỉ định liệu 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 (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 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 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 chúng ta đặ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 địa chỉ email.
Ví dụ minh họa hàm REGEXTEST trong Excel, kiểm tra tính hợp lệ của địa chỉ email "[email protected]" và trả về kết quả TRUE.
REGEXEXTRACT: Trích Xuất Dữ Liệu Từ Chuỗi Bằng Biểu Thức Chính Quy
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 ban đầu) khớp với mẫu Regex được cung cấp.
Cú pháp của hàm REGEXEXTRACT như sau:
REGEXEXTRACT(chuoi_can_kiem_tra, mau_regex_can_su_dung, [che_do_tra_ve], [phan_biet_chu_hoa_chu_thuong])Tiếp tục với ví dụ email, hãy thêm một công thức vào ô B4 để trích xuất tên người dùng (username) từ địa chỉ email.
Đây là công thức sẽ trông như sau:
=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 ở ô B3.
Hình ảnh minh họa cách sử dụng hàm REGEXEXTRACT trong Excel để trích xuất phần tên người dùng ("john.doe") từ địa chỉ email.
REGEXREPLACE: Tìm và Thay Thế Chuỗi Nâng Cao Trong Excel
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 hỗ trợ cả 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 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_can_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 sẽ dùng để thay thế chuỗi con tìm được.
- so_lan_xuat_hien: Lần xuất hiện chính xác mà bạn muốn thay thế.
Đâ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].
Ví dụ áp dụng hàm REGEXREPLACE trong Excel để thay thế phần tên người dùng trong địa chỉ email từ "john.doe" thành "jane.doe".
Kết Hợp Hàm Regex Với Các Hàm Excel Khác: Mở Rộng Khả Năng Xử Lý
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 để tạo ra các giải pháp phức tạp hơn. Ví dụ, bạn có thể kết hợp hàm REGEXTEST với câu lệnh IF của Excel và hiển thị thông báo phù hợp dựa trên kết quả.
Dưới đây là một ví dụ về 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 trong ô B6 có hợp lệ hay không, sau đó hiển thị “Đây là địa chỉ email hợp lệ!” nếu kết quả là TRUE hoặc “Địa chỉ email không hợp lệ!” nếu kết quả 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 theo các mẫu cụ thể.
Mặc dù đây không phải là một hướng dẫn toàn diện về biểu thức chính quy – vì chủ đề này đòi hỏi nhiều bài viết riêng biệt – nhưng nó 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 ứng dụng chỉ bị giới hạn bởi sự sáng tạo và nhu cầu của bạn. Hãy bắt đầu thử nghiệm ngay hôm nay để tối ưu hóa công việc xử lý dữ liệu của mình!
