2026-09-25 · Power Query & Excel
Một cột "Họ và tên" cần tách thành họ, tên đệm và tên để sắp xếp theo tên. Một cột mã hồ sơ kiểu "BH-HN-0002" cần tách ra loại hồ sơ và chi nhánh để làm báo cáo. Một cột tiền nhìn như số nhưng cộng ra 0 vì Excel đang coi nó là chữ. Ba việc khác nhau, cùng một nhóm công cụ: tách cột trong Excel.
Bài này so sánh 4 cách tách họ tên và tách cột: Text to Columns, Flash Fill, hàm và Power Query. Mỗi cách có một chỗ làm hỏng dữ liệu mà không báo lỗi. Ví dụ lấy từ hai file demo của EzTech: bảng 1.000 hồ sơ công nợ và danh sách 16 nhân viên bán hàng.
Hai file demo dùng trong bài
- Bảng hồ sơ công nợ: 1.000 dòng, cột Mã hồ sơ dạng Loại-Chi nhánh-Số (BH-HO-0001, DV-HN-0002...), cột Khách hàng có 892 dòng kèm hậu tố chi nhánh như "Cty TNHH Minh Phát - CN Hà Nội" và 108 dòng không có hậu tố (hồ sơ tại trụ sở), hai cột Thành tiền và Đã thanh toán lưu dạng chữ theo kiểu "6,700,000.50".
- Danh sách nhân viên: 16 người, ví dụ Nguyễn Văn An, Vũ Đình Phong, Đỗ Thị Mai.
Cách 1: Text to Columns
Công cụ có sẵn từ lâu, hợp với dữ liệu có dấu phân cách cố định như mã hồ sơ.
- Chèn sẵn hai cột trống bên phải cột Mã hồ sơ. Text to Columns ghi đè lên các cột bên cạnh.
- Chọn cột Mã hồ sơ, vào Data → Text to Columns.
- Bước 1 chọn Delimited. Bước 2 bỏ tích Tab, tích Other và gõ dấu
-. - Bước 3: bấm vào cột thứ ba trong khung xem trước, chọn Column data format là Text. Bấm Finish.
Kết quả là ba cột: loại hồ sơ (BH 334 dòng, DV 333, TM 333), mã chi nhánh (10 mã, từ HO đến BD) và số thứ tự.
Chỗ làm hỏng dữ liệu: bỏ qua bước chọn Text ở cột thứ ba thì "0001" thành số 1, "0002" thành 2. Số 0 đứng đầu mất hẳn, và nếu cần ghép lại mã hồ sơ để tra cứu thì không khớp nữa.
Dùng Text to Columns đổi chữ thành số
Cột Thành tiền của file demo lưu "3,000,000.00" dạng chữ, nên hàm SUM trả về 0. Gõ lại từng ô cũng không ăn thua trên máy đặt thiết lập tiếng Việt, vì ở đó dấu phẩy là dấu thập phân. Cách đổi nhanh:
- Chọn cột Thành tiền, vào Data → Text to Columns.
- Bước 1 chọn Delimited, bước 2 bỏ tích mọi dấu phân cách.
- Bước 3 chọn General, bấm Advanced...: Decimal separator gõ dấu chấm, Thousands separator gõ dấu phẩy. Bấm OK rồi Finish.
Làm cho cả hai cột tiền, tổng Thành tiền ra 51.315.000.375 đồng, tổng Đã thanh toán 40.998.300.375 đồng, còn phải thu 10.316.700.000 đồng. Trước khi đổi, cả hai tổng đều bằng 0.
Giới hạn: chỉ nhận một ký tự phân cách
Muốn tách "Cty TNHH Minh Phát - CN Hà Nội" thành tên khách và chi nhánh, dấu phân cách đúng là cụm " - " gồm ba ký tự. Ô Other chỉ nhận một ký tự, nên phải tách theo dấu -, sau đó bỏ khoảng trắng thừa ở hai cột kết quả. Tên khách nào có sẵn dấu gạch nối trong tên cũng sẽ bị cắt đôi.
Cách 2: Flash Fill (Ctrl + E)
Gõ kết quả mong muốn cho một hai dòng đầu, Excel đoán quy tắc và điền phần còn lại.
- Cột bên cạnh "Nguyễn Văn An", gõ An.
- Xuống ô dưới, nhấn Ctrl + E (hoặc Data → Flash Fill).
Với 16 nhân viên của file demo, cả 16 tên đều đúng ba chữ, nên dù Flash Fill đoán quy tắc là "chữ thứ ba" hay "chữ cuối cùng" thì kết quả vẫn như nhau.
Chỗ làm hỏng dữ liệu: Flash Fill cho ra giá trị cố định, không phải công thức. Sửa tên ở cột gốc thì cột tên không đổi theo. Khi dữ liệu có tên hai chữ hoặc bốn chữ, Flash Fill có thể đoán theo quy tắc "chữ thứ ba" thay vì "chữ cuối cùng", và không báo gì. Sau khi Ctrl + E, lọc thử cột kết quả, xem có ô nào trống hoặc lạ.
Cách 3: dùng hàm
Hàm tự cập nhật khi dữ liệu gốc đổi. Với Excel Microsoft 365 hoặc Excel 2024, dùng TEXTBEFORE và TEXTAFTER, ô A2 chứa họ tên:
- Họ:
=TEXTBEFORE(TRIM(A2)," ") - Tên:
=TEXTAFTER(TRIM(A2)," ",-1)(số -1 nghĩa là lấy phần sau dấu cách cuối cùng) - Tên đệm:
=TEXTBEFORE(TEXTAFTER(TRIM(A2)," ")," ",-1,,,"")(tên chỉ có hai chữ thì trả về ô trống)
Với Excel bản cũ hơn, lấy tên (chữ cuối cùng) bằng công thức:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),100))
và lấy họ bằng =LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1).
Hàm TRIM bọc ngoài để xử lý khoảng trắng thừa: "Nguyễn Văn An" có hai dấu cách liền nhau sẽ làm công thức tách sai chỗ.
Cách 4: Power Query
Hợp khi việc tách lặp lại mỗi tháng trên file mới. Các bước được ghi lại và chạy lại khi Refresh.
Tách họ tên theo dấu cách cuối cùng
- Chọn bảng, vào Data → From Table/Range để mở Power Query Editor.
- Bấm vào cột Họ và tên, chọn Home → Split Column → By Delimiter.
- Chọn Space, ở mục Split at chọn Right-most delimiter. Bấm OK.
Kết quả là hai cột: "Nguyễn Văn" và "An". Tên hai chữ, ba chữ hay bốn chữ đều cho ra đúng tên ở cột thứ hai. Muốn tách tiếp họ và tên đệm, chọn cột thứ nhất, Split Column theo Space, lần này chọn Left-most delimiter.
Tách tên khách và chi nhánh bằng cụm " - "
Split Column → By Delimiter → chọn Custom, gõ cụm - (dấu cách, gạch, dấu cách). Power Query nhận dấu phân cách nhiều ký tự, nên tên khách có gạch nối bên trong không bị cắt. 108 hồ sơ tại trụ sở không có hậu tố sẽ nhận giá trị null ở cột chi nhánh; dùng Transform → Replace Values, thay null bằng "Trụ sở".
Bẫy của Power Query: bước Changed Type tự thêm
Tách cột Mã hồ sơ theo dấu - xong, nhìn khung Applied Steps sẽ thấy một bước Changed Type tự xuất hiện. Power Query thấy cột thứ ba toàn chữ số nên đổi sang Whole Number, và "0001" lại thành 1, giống lỗi của Text to Columns. Cách sửa: bấm vào biểu tượng kiểu dữ liệu ở đầu cột đó, chọn Text, rồi chọn Replace current để sửa luôn bước Changed Type thay vì thêm bước mới.
Đổi cột tiền dạng chữ sang số trong Power Query: bấm chuột phải cột Thành tiền → Change Type → Using Locale..., chọn Data Type là Decimal Number, Locale là English (United States). Power Query sẽ đọc dấu phẩy là phân cách nghìn và dấu chấm là phân cách thập phân, bất kể máy đang đặt thiết lập vùng nào.
Chọn cách nào
| Cách | Hợp khi | Tự cập nhật | Cần chú ý |
|---|---|---|---|
| Text to Columns | Tách một lần, dấu phân cách một ký tự | Không | Chọn Text cho cột có số 0 đứng đầu; ghi đè cột bên cạnh |
| Flash Fill | Tách nhanh vài trăm dòng có quy luật đều | Không | Lọc lại kết quả, dễ đoán sai với tên dài ngắn khác nhau |
| Hàm | Dữ liệu gốc còn được sửa, cần kết quả đổi theo | Có | Bọc TRIM; TEXTBEFORE/TEXTAFTER cần Excel 365 hoặc 2024 |
| Power Query | Việc lặp lại mỗi tháng trên file mới | Có, khi Refresh | Kiểm tra bước Changed Type tự thêm |
Câu hỏi thường gặp
Tên có bốn chữ như "Nguyễn Thị Minh Khai" thì tách thế nào?
Tách theo dấu cách cuối cùng (hàm TEXTAFTER với số -1, hoặc Right-most delimiter trong Power Query) cho ra tên là "Khai" và phần còn lại là "Nguyễn Thị Minh". Tách tiếp phần còn lại theo dấu cách đầu tiên để lấy họ "Nguyễn" và tên đệm "Thị Minh".
Tên có họ kép như "Tôn Nữ" hay "Âu Dương" thì sao?
Không công cụ nào tự biết đâu là họ kép. Tách theo quy tắc chung rồi lập một danh sách họ kép để sửa riêng các dòng đó. Việc tách tên luôn chính xác nhất nếu hệ thống nhập liệu có sẵn hai ô riêng cho họ và tên.
Column From Examples trong Power Query khác Flash Fill thế nào?
Cách dùng giống nhau: gõ vài dòng mẫu để Power Query đoán quy tắc (Add Column → Column From Examples). Khác ở chỗ Power Query viết quy tắc đoán được thành một công thức hiện trên đầu hộp thoại, đọc được và kiểm tra được, và công thức đó chạy lại ở mọi lần Refresh.
Bắt đầu từ đâu
Nếu chỉ tách một lần, dùng Text to Columns hoặc Flash Fill rồi kiểm tra lại kết quả. Nếu tháng nào cũng nhận một file mới cần tách giống hệt, dựng bằng Power Query một lần. Dữ liệu xuất từ phần mềm kế toán thường cần tách và đổi kiểu cùng lúc, có ví dụ đầy đủ ở bài xử lý dữ liệu MISA trong Excel. Tách xong mà cần ghép với bảng danh mục thì xem bài Merge Queries thay VLOOKUP.
Khóa DATA FINANCE của EzTech đi từ Power Query chuyên sâu trong Excel đến báo cáo quản trị trên Power BI, dành cho người làm kế toán, tài chính. Có thể tự kiểm tra mức nắm Power Query hiện tại bằng bài trắc nghiệm Power Query miễn phí.
