Mẹo Google Sheets · 14 phút đọc
Muốn tách họ và tên trong Google Sheets, dùng =REGEXEXTRACT(TRIM(A2); "[^ ]+$") để lấy tên (chữ cuối) và =REGEXEXTRACT(TRIM(A2); "^[^ ]+") để lấy họ; tên đệm là phần ở giữa. Bài này đi qua từng công thức với tên tiếng Việt có dấu, hàm SPLIT và hai cái bẫy của nó, công cụ Phân tách văn bản thành các cột, cách gộp lại bằng & và TEXTJOIN, tách địa chỉ, mã đơn, số điện thoại, và cách làm tương đương trong Excel.
Giả sử cột A chứa họ tên đầy đủ, bắt đầu từ A2. Ba công thức dưới đây đặt ở B2, C2, D2 rồi kéo xuống là xong. Cả ba đều bọc TRIM trước, để những ô lỡ có dấu cách thừa ở đầu, cuối hoặc giữa vẫn tách đúng.
Thử lại với A2 = "Nguyễn Thị Minh Khai": cột Tên ra Khai, cột Họ ra Nguyễn, cột Tên đệm ra Thị Minh, cột Họ + đệm ra Nguyễn Thị Minh. Với A2 = "Trần Bảo" (không có tên đệm), mẫu tìm tên đệm không khớp nên REGEXEXTRACT báo #N/A; IFNA đổi lỗi đó thành ô trống.
Đọc mẫu tìm như sau: [^ ]+ là "một hoặc nhiều ký tự không phải dấu cách", tức là một chữ. Dấu $ là cuối chuỗi, dấu ^ là đầu chuỗi. Vậy "[^ ]+$" là chữ cuối cùng, "^[^ ]+" là chữ đầu tiên. Phần nằm trong ngoặc đơn ( ) là phần được lấy ra. Chữ có dấu tiếng Việt như "Nguyễn", "Đức" vẫn được tính là một chữ bình thường.
Nếu bạn cần một công thức chạy được cả trên Excel đời cũ, dùng mẹo kinh điển: thay mỗi dấu cách bằng 50 dấu cách, lấy 50 ký tự bên phải, rồi cắt khoảng trắng.
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2); " "; REPT(" "; 50)); 50))Thử lại với "Nguyễn Thị Minh Khai": sau SUBSTITUTE, giữa các chữ là 50 dấu cách. 50 ký tự bên phải gồm 46 dấu cách và "Khai"; TRIM bỏ dấu cách, còn Khai.
Họ và phần họ + đệm lấy bằng LEFT và FIND:
=LEFT(TRIM(A2); FIND(" "; TRIM(A2)&" ")-1)=TRIM(LEFT(TRIM(A2); LEN(TRIM(A2))-LEN(D2)))Thử lại: "Nguyễn Thị Minh Khai" dài 20 ký tự, tên "Khai" dài 4. LEFT lấy 16 ký tự "Nguyễn Thị Minh " (có dấu cách cuối), TRIM bỏ dấu cách, còn Nguyễn Thị Minh. Hàm LEN của Google Sheets đếm "ễ" là một ký tự, nên chữ có dấu không làm lệch kết quả.
SPLIT chia chuỗi theo dấu phân cách và đổ kết quả sang các ô bên phải. Cú pháp: SPLIT(văn_bản; dấu_phân_cách; [tách_theo_từng_ký_tự]; [bỏ_ô_trống]), hai đối số sau mặc định là TRUE.
=SPLIT(TRIM(A2); " ")Thử lại: "Nguyễn Thị Minh Khai" ra 4 ô Nguyễn | Thị | Minh | Khai; "Trần Bảo" ra 2 ô. Vì mỗi người có số chữ khác nhau, các cột sau SPLIT không thẳng hàng: cột E là tên với người 4 chữ nhưng trống với người 3 chữ. Đó là lý do muốn có đúng ba cột Họ, Đệm, Tên thì nên dùng REGEXEXTRACT ở phần trên. Nếu vẫn muốn dùng SPLIT để lấy chữ cuối:
=INDEX(SPLIT(TRIM(A2); " "); 1; COUNTA(SPLIT(TRIM(A2); " ")))Hai bẫy của SPLIT
Thứ nhất, SPLIT(A2; ", ") không tách theo cụm ", " mà tách ở mọi dấu phẩy và mọi dấu cách, vì đối số thứ ba mặc định là TRUE. Muốn tách theo cả cụm, thêm FALSE: SPLIT(A2; ", "; FALSE). Thứ hai, phần tách ra trông giống số có thể bị đổi thành số, nên mã "NV-0012" tách theo "-" có thể ra 12 thay vì 0012. Cần giữ nguyên thì dùng REGEXEXTRACT, hàm này luôn trả về chữ.
Muốn một công thức cho cả cột thay vì kéo xuống, bọc ARRAYFORMULA và bỏ dòng trống: =ARRAYFORMULA(IF(A2:A=""; ""; REGEXEXTRACT(TRIM(A2:A); "[^ ]+$"))). Cách viết và các lỗi #REF! khi bị chặn chỗ tràn có ở bài hàm ARRAYFORMULA.
Khi chỉ cần tách một lần, ví dụ dữ liệu dán từ file khác, dùng công cụ có sẵn:
Kết quả là giá trị tĩnh: sửa ô gốc thì các cột đã tách không đổi theo. Với danh sách họ tên, công cụ này cũng có cùng vấn đề như SPLIT: người 4 chữ ra 4 cột, người 2 chữ ra 2 cột. Công cụ này còn hay được dùng để đổi số dạng chữ thành số thật, xem bài sửa lỗi SUM bằng 0.
Dữ liệu dán từ Zalo, form hay web thường có dấu cách ở đầu, ở cuối hoặc hai dấu cách liền nhau. Mắt không thấy nhưng công thức thì thấy: " Lê Văn An " sẽ cho ra chữ đầu là ô trống nếu không TRIM. Hàm TRIM của Google Sheets bỏ dấu cách ở hai đầu và gộp các dấu cách liền nhau ở giữa thành một.
=PROPER(TRIM(A2))Thử lại: A2 = " nguyễn thị minh KHAI " cho ra Nguyễn Thị Minh Khai. PROPER cũng đưa các chữ còn lại về chữ thường nên "KHAI" thành "Khai". Muốn toàn bộ viết hoa (in phong bì, thẻ tên) thì dùng UPPER.
Dữ liệu chép từ trang web đôi khi có dấu cách không ngắt (CHAR(160)) mà TRIM không xoá được; cách xử lý và các kiểu "gần trùng" khác đã có trong bài lọc dữ liệu trùng lặp. Nếu chỉ cần dọn một lần, dùng Dữ liệu › Dọn dẹp dữ liệu › Cắt bớt khoảng trắng.
Công thức nào cũng dựa trên quy ước "chữ đầu là họ, chữ cuối là tên". Quy ước đó đúng với đa số tên người Việt, nhưng có ngoại lệ bạn nên biết trước khi tách cả nghìn dòng:
| Họ tên | Công thức cho ra | Thực tế có thể là |
|---|---|---|
| Tôn Nữ Thanh Hà | Họ: Tôn | Họ kép: Tôn Nữ (tuỳ gia đình) |
| Âu Dương Minh | Họ: Âu | Họ kép: Âu Dương |
| Nguyễn Ngọc Bảo Anh | Tên: Anh | Có người dùng tên gọi Bảo Anh |
| John Nguyễn | Họ: John, Tên: Nguyễn | Tên nước ngoài đặt trước họ |
Không công thức nào đoán đúng được những trường hợp này. Cách thực tế: tách bằng công thức, rồi lọc những dòng có từ 4 chữ trở lên để soát bằng mắt. Số chữ của một ô đếm bằng =COUNTA(SPLIT(TRIM(A2); " ")). Về lâu dài, nếu danh sách cần sắp theo tên hoặc in thư mời theo tên gọi, hãy để người nhập tự gõ cột Tên riêng ngay từ đầu.
Khi đã có cột Tên, xếp danh sách theo tên rất đơn giản: =SORT(A2:D100; 4; TRUE) xếp cả bảng theo cột thứ 4 (Tên) từ A đến Z.
Chiều ngược lại hay gặp khi file nhận về có Họ, Đệm, Tên ở ba cột riêng mà bạn cần một cột họ tên đầy đủ để in hay tra cứu.
=B2&" "&C2&" "&D2Nhược điểm của dấu &: người không có tên đệm sẽ bị hai dấu cách liền nhau ("Lê An"). Bọc TRIM bên ngoài, hoặc dùng TEXTJOIN với đối số thứ hai là TRUE để bỏ qua ô trống:
=TEXTJOIN(" "; TRUE; B2:D2)CONCATENATE(B2; " "; C2; " "; D2) cho kết quả như dấu & nhưng dài hơn; trong Google Sheets, hàm CONCAT chỉ nhận đúng hai giá trị nên ít dùng.
Gộp ô chứa ngày hoặc số tiền phải bọc TEXT, nếu không ngày hiện thành số sê-ri: ="Vào làm: "&TEXT(E2; "dd/mm/yyyy") cho ra "Vào làm: 05/01/2026"; nối trực tiếp thì ra "Vào làm: 46027". Ngày gõ sai từ đầu thì xem sửa lỗi ngày tháng; nối số tiền có dấu chấm hàng nghìn xem phần hàm TEXT trong bài định dạng số tiền.
Gộp nhiều dòng vào một ô, ví dụ liệt kê tên mọi người phòng Kinh doanh: =TEXTJOIN(", "; TRUE; FILTER(B2:B100; C2:C100="Kinh doanh")). Cách viết điều kiện cho FILTER có ở bài hàm FILTER. Muốn xuống dòng trong ô thay vì dấu phẩy thì thay ", " bằng CHAR(10).
Nút Hợp nhất ô không phải là gộp dữ liệu
Hợp nhất ô (merge) chỉ giữ giá trị của ô trên cùng bên trái và bỏ phần còn lại. Muốn gộp nội dung, dùng công thức ở trên. Gộp xong định xoá cột gốc thì chép cột kết quả, dán chỉ giá trị (Ctrl+Shift+V) trước khi xoá, nếu không công thức sẽ báo #REF!.
Cùng hai hàm SPLIT và REGEXEXTRACT tách được hầu hết các kiểu chuỗi có dấu phân cách rõ ràng.
Địa chỉ. Với A2 = "12 Lê Lợi, Phường Bến Thành, TP. Hồ Chí Minh":
=ARRAYFORMULA(TRIM(SPLIT(A2; ",")))=TRIM(REGEXEXTRACT(A2; "[^,]+$"))Lấy phần cuối an toàn hơn lấy "cột thứ 3", vì địa chỉ cũ có thêm quận/huyện còn địa chỉ mới (từ 01/07/2025) chỉ còn phường/xã và tỉnh/thành, nên số phần mỗi dòng khác nhau.
Mã ghép. Mã hoá đơn kiểu "HD-A101-202509" (tiền tố, phòng, kỳ):
=REGEXEXTRACT(A2; "^[^-]+-([^-]+)")=REGEXEXTRACT(A2; "[^-]+$")Cần dùng phần số để tính toán thì bọc VALUE: =VALUE(REGEXEXTRACT("SP0012"; "\d+")) ra số 12. \d+ là "một hoặc nhiều chữ số".
Số điện thoại lẫn trong ghi chú. A2 = "Anh Tuấn 0912345678 giao chiều":
=IFNA(REGEXEXTRACT(A2; "0\d{9}"); "")Số gõ có dấu cách hay dấu chấm ở giữa (0912 345 678) sẽ không khớp mẫu này; bỏ ký tự thừa trước bằng REGEXREPLACE như trong bài lọc dữ liệu trùng lặp. Cột số điện thoại nên để định dạng văn bản thuần tuý để không mất số 0 ở đầu.
Với danh sách nhân sự, khách hàng, học viên, cách gọn nhất là một cột Họ và tên để nhập và tra cứu, còn cột Tên chỉ tách ra (bằng công thức) khi cần sắp xếp hay in thư theo tên. Người nhập chỉ gõ một lần, không ai phải nhớ tách tay.
Cách tổ chức hồ sơ nhân viên (cột nào nên có, chia nhóm thế nào) có ở bài quản lý hồ sơ nhân sự bằng Google Sheets.
Excel có những công cụ riêng, tuỳ phiên bản:
| Cách | Công thức / thao tác | Phiên bản |
|---|---|---|
| Lấy tên cuối | =TEXTAFTER(TRIM(A2); " "; -1) | Microsoft 365, Excel cho web |
| Lấy họ + đệm | =TEXTBEFORE(TRIM(A2); " "; -1) | Microsoft 365, Excel cho web |
| Tách thành từng chữ | =TEXTSPLIT(TRIM(A2); " ") | Microsoft 365, Excel cho web |
| Lấy tên cuối (mọi bản) | =TRIM(RIGHT(SUBSTITUTE(TRIM(A2); " "; REPT(" "; 50)); 50)) | Mọi phiên bản |
| Flash Fill | Gõ mẫu 1–2 dòng, bấm Ctrl+E | Excel 2013 trở lên |
| Text to Columns | Data › Text to Columns, chọn Delimited, Space | Mọi phiên bản |
| Gộp bỏ ô trống | =TEXTJOIN(" "; TRUE; B2:D2) | Excel 2019 trở lên, 365 |
Số -1 trong TEXTAFTER và TEXTBEFORE nghĩa là "tính từ dấu cách cuối cùng". Thử lại với "Nguyễn Thị Minh Khai": TEXTAFTER ra Khai, TEXTBEFORE ra Nguyễn Thị Minh.
Flash Fill đoán quy luật từ ví dụ bạn gõ: ở B2 gõ "Khai", ở B3 gõ tên của dòng 3, rồi bấm Ctrl+E để Excel điền phần còn lại. Nhanh, nhưng kết quả là giá trị tĩnh và có thể đoán sai với họ tên dài ngắn khác nhau, nên soát lại vài dòng 4 chữ.
Excel dùng dấu ; hay dấu , giữa các đối số tuỳ cài đặt vùng của máy. Hai nền tảng khác nhau ở những điểm khác nữa, xem Google Sheets hay Excel.
Dùng =REGEXEXTRACT(TRIM(A2); "[^ ]+$"). Với "Nguyễn Thị Minh Khai" kết quả là Khai. Cách không dùng REGEX: =TRIM(RIGHT(SUBSTITUTE(TRIM(A2); " "; REPT(" "; 50)); 50)).
Họ: =REGEXEXTRACT(TRIM(A2); "^[^ ]+"). Tên đệm: =IFNA(REGEXEXTRACT(TRIM(A2); "^[^ ]+ (.+) [^ ]+$"); ""). Tên: =REGEXEXTRACT(TRIM(A2); "[^ ]+$"). Người không có tên đệm thì cột giữa để trống.
Có, nếu bạn tách bằng công thức (REGEXEXTRACT, SPLIT). Không, nếu dùng Dữ liệu › Phân tách văn bản thành các cột hoặc Flash Fill trong Excel, vì hai cách đó cho ra giá trị tĩnh.
Dùng =TEXTJOIN(" "; TRUE; B2:D2). Đối số TRUE bỏ qua ô trống. Hoặc dùng =TRIM(B2&" "&C2&" "&D2).
SPLIT có thể đổi phần trông giống số thành số, nên 0012 thành 12. Dùng =REGEXEXTRACT(A2; "[^-]+$"), hàm này luôn trả về chữ nên giữ nguyên 0012.
Microsoft 365 có TEXTSPLIT, TEXTAFTER, TEXTBEFORE: =TEXTAFTER(TRIM(A2); " "; -1) lấy tên cuối. Excel đời cũ dùng công thức RIGHT + SUBSTITUTE, hoặc Flash Fill (Ctrl+E) từ Excel 2013.
Google Sheets hay Excel: so chi phí, làm chung, dùng trên điện thoại, khi mất mạng, giới hạn dữ liệu, Apps Script với VBA và các công thức khác nhau.
Cách định dạng số tiền trong Google Sheets: dấu chấm hàng nghìn, đồng VND, hiện triệu/tỷ, số âm màu đỏ, giữ số 0 đầu số điện thoại, ROUND và hàm TEXT.
Cách khôi phục dữ liệu Google Sheets: lịch sử phiên bản, nhật ký sửa của ô, lấy lại tab bị xoá, file trong Thùng rác 30 ngày và sao lưu tự động hằng tuần.
Apps Script trong Google Sheets cho người mới: mở trình soạn, viết hàm đầu tiên, hàm tuỳ chỉnh, onEdit ghi giờ, trigger hằng ngày, gửi email, cấp quyền, quota.
Xem thêm mẹo và video hướng dẫn trên kênh YouTube HiTaVa, hoặc đọc các bài khác trong chủ đề Mẹo Google Sheets.
Cần hỏi thêm?Zalo 0935077673 (Hải Vân)
