=TEXTBEFORE(A2," ") trả về mọi thứ trước dấu cách đầu tiên, nên Ana Silva cho Ana, còn =TEXTAFTER(A2," ") trả về mọi thứ sau dấu cách đó, Silva. Kéo cả hai công thức xuống và mọi họ tên đầy đủ được tách thành hai cột.
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Wu | Chen | Wu |
| 5 | Dara Kelly | Dara | Kelly |
| 6 | Eli Novak | Eli | Novak |
Đổi một cái tên ở cột A, hai cột kia thay đổi theo. TEXTBEFORE, TEXTAFTER và TEXTSPLIT cần Microsoft 365 hoặc Excel 2024. Với Excel 2021 trở về trước, cách dùng LEFT và FIND nằm ở phần bên dưới.
TEXTSPLIT: tách một ô thành nhiều cột
=TEXTSPLIT(A2," ") cắt văn bản tại mọi dấu cách và trả tràn mỗi phần vào một ô riêng. Một công thức lấp đầy số cột bằng đúng số phần:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | ||||
| 2 | Ana Maria Silva | Ana | Maria | Silva | |
| 3 | Paris, Lyon, Nice | Paris | Lyon | Nice |
- Dấu phân cách có thể dài hơn một ký tự:
", "tách tại dấu phẩy theo sau là dấu cách, nên các phần không có dấu cách ở đầu. - Hãy chừa chỗ cho kết quả. Nếu một ô bên phải không trống, công thức sẽ hiện #SPILL! thay vì kết quả.
- Các phần luôn là văn bản, kể cả chữ số, nên SUM bỏ qua chúng. Đặt hai dấu trừ phía trước,
=--TEXTSPLIT(A2,","), khi bạn cần số.
Đối số thứ ba là dấu phân cách hàng. Khi có cả hai, TEXTSPLIT dựng một bảng nhỏ từ một ô: dấu phẩy ngăn cách các cột còn dấu chấm phẩy ngăn cách các hàng.
| A | B | |
|---|---|---|
| 1 | Teams | |
| 2 | Ana,Red;Ben,Blue;Chen,Green | |
| 3 | ||
| 4 | Ana | Red |
| 5 | Ben | Blue |
| 6 | Chen | Green |
Hai dạng khác chạy được trong Excel, hiển thị ở đây dưới dạng văn bản:
=TEXTSPLIT(A2,,", ") one piece per row: the column delimiter is left out
=TEXTSPLIT(A2,{";",","}) several delimiters in braces: splits at ; or ,
Dạng thứ nhất biến excel, formulas, text thành một cột ba ô, đây là cách chuyển một danh sách gõ trong một ô thành nhiều hàng. Dạng thứ hai biến red;green,blue thành red, green, blue.
Tên đệm và dạng "Họ, Tên"
TEXTAFTER(A2," ") trả về mọi thứ sau dấu cách đầu tiên, nên với Ana Maria Silva nó trả về Maria Silva. Để lấy từ cuối cùng, hãy đếm từ cuối lên với số thứ tự lần xuất hiện là -1. Với tên viết dạng Silva, Ana, hãy tách tại dấu phẩy và dấu cách:
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Maria Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Li Wu | Chen | Wu |
| 5 | Cher | Cher | |
| 6 | |||
| 7 | Last, First | First | Last |
| 8 | Silva, Ana | Ana | Silva |
Khi không có dấu phân cách, TEXTBEFORE và TEXTAFTER trả về #N/A, và một cái tên chỉ có một từ như Cher thì không có dấu cách. Hàng 5 dùng đối số thứ sáu, giá trị trả về thay thế: =TEXTBEFORE(A5," ",,,,A5) trả về cả cái tên, còn TEXTAFTER(...,"") trả về một ô trống.
| A | B | C | |
|---|---|---|---|
| 1 | User | Domain | |
| 2 | ana.silva@coddy.tech | ana.silva |
Đến lượt bạn: Ở C2, trả về tên miền của địa chỉ email ở A2: mọi thứ sau dấu @.
Tách họ tên trong Excel cũ
Không có TEXTBEFORE và TEXTAFTER, hãy tìm vị trí của dấu cách bằng FIND rồi cắt quanh nó bằng LEFT, MID và RIGHT:
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Wu | Chen | Wu |
FIND(" ",A2)là vị trí của dấu cách: 4 trongAna Silva.LEFT(A2,4-1)lấy 3 ký tự đứng trước nó.MID(A2,4+1,100)lấy tối đa 100 ký tự bắt đầu sau dấu cách. 100 chỉ có nghĩa là "dài hơn mọi cái tên";=RIGHT(A2,LEN(A2)-FIND(" ",A2))cho cùng kết quả với độ dài chính xác.
Các công thức này trả về #VALUE! khi không có dấu cách. Hãy bọc chúng trong IFERROR, =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2), để trả về cả ô thay vì lỗi.
| A | B | |
|---|---|---|
| 1 | Code | Prefix |
| 2 | KT-1043-BLUE |
Đến lượt bạn: Ở B2, trả về phần mã sản phẩm đứng trước dấu gạch ngang đầu tiên, dùng LEFT và FIND.
Text to Columns và Flash Fill
Để tách một cột một lần mà không cần công thức:
- Chọn cột chứa họ tên đầy đủ. Nhớ để trống các cột bên phải, vì Text to Columns sẽ ghi đè lên chúng.
- Data > Text to Columns.
- Chọn Delimited, bấm Next, đánh dấu Space (hoặc Comma, hoặc gõ ký tự khác vào Other), bấm Next.
- Phần đầu tiên sẽ thay thế họ tên đầy đủ. Để giữ lại họ tên, đặt Destination là ô trống bên phải chúng, rồi bấm Finish.
Flash Fill nhanh hơn khi có một quy luật: gõ Ana cạnh Ana Silva, nhấn Enter, rồi nhấn Ctrl+E hoặc chọn Data > Flash Fill, Excel sẽ điền tên đầu cho các hàng còn lại. Cả hai công cụ đều ghi giá trị cố định. Nếu sau này các cái tên thay đổi, kết quả tách không đổi theo; công thức thì có.
Câu hỏi thường gặp
Làm sao để tách họ và tên trong Excel?
Với một tên như Ana Silva ở A2, =TEXTBEFORE(A2," ") cho Ana và =TEXTAFTER(A2," ",-1) cho Silva, kể cả khi có tên đệm. Trong Excel 2021 trở về trước, dùng =LEFT(A2,FIND(" ",A2)-1) để lấy tên đầu.
Những phiên bản Excel nào có TEXTSPLIT?
TEXTSPLIT, TEXTBEFORE và TEXTAFTER có trong Microsoft 365, Excel cho web và Excel 2024. Excel 2021 trở về trước hiện #NAME?; ở đó hãy dùng LEFT, MID, RIGHT và FIND, hoặc Data > Text to Columns.
Làm sao để tách một ô theo dấu phẩy trong Excel?
=TEXTSPLIT(A2,", ") trả tràn mỗi phần vào một cột riêng. Muốn đặt các phần theo hàng, hãy để trống dấu phân cách cột: =TEXTSPLIT(A2,,", ").
Vì sao TEXTBEFORE trả về #N/A?
Dấu phân cách không có trong ô, ví dụ một cái tên chỉ có một từ, không có dấu cách. Hãy đưa giá trị cho trường hợp đó vào đối số thứ sáu: =TEXTBEFORE(A2," ",,,,A2) trả về cả ô khi không có dấu cách.
