Chuyển văn bản thành cột là tác vụ phổ biến trong công việc hàng ngày. Trong nhiều tình huống, chúng ta phải tách văn bản thành các cột, và Microsoft Excel vượt trội trong việc này. Bài viết này khám phá chi tiết cách chuyển văn bản thành cột với nhiều dấu phân tách trong Excel.
6 cách chuyển văn bản thành cột với nhiều dấu phân tách
Giả sử tập dữ liệu Danh sách Best Sellers trong ô B4:B13, chứa cột Tên sách, Tác giả, Thể loại. Mục tiêu là tách 3 thành phần này thành các cột riêng biệt. Chúng ta sẽ tìm hiểu từng phương pháp.
Bài viết sử dụng Microsoft Excel 365, bạn có thể dùng phiên bản khác.
Phương pháp 1: Dùng tính năng Text to Columns
Bắt đầu với tính năng Text to Columns của Excel - công cụ tiện lợi để tách văn bản với nhiều dấu phân tách.
📌 Các bước:
- Chọn ô B5:B13 → tab Data → Text to Columns.
- Cửa sổ Convert Text to Columns Wizard hiện ra. Chọn Delimited → Next.
- Chọn dấu phân tách Comma (Dấu phẩy) → Next.
- Nhập ô đích C5 → Finish.
- Nếu xuất hiện cảnh báo, nhấn OK.
Kết quả cuối cùng:
Xem thêm: Cách dùng Text to Columns với Carriage Return
Phương pháp 2: Kết hợp hàm TRIM, MID, SUBSTITUTE, REPT, LEN
Kết hợp 5 hàm để tách văn bản: LEN trả về độ dài chuỗi, REPT lặp lại văn bản, SUBSTITUTE thay thế văn bản, MID trích xuất giữa chuỗi, TRIM loại bỏ khoảng trắng thừa.
📌 Các bước:
- Ô C6, nhập công thức:
=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))
Trong đó B6 là cột dữ liệu gốc, C5 là số thứ tự cột kết quả (1, 2, 3...).
Giải thích công thức:
- LEN($B6): Độ dài chuỗi (ví dụ 43 ký tự).
- REPT(" ",LEN($B6)): Tạo 43 khoảng trắng.
- SUBSTITUTE(...): Thay dấu phẩy bằng 43 khoảng trắng.
- MID(...): Trích xuất đoạn chuỗi tương ứng với cột.
- TRIM(...): Loại bỏ khoảng trắng thừa.
- Kéo Fill Handle ngang và dọc để áp dụng cho toàn bộ dữ liệu.
Kết quả:
Xem thêm: Cách tự động tách văn bản thành cột bằng công thức
Phương pháp 3: Kết hợp LEFT, RIGHT, MID, LEN, FIND
Sử dụng bộ hàm này cho dữ liệu phân tách bằng dấu chấm phẩy (;) và @. Ví dụ tập dữ liệu Danh sách khách hàng ô B4:B12 chứa Tên, Quốc gia, Thành phố.
📌 Các bước:
- Ô C5 (Tên):
=LEFT(B5,FIND(";",B5)-1)
Giải thích: FIND tìm vị trí dấu ";", LEFT lấy ký tự bên trái.
- Ô D5 (Quốc gia):
=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)
Giải thích: Trích xuất chuỗi giữa ";" và "@".
- Ô E5 (Thành phố):
=RIGHT(B5,LEN(B5)-FIND("@",B5))
Giải thích: Lấy phần chuỗi bên phải sau "@".
Kết quả:
Phương pháp 4: Dùng Flash Fill (Điền nhanh)
Nếu không muốn dùng công thức phức tạp, Flash Fill là lựa chọn tuyệt vời.
📌 Các bước:
- Nhập thủ công giá trị đầu tiên (ví dụ "Trevor" ở ô C5) → tab Home → Fill → Flash Fill.
Excel tự động điền phần còn lại.
- Làm tương tự cho cột Quốc gia và Thành phố.
Phương pháp 5: Dùng Power Query
Power Query là tính năng mạnh mẽ nhưng thường bị bỏ qua. Chỉ vài click để tách cột.
📌 Các bước:
- Ô B4 → CTRL + T tạo bảng → OK.
- Tab Data → From Table/Range.
- Cửa sổ Power Query Editor mở ra. Chọn Split Column → By Delimiter.
- Chọn Semicolon → tick Each occurrence of the delimiter → OK.
- Đổi tên tiêu đề cột → Close & Load.
Kết quả:
Xem thêm: Cách dùng Line Break làm phân tách trong Text to Columns
Phương pháp 6: Dùng mã VBA
Nếu thường xuyên làm tác vụ này, mã VBA sẽ tự động hóa hoàn toàn.
📌 Các bước:
- Tab Developer → Visual Basic.
- Tab Insert → Module.
Sao chép mã sau vào module:
Sub Separate_Text_String()
Dim Arr() As String, _
cnt As Long, _
j As Variant
For k = 5 To 13
Arr = Split(Cells(k, 2), ";")
cnt = 3
For Each j In Arr
Cells(k, cnt) = j
cnt = cnt + 1
Next j
Next k
End Sub
⚡ Giải thích mã:
- Sub-routine tên Separate_Text_String().
- Khai báo biến Arr, cnt, j.
- Vòng lặp For k = 5 To 13: hàng 5 đến 13.
- Split(Cells(k, 2), ";"): Tách theo dấu ";". Có thể đổi thành dấu phẩy, pipe...
- cnt = 3: Bắt đầu ghi từ cột C (cột 3).
- Nhấn Run (F5) để thực thi.
Kết quả:
Phần thực hành
Bài viết cung cấp phần Practice để bạn tự thực hành. Hãy tải file và làm theo.
Kết luận
Bài viết trình bày 6 cách hiệu quả chuyển văn bản thành cột với nhiều dấu phân tách trong Excel. Hãy đọc kỹ, tải file thực hành miễn phí. Ghé thăm ExcelDemy để xem thêm nhiều bài viết tương tự.
Bài viết liên quan
- Cách dùng Text to Columns trong Excel cho ngày tháng
- [Đã khắc phục!] Excel Text to Columns bị xóa dữ liệu
- Cách chuyển Text to Columns mà không ghi đè