Làm việc với nhiều loại văn bản rất phổ biến trong Excel. Nhưng đôi khi cần tách chúng để hiểu rõ hơn. Bài viết tìm hiểu tách văn bản trong Excel với 8 ứng dụng.
Tải file mẫu và tự thử.
8 Ứng dụng Hữu ích Tách Văn bản trong Excel
Để minh họa, đây là tập dữ liệu thông tin Tên & Địa chỉ (Name & Address) của 5 người ở các quốc gia khác nhau.
Hãy làm theo các ví dụ dưới để tách văn bản trong tập dữ liệu này.
1. Dùng Trình dẫn Cột (Column Wizard) tách văn bản
Phương pháp đầu hướng dẫn tách văn bản nhờ Trình dẫn Văn bản thành cột (Text to Column Wizard) xác định Dấu phân cách (Delimiter). Quy trình:
- Vào tab Dữ liệu (Data), chọn Văn bản thành cột (Text to Columns) trong nhóm Công cụ dữ liệu (Data Tools).
- Chọn tùy chọn Có dấu phân cách (Delimited) ở bước 1 của Trình dẫn Văn bản thành cột, nhấn Tiếp theo (Next).
- Bước 2, chọn Dấu phân cách là Dấu phẩy (Comma), nhấn Tiếp theo.
- Bước cuối, cung cấp Đích (Destination) là ô C5, nhấn Hoàn tất (Finish).
- Xong, văn bản đã tách như hình:
2. Tách văn bản với Flash Fill
Flash Fill bổ sung tốt cho Trình dẫn Cột tách văn bản. Quy trình:
- Nhập phần đầu của văn bản vào ô C5.
- Vào tab Dữ liệu, chọn Flash Fill trong nhóm Công cụ dữ liệu.
- Phạm vi C6:C9 tự động điền phần đầu từ mỗi nguồn.
- Làm tương tự cho các phần còn lại, kết quả cuối:
3. Kết hợp LEFT, RIGHT, FIND & LEN tách văn bản
Phương pháp hữu ích khác: kết hợp LEFT, RIGHT, FIND & LEN. Cách hoạt động:
- Nhập công thức vào ô C5:
=LEFT(B5,FIND(",",B5)-1)
- Nhấn Enter.
- Dùng Tự động điền (AutoFill) cho kết quả:
Ở đây, LEFT lấy dữ liệu từ bên trái ô. FIND tìm vị trí văn bản trong ô B5.
- Nhập công thức vào ô D5:
=RIGHT(B5,LEN(B5)-FIND(",",B5))
- Nhấn Enter.
- Dùng AutoFill cho phạm vi D5:D9.
RIGHT trích xuất phần phải ô B5. LEN xác định độ dài chuỗi. FIND tìm chuỗi cụ thể.
4. Chèn hàm TRANSPOSE tách văn bản
Hàm TRANSPOSE cũng rất hữu ích tách văn bản. Quy trình:
- Nhập công thức vào ô C5:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","") & "","//s"))
- Nhấn Enter.
- Văn bản tự động chia thành ô C5, D5, E5.
TRANSPOSE tạo mảng tách văn bản. FILTERXML trích xuất mục riêng lẻ từ ô B5. SUBSTITUTE thay thế văn bản dựa trên dữ liệu.
- Kéo Thanh tràn (Fill Handle) cho các ô tiếp theo.
Mẹo thêm: cũng dùng công thức này: =FILTERXML(""&SUBSTITUTE(B3,",","")&"", "//s")
5. Áp dụng VBA Excel tách văn bản
VBA Excel giải pháp xuất sắc tách văn bản. Các bước đơn giản:
- Vào tab Nhà phát triển (Developer), chọn Visual Basic từ nhóm Mã (Code).
- Chọn Module từ Chèn (Insert).
- Chèn mã sau vào trang trống:
Sub SplitCellText() Dim MyAry() As String, Cnt As Long, j As Variant For n = 5 To 9 MyAry = Split(Cells(n, 2), ",") Cnt = 3 For Each j In MyAry Cells(n, Cnt) = j Cnt = Cnt + 1 Next j Next n End Sub
- Nhấn Chạy Sub (Run Sub) hoặc F5.
- Nhấn Chạy (Run) trong cửa sổ Macros.
- Hoàn thành tách văn bản trong Excel.
6. Dùng Power Query tách văn bản
Phương pháp hữu ích khác: dùng Power Query trong Excel. Công cụ giúp nhập và kết nối sổ làm việc bên ngoài, định hình lại theo ý muốn. Làm theo cẩn thận:
- Vào tab Dữ liệu (Data), chọn Lấy dữ liệu (Get Data).
- Chọn Từ tệp (From File) rồi Từ Sổ làm việc Excel (From Excel Workbook) trong menu ngữ cảnh.
- Chọn trang tính Power Query trong danh sách Trình điều hướng (Navigator).
- Nhấn Chuyển đổi dữ liệu (Transform Data).
- Trong cửa sổ, vào thanh Chuyển đổi (Transform), nhấn Tách cột (Split Column).
- Chọn Theo dấu phân cách (By Delimiter) từ menu thả xuống.
- Được dẫn đến hộp thoại Tách cột theo dấu phân cách (Split Column by Delimiter).
- Chọn dấu phân cách: Dấu phẩy (Comma).
- Chọn tùy chọn Mỗi lần xuất hiện của dấu phân cách (Each occurrence of the delimiter) để Tách tại (Split at).
- Nhấn OK.
- Cuối cùng, nhấn Đóng & Tải (Close & Load).
- Trang tính mới với văn bản đã tách hiện ra.
- Bạn có thể sửa bảng theo ý muốn sau đó.
7. Tách văn bản với TEXTBEFORE & TEXTAFTER
Phương pháp cuối dùng TEXTBEFORE và TEXTAFTER trên tập dữ liệu mới. Thông tin họ tên 5 người ở phạm vi B5:B9. Các bước:
- Nhập công thức vào ô C5 và nhấn Enter.
=TEXTBEFORE(B5," ")
TEXTBEFORE trả về văn bản trong B5 trước dấu phân cách.
- Áp dụng công thức vào ô D5, nhấn Enter.
=IFERROR(TEXTBEFORE(TEXTAFTER(B5," ")," "),-1),"")
IFERROR trả về giá trị nếu công thức lỗi. TEXTBEFORE và TEXTAFTER trả về văn bản trước/sau dấu phân cách.
- Áp dụng công thức vào ô E5.
=TEXTAFTER(B5," "),-1)
TEXTAFTER trả về văn bản sau dấu phân cách.
- Kết quả cho ô B5:
- Kéo Thanh tràn (Fill Handle) lấy kết quả cuối.
8. Tách văn bản từ ngắt dòng (Line Break)
Phần này nói về khía cạnh mới: tách văn bản từ ngắt dòng. Quy trình:
- Nhập công thức vào ô C5:
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)
LEFT trích xuất giá trị bên trái. SEARCH trả về vị trí số trong chuỗi. CHAR trả về ký tự theo số trong ô B5.
- Áp dụng công thức vào ô D5:
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)
MID lấy giá trị dòng giữa trong ô B5. SEARCH trả về vị trí. CHAR trả về ký tự theo số.
- Nhập công thức vào ô E5:
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10),B5) + 1))
RIGHT trích xuất giá trị bên phải. SEARCH trả về vị trí, LEN xác định độ dài chuỗi. CHAR trả về ký tự theo số trong ô B5.
- Kết quả cho ô B5:
- Lặp lại thủ tục, bộ văn bản tách hoàn chỉnh:
Cách Tách Số khỏi Văn bản trong Excel
Hình sau显示工作表有数字和文本在同一列。目标是从每个单元格提取数字部分，放入单独列，使用 Flash Fill.
- Nhập số của ô B5 vào ô C5.
- Chọn ô ngay sau đó.
- Vào tab Dữ liệu, chọn Flash Fill.
- Xong, tất cả số xuất hiện cùng lúc.
Kết luận
Đã thảo luận tách văn bản trong Excel với 8 ứng dụng thực tế. Hãy thử và cho phản hồi. Theo dõi ExcelDemy để đọc thêm blog Excel.