Hàm REGEXREPLACE Excel 365 Mà 90 Phần Trăm Người Dùng Không Biết

Ngay cả khi làm việc với Excel hàng ngày, có những hàm mạnh mẽ nằm trong Microsoft 365 mà hầu hết người dùng chưa từng nghe đến. Hàm REGEXREPLACE là một trong số đó. Nó không xuất hiện trong danh sách hàm phổ biến, không có trong Excel 2019 hay các phiên bản cũ hơn, và chỉ có sẵn trong Excel 365 từ tháng 6 năm 2024. Microsoft giới thiệu nó một cách khá kín đáo trong bản cập nhật kênh thử nghiệm phiên bản 17715.

Mục Lục

Chuẩn hóa số điện thoại trong một bước

Thay vì mười công thức phức tạp

Số điện thoại trong bảng tính thường xuất hiện với vô số định dạng khác nhau. Một cột có thể chứa 0123456789, 012-345-6789, (012) 345-6789, và 012.345.6789 cùng lúc. Hàm REPLACE và SUBSTITUTE truyền thống yêu cầu bạn viết nhiều công thức lồng nhau để xử lý từng trường hợp.

REGEXREPLACE giải quyết tất cả bằng một công thức duy nhất.

Cú pháp cơ bản:

=REGEXREPLACE(văn_bản, biểu_thức_chính_quy, văn_bản_thay_thế)

Ví dụ chuẩn hóa số điện thoại:

Bước 1: Loại bỏ mọi ký tự không phải số

=REGEXREPLACE(A2, "[^0-9]", "")

Mẫu [^0-9] có nghĩa là tất cả ký tự không phải số từ 0 đến 9. Dấu mũ ^ bên trong dấu ngoặc vuông nghĩa là phủ định.

Bước 2: Định dạng lại thành chuẩn

=REGEXREPLACE(REGEXREPLACE(A2,"[^0-9]",""), "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")

Kết quả: Mọi định dạng số điện thoại trong cột A đều chuyển thành dạng (012) 345-6789 thống nhất. Thời gian xử lý một cột nghìn dòng từ mười lăm phút xuống còn ba giây.

Mẫu (\d{3})(\d{3})(\d{4}) chia số thành ba nhóm. Ký hiệu \d đại diện cho chữ số, {3} nghĩa là ba lần. Dấu ngoặc tròn tạo nhóm bắt để sử dụng lại với $1, $2, $3 trong phần thay thế.

Làm sạch dữ liệu nhập từ trang web

Xóa ký tự đặc biệt và biểu tượng cảm xúc trong nháy mắt

Khi sao chép dữ liệu từ trang web hoặc tệp PDF vào Excel, văn bản thường kèm theo ký tự xuống dòng lạ, khoảng trắng dư thừa, biểu tượng cảm xúc, và các ký tự đặc biệt khác. Phương pháp cũ là dùng tìm và thay thế nhiều lần, hoặc viết công thức SUBSTITUTE lồng năm sáu tầng.

XEM THÊM: Hàm LEN Trong Excel Không Chỉ Đếm Ký Tự: 7 Ứng Dụng Bạn Chưa Biết

Loại bỏ mọi ký tự đặc biệt:

=REGEXREPLACE(A2, "[^a-zA-Z0-9 .]", "")

Mẫu này giữ lại chữ cái tiếng Anh, số, khoảng trắng, và dấu chấm. Tất cả ký tự còn lại bị xóa.

Loại bỏ khoảng trắng thừa:

=REGEXREPLACE(A2, "\s+", " ")

Mẫu \s+ tìm một hoặc nhiều khoảng trắng liên tiếp và thay bằng một khoảng trắng duy nhất. Ký hiệu \s đại diện cho mọi loại khoảng trắng bao gồm khoảng cách, tab, và xuống dòng.

Kết hợp cả hai:

=REGEXREPLACE(REGEXREPLACE(A2, "[^a-zA-Z0-9 .]", ""), "\s+", " ")

Bảng dữ liệu khách hàng của tôi từng chứa đầy biểu tượng cảm xúc và ký tự Unicode lạ sau khi nhập từ biểu mẫu trực tuyến. Một công thức REGEXREPLACE xử lý sạch hết năm nghìn dòng thay vì chỉnh sửa thủ công cả ngày.

Chuyển đổi định dạng văn bản tự động

Viết hoa chữ cái đầu, chuẩn hóa cách viết

Excel có hàm UPPER, LOWER, PROPER để chuyển đổi chữ hoa chữ thường. Nhưng chúng không thể chỉ viết hoa ký tự đầu tiên của chuỗi mà để nguyên phần còn lại, hoặc viết hoa những từ cụ thể giữa câu.

Viết hoa chữ cái đầu tiên:

=REGEXREPLACE(A2, "^(.)", "\U$1")

Mẫu ^(.) bắt ký tự đầu tiên của chuỗi. Ký hiệu ^ nghĩa là đầu chuỗi, dấu chấm . khớp với bất kỳ ký tự nào. Trong phần thay thế, \U chuyển thành chữ hoa, $1 là nhóm bắt đầu tiên.

Viết hoa những từ cụ thể:

=REGEXREPLACE(A2, "\b(excel|word|powerpoint)\b", "\U$1", , 1)

Mẫu này tìm ba từ excel, word, powerpoint và viết hoa chúng bất kể vị trí trong câu. Ký hiệu \b là ranh giới từ để đảm bảo khớp từ hoàn chỉnh chứ không phải một phần của từ khác. Tham số cuối cùng là 1 để tắt phân biệt chữ hoa chữ thường.

XEM THÊM: 3 Bước Làm Chủ Hàm NUMBERVALUE Trong Excel 2013 Cho Người Mới

Chuẩn hóa mã sản phẩm:

=REGEXREPLACE(A2, "([a-z]+)(\d+)", "\U$1-$2")

Công thức này chuyển mã như abc123 thành ABC-123. Mẫu ([a-z]+) bắt chữ cái thường, (\d+) bắt số. Thay thế \U$1-$2 viết hoa phần chữ và thêm dấu gạch ngang.

So sánh với REPLACE và SUBSTITUTE cũ

Tại sao REGEXREPLACE thay đổi hoàn toàn cách làm việc

Hàm REPLACE chỉ hoạt động dựa trên vị trí ký tự cố định. Bạn phải biết chính xác ký tự thứ ba đến thứ bảy cần thay đổi. Hàm SUBSTITUTE tìm và thay thế văn bản chính xác, nhưng không thể làm việc với các mẫu biểu thức.

Ví dụ so sánh cụ thể:

Yêu cầu: Loại bỏ số 0 đầu tiên trong mã sản phẩm

Với SUBSTITUTE (không làm được nếu số 0 xuất hiện ở nhiều vị trí):

=SUBSTITUTE(A2, "0", "", 1)

Nếu mã là 007ABC0123, kết quả là 07ABC0123 chứ không phải 7ABC0123.

Với REGEXREPLACE:

=REGEXREPLACE(A2, "^0+", "")

Mẫu ^0+ chỉ khớp các số 0 ở đầu chuỗi. Kết quả chính xác là 7ABC0123.

Hiệu suất:

Tôi thử nghiệm trên bảng dữ liệu mười nghìn dòng cần chuẩn hóa địa chỉ thư điện tử. Phương pháp cũ với năm hàm SUBSTITUTE lồng nhau mất chín giây để tính toán lại khi thay đổi dữ liệu nguồn. REGEXREPLACE hoàn thành trong một phẩy hai giây. Sự khác biệt lớn hơn với các bảng tính phức tạp có nhiều công thức phụ thuộc.

Khả năng xử lý:

SUBSTITUTE chỉ thay thế văn bản giống hệt nhau. REPLACE chỉ làm việc với vị trí cố định. REGEXREPLACE xử lý được mọi mẫu có cấu trúc nhận diện được như thư điện tử, đường dẫn trang web, ngày tháng, mã bưu chính, số tài khoản ngân hàng.

Những mẫu biểu thức hữu ích cho Excel

Áp dụng ngay vào công việc hàng ngày

Dưới đây là các mẫu tôi sử dụng thường xuyên nhất:

Làm việc với số:

\d – Một chữ số bất kỳ
\d{3} – Chính xác ba chữ số
\d+ – Một hoặc nhiều chữ số
[0-9] – Tương đương với \d
[^0-9] – Mọi thứ trừ số

XEM THÊM: Nối Họ Tên Trong Excel Nhanh Gấp 3 Lần Với Hàm CONCAT 2019

Làm việc với văn bản:

[a-z] – Chữ cái thường
[A-Z] – Chữ cái hoa
[a-zA-Z] – Cả chữ hoa và thường
. – Bất kỳ ký tự nào
\s – Khoảng trắng, tab, xuống dòng
\w – Chữ cái, số, và gạch dưới

Ranh giới và vị trí:

^ – Đầu chuỗi
$ – Cuối chuỗi
\b – Ranh giới từ

Số lượng:

+ – Một hoặc nhiều lần
* – Không hoặc nhiều lần
? – Không hoặc một lần
{n} – Chính xác n lần
{n,} – Ít nhất n lần
{n,m} – Từ n đến m lần

Ví dụ thực tế:

Trích xuất tên miền từ thư điện tử:

=REGEXREPLACE(A2, ".*@", "")

Ẩn phần giữa số thẻ tín dụng:

=REGEXREPLACE(A2, "(\d{4})\d{8}(\d{4})", "$1-****-****-$2")

Định dạng ngày từ YYYYMMDD thành DD/MM/YYYY:

=REGEXREPLACE(A2, "(\d{4})(\d{2})(\d{2})", "$3/\/\")

Yêu cầu hệ thống và tương thích

Kiểm tra trước khi sử dụng

REGEXREPLACE chỉ có sẵn trong Excel 365 với các phiên bản sau:

Windows: Phiên bản 2406 số hiệu 17715.20000 trở lên
Mac: Phiên bản 16.87 số hiệu 24071324 trở lên
Excel trên trang web: Có sẵn trong kênh thử nghiệm

Hàm này không tồn tại trong Excel 2021, Excel 2019, Excel 2016, hoặc các phiên bản cũ hơn. Nếu bạn mở tệp chứa REGEXREPLACE trên các phiên bản này, công thức sẽ hiển thị lỗi #NAME?.

Để kiểm tra nhanh Excel của bạn có hỗ trợ hay không, gõ =REGEX vào ô bất kỳ. Nếu danh sách gợi ý hiện ra REGEXTEST, REGEXEXTRACT, REGEXREPLACE thì bạn có thể sử dụng.

Các hàm biểu thức chính quy trong Excel sử dụng chuẩn PCRE2. Nếu bạn quen thuộc với biểu thức chính quy trong Python, JavaScript, hoặc các ngôn ngữ lập trình khác, cú pháp gần như giống hệt nhau.

Một công cụ hữu ích là ChatGPT hoặc Claude để tạo mẫu biểu thức phức tạp. Cung cấp mô tả chi tiết về dữ liệu cần xử lý và yêu cầu công cụ trí tuệ nhân tạo viết mẫu phù hợp. Sau đó dán trực tiếp vào công thức REGEXREPLACE trong Excel.

Bắt đầu với các mẫu đơn giản như loại bỏ số hoặc ký tự đặc biệt. Khi làm quen hơn, chuyển sang các mẫu phức tạp hơn cho địa chỉ thư điện tử, đường dẫn trang web, hoặc cấu trúc dữ liệu đặc thù của công ty bạn. Khả năng này thay đổi hoàn toàn cách xử lý dữ liệu văn bản trong Excel.