Bạn có thấy việc thao tác với dữ liệu văn bản trong Excel đôi khi trở nên phức tạp? Các chức năng tìm kiếm và lọc thông thường có thể khiến bạn cảm thấy như đang “đoán mò” khi cần xử lý các mẫu dữ liệu phức tạp hoặc trích xuất thông tin cụ thể. Đây là lúc Regex trong Excel phát huy sức mạnh. Biểu thức chính quy (Regex) là một công cụ cực kỳ mạnh mẽ, giúp bạn định vị chính xác những gì mình cần – từ các mẫu phức tạp, khớp một phần, cho đến việc trích xuất dữ liệu có cấu trúc.
Bài viết này sẽ đưa bạn đi sâu vào thế giới của Regex trong Excel, hướng dẫn chi tiết cách sử dụng các hàm chuyên biệt để tìm kiếm, trích xuất và thay thế dữ liệu một cách hiệu quả nhất. Với những kiến thức này, bạn sẽ làm chủ khả năng xử lý chuỗi nâng cao, tối ưu hóa công việc và tiết kiệm thời gian đáng kể.
Regex là gì và Tại sao cần nó trong Excel?
Regex, viết tắt của Regular Expression (Biểu thức chính quy), là một chuỗi các ký tự đặc biệt được sử dụng để mô tả một mẫu (pattern) tìm kiếm trong các chuỗi văn bản. 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 định dạng email bạn nhập không hợp lệ? Đó chính là một ví dụ về mẫu Regex sử dụng để kiểm tra chữ ký email.
Biểu thức chính quy không chỉ giới hạn 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 (như Python, JavaScript), công cụ dòng lệnh, IDE và thậm chí cả đối thủ của Excel là Google Sheets.
Regex nghe có vẻ phức tạp, và nó thực sự 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 chuyên nghiệp để sử dụng nó 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. 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 ứng dụng ngay lập tức.
Dưới đây là các ký hiệu Regex cơ bản mà chúng ta sẽ sử dụng trong hướng dẫn này:
| Ký hiệu | Mô tả |
|---|---|
| – | Chỉ định một khoảng ký tự trong ngoặc vuông (ví dụ: [a-z]). |
| ^ | Khớp với phần đầu của chuỗi. |
| $ | Khớp với phần cuối của chuỗi. |
| . | Khớp với bất kỳ ký tự nào khác ký tự xuống dòng. |
| * | Khớp 0 hoặc nhiều lần ký tự đứng trước. |
| + | Khớp 1 hoặc nhiều lần ký tự đứng trước. |
| () | Nhóm các ký tự khớp thành một (captured group). |
| [] | Khớp với bất kỳ ký tự nào bên trong ngoặc vuông. |
| [^] | Khớp với bất kỳ ký tự nào không nằm trong ngoặc vuông. |
| {n} | Khớp chính xác n lần ký tự đứng trước. |
| {n,} | Khớp n lần trở lên của ký tự đứng trước. |
Dựa vào các ký hiệu này, bạn có thể xây dựng các mẫu Regex đơn giản như sau:
| Mẫu Regex | Mô tả |
|---|---|
[0-9] |
Khớp một chữ số duy nhất từ 0 đến 9. |
[a-zA-z0-9] |
Là một dải 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 lần trở lên của ký tự a (ví dụ: aaa, aaaa). |
Các hàm Regex trong Excel là những công thức được định nghĩa trước, cho phép bạn xác định một mẫu để 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 độc lập cũng như kết hợp với các hàm khác.
Khám phá các Hàm Regex trong Excel
REGEXTEST: Kiểm tra mẫu dữ liệu
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 cầ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 (nếu tìm thấy sự khớp) hoặc FALSE (nếu không tìm thấy).
Cú pháp của hàm REGEXTEST như sau:
REGEXTEST(chuỗi_cần_kiểm_tra, mẫu_regex, [phân_biệt_chữ_hoa_chữ_thường])Hai tham số đầu tiên, chuỗi_cần_kiểm_tra và mẫu_regex, khá dễ hiểu. Tham số [phân_biệt_chữ_hoa_chữ_thường] là tùy chọn (những gì đặt trong ngoặc vuông trong cú pháp Excel đều là tùy chọn). Nó cho biết bạn có muốn tìm kiếm phân biệt chữ hoa chữ thường hay không (0: phân biệt, 1: không phân biệt). 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ách 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 nó có chứa một địa chỉ email hay không, sử dụng mẫu Regex sau:
^[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ì chuỗi đó khớp với định dạng của một địa chỉ email hợp lệ.
Kiểm tra email hợp lệ bằng hàm REGEXTEST trong Excel
REGEXEXTRACT: Trích xuất dữ liệu cụ thể
Tiếp theo, hãy cùng tìm hiểu hàm REGEXEXTRACT. Hàm này sẽ 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(chuỗi_cần_trích_xuất, mẫu_regex, [chế_độ_trả_về], [phân_biệt_chữ_hoa_chữ_thường])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 (username) từ địa chỉ email.
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 vào ô B3. Mẫu ([^@]+) có nghĩa là khớp bất kỳ ký tự nào không phải @ ([^@]) và lặp lại một hoặc nhiều lần (+).
Trích xuất tên người dùng từ email bằng hàm REGEXEXTRACT trong Excel
REGEXREPLACE: Tìm và Thay thế dữ liệu linh hoạt
Hàm Regex cuối cùng mà 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ó thêm khả năng hỗ trợ biểu thức chính quy. Nó nhận một 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 được định nghĩa hay không. Nếu tìm thấy, nó sẽ thay thế chuỗi con đó bằng chuỗi thay thế được cung cấp.
Cú pháp của hàm REGEXREPLACE như sau:
REGEXREPLACE(chuỗi_cần_sửa_đổi, mẫu_regex_cần_tìm, chuỗi_thay_thế, [số_lần_xuất_hiện], [phân_biệt_chữ_hoa_chữ_thường])Dưới đây là các tham số quan trọng cần chú ý trong hàm này:
chuỗi_cần_sửa_đổi: Chuỗi văn bản bạn muốn sửa đổi.chuỗi_thay_thế: Chuỗi sẽ thay thế chuỗi con khớp với mẫu Regex.số_lần_xuất_hiện: Thể hiện vị trí chính xác của lần xuất hiện mà bạn muốn thay thế.
Dưới đây là ví dụ về cách sử dụng hàm này để 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]. Mẫu ^[^@]+ khớp với mọi ký tự từ đầu chuỗi (^) cho đến khi gặp ký tự @.
Thay thế phần tên người dùng trong email bằng hàm REGEXREPLACE của Excel
Tận dụng sức mạnh kết hợp: Regex với các Hàm Excel Khác
Các hàm Regex không chỉ hoạt động độc lập mà còn có thể kết hợp với các hàm khác trong Excel để tạo ra các giải pháp xử lý dữ liệu mạnh mẽ và linh hoạt hơn.
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 để hiển thị thông báo phù hợp dựa trên kết quả kiểm tra.
Đâ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 trong ô B6 có hợp lệ hay không. Nếu kết quả là TRUE, nó sẽ hiển thị “Đây là địa chỉ email hợp lệ!”; nếu là FALSE, nó sẽ hiển thị “Địa chỉ email không hợp lệ!”. Ngoài ra, bạn cũng có thể kết hợp các hàm Regex này với hàm FIND để nhanh chóng tìm kiếm dữ liệu trong Excel hoặc với hàm FILTER để lọc các chuỗi theo mẫu phức tạp.
Nâng Tầm Kỹ Năng Excel của Bạn với Regex
Đây không phải là một hướng dẫn toàn diện về biểu thức chính quy – chủ đề này cần nhiều bài viết riêng để khám phá hết. Tuy nhiên, đây là một khởi đầu tuyệt vời để bạn bắt đầu sử dụng Regex trong Excel. Với các hàm REGEXTEST, REGEXEXTRACT và REGEXREPLACE, bạn đã có trong tay những công cụ mạnh mẽ để tìm kiếm, trích xuất và thay thế dữ liệu văn bản một cách linh hoạt và chính xác hơn bao giờ hết.
Các trường hợp sử dụng và khả năng ứng dụng của Regex 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 ngay hôm nay để nâng tầm kỹ năng Excel của bạn. Nếu bạn có bất kỳ câu hỏi hoặc mẹo sử dụng Regex nào trong Excel, đừng ngần ngại chia sẻ trong phần bình luận bên dưới nhé!
