Có thể bạn quan tâm: Excel Skills For Business Essentials: Nâng Cao Năng Lực Phân Tích Và Quản Lý Dữ Liệu Trong Doanh Nghiệp
Mở Đầu
Trong môi trường làm việc với dữ liệu, việc xử lý và tách chuỗi thành các phần riêng biệt là một nhiệm vụ thường xuyên gặp phải, đặc biệt khi làm việc với Microsoft Excel. Dù bạn là người mới bắt đầu hay đã có kinh nghiệm lâu năm, việc nắm vững các phương pháp tách chuỗi (split string) dựa trên ký tự đặc trưng (delimiter) sẽ giúp bạn tiết kiệm thời gian, giảm thiểu sai sót và nâng cao hiệu quả công việc.
Bài viết dưới đây sẽ cung cấp hướng dẫn chi tiết, đầy đủ và thực tế về cách tách chuỗi trong Excel bằng ký tự đặc trưng, bao gồm:
- Giới thiệu tổng quan về khái niệm tách chuỗi và lý do tại sao lại cần thiết.
- Các công cụ và hàm tích hợp sẵn trong Excel:
TEXTSPLIT,TEXTTOCOLUMNS,LEFT,RIGHT,MID,FIND,SEARCH,LEN,FILTERXML,TRIM,SUBSTITUTE,REPLACE,SPLIT(trong Power Query) và các hàm mới trong Office 365/Excel 2026. - Sử dụng Power Query để tách chuỗi một cách linh hoạt và mạnh mẽ.
- Kỹ thuật tách chuỗi bằng VBA cho những trường hợp phức tạp, tự động hoá.
- Các ví dụ thực tế trong các lĩnh vực: quản lý khách hàng, tài chính, marketing, logistics.
- Mẹo tối ưu hoá, xử lý lỗi thường gặp và cách kiểm tra kết quả.
- So sánh giữa các phương pháp để bạn chọn giải pháp phù hợp nhất.
Sau khi đọc xong bài viết, bạn sẽ có bộ công cụ hoàn chỉnh để giải quyết mọi nhu cầu tách chuỗi trong Excel, dù dữ liệu của bạn có phức tạp đến đâu.
Có thể bạn quan tâm: Excel Hiển Thị Công Thức Thay Vì Kết Quả: Nguyên Nhân, Giải Pháp Và Cách Khắc Phục
1. Tại Sao Việc Tách Chuỗi Lại Quan Trọng?
1.1. Định Nghĩa và Vai Trò
- Chuỗi (String): Dữ liệu dạng văn bản, có thể chứa các ký tự, số, dấu câu, khoảng trắng…
- Ký tự đặc trưng (Delimiter): Ký tự hoặc chuỗi ký tự dùng để phân tách các phần trong chuỗi (ví dụ: dấu phẩy
,, dấu chấm., dấu gạch ngang-, dấu cách, ký tự|, v.v.).
Khi dữ liệu được nhập dưới dạng chuỗi tổng hợp (ví dụ: “Họ,Tên,Ngày sinh”), việc tách ra các trường riêng biệt cho phép:
- Phân tích dữ liệu: Nhận diện từng trường để lọc, sắp xếp, tính toán.
- Chuẩn hoá dữ liệu: Đảm bảo tính nhất quán, chuẩn hoá định dạng.
- Tự động hoá quy trình: Dễ dàng áp dụng công thức, pivot table, biểu đồ.
- Tích hợp hệ thống: Dữ liệu được chuẩn bị để nhập vào các hệ thống ERP, CRM, BI.
1.2. Các Trường Hợp Thực Tế
| Tình huống | Dữ liệu gốc | Ký tự tách | Mục tiêu |
|---|---|---|---|
| Danh sách email | john.doe@example.com; jane.smith@company.com | ; | Tách từng email thành cột riêng |
| Địa chỉ | 123 Main St, Springfield, IL 62704 | , | Tách thành “Số nhà & Đường”, “Thành phố”, “Bang & Mã ZIP” |
| Mã sản phẩm | PROD-2026-001 | - | Tách “PROD”, “2026”, “001” |
| Ngày tháng | 2026/04/15 | / | Tách năm, tháng, ngày |
| Thông tin nhân viên | Nguyen Van A|IT|Manager|5000 | | | Tách họ tên, phòng ban, chức vụ, lương |
Có thể bạn quan tâm: Giới Thiệu Chi Tiết Về Độ Dài Tối Đa Của Tên Sheet Trong Microsoft Excel
2. Các Phương Pháp Tách Chuỗi Trong Excel
2.1. Sử Dụng Tính Năng “Text to Columns” (Văn Bản Thành Cột)
Cách thực hiện:
- Chọn cột chứa chuỗi cần tách.
- Vào Data → Text to Columns.
- Chọn Delimited → Next.
- Đánh dấu vào Other và nhập ký tự tách (ví dụ:
|). - Finish.
Ưu điểm:
- Nhanh, không cần công thức.
- Thích hợp cho một lần tách dữ liệu.
Nhược điểm:
- Không tự động cập nhật khi dữ liệu thay đổi.
- Không linh hoạt với dữ liệu đa dạng (có thể gây lỗi nếu có ký tự tách trong nội dung).
2.2. Hàm TEXTSPLIT (Excel 365/2026)
Cú pháp:
=TEXTSPLIT(text, col_delimiter, row_delimiter, ignore_empty)
text: Chuỗi cần tách.col_delimiter: Ký tự tách cột (có thể là mảng).row_delimiter(tùy chọn): Ký tự tách hàng.ignore_empty(tùy chọn): Bỏ qua ô trống hay không.
Ví dụ 1: Tách email bằng dấu chấm phẩy.
=TEXTSPLIT(A2, ";")
Kết quả sẽ trả về một mảng ngang (các email nằm trong các cột liền kề).
Ví dụ 2: Tách địa chỉ thành ba phần (đường, thành phố, bang).
=TEXTSPLIT(A3, ",")
Nếu muốn tách thành hàng thay vì cột, chỉ cần thêm row_delimiter:
=TEXTSPLIT(A3, ",", CHAR(10))
Lưu ý: Hàm này chỉ có trong Office 365 và Excel 2026 trở lên. Nếu bạn dùng phiên bản cũ, hãy xem các cách khác.
2.3. Kết Hợp LEFT, RIGHT, MID, FIND/SEARCH, LEN
Đây là cách cổ điển nhưng cực kỳ hiệu quả khi bạn cần tách các phần cố định hoặc các ký tự xuất hiện duy nhất.
2.3.1. Tách phần trước ký tự đầu tiên
=LEFT(A2, FIND("-", A2)-1)
FIND("-", A2)trả về vị trí của dấu-đầu tiên.LEFTlấy các ký tự bên trái vị trí đó.
2.3.2. Tách phần sau ký tự cuối cùng
=RIGHT(A2, LEN(A2) - FIND("@", A2, FIND("@", A2)+1))
Trong ví dụ trên, chúng ta tìm ký tự @ thứ hai và lấy phần sau nó.
2.3.3. Tách phần giữa hai ký tự
=MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1) - FIND("-", A2) - 1)
MIDbắt đầu từ vị trí sau dấu-đầu tiên.- Độ dài tính bằng khoảng cách giữa hai dấu
-.
2.3.4. Khi có nhiều ký tự tách (dấu phẩy, dấu chấm, khoảng trắng)
Đối với trường hợp có nhiều delimiter (ví dụ: “John, Doe; 30”), bạn có thể thay thế các ký tự bằng một ký tự chung rồi dùng TEXTSPLIT.
=TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2, ",", "|"), ";", "|"), "|")
Hoặc dùng TRIM để loại bỏ khoảng trắng thừa:
=TRIM(TEXTSPLIT(SUBSTITUTE(A2, ",", "|"), "|"))
2.4. FILTERXML Kết Hợp Với SUBSTITUTE
Nếu bạn có Excel 2026 trở lên, bạn có thể dùng hàm FILTERXML để tách chuỗi thành mảng. Cách này dựa trên việc chuyển chuỗi thành một đoạn XML rồi dùng XPath để trích xuất.
Bước 1: Thêm một ký tự đặc trưng (ví dụ |) và bao bọc chuỗi bằng thẻ <t>.
= "<t>" & SUBSTITUTE(A2, "|", "</t><t>") & "</t>"
Bước 2: Dùng FILTERXML để lấy các phần tử.
=FILTERXML("<t>" & SUBSTITUTE(A2, "|", "</t><t>") & "</t>", "//t")
Kết quả sẽ trả về một mảng (các phần tử nằm trong các ô liền kề). Phương pháp này rất mạnh khi bạn cần tách hàng và cột cùng lúc.
2.5. Power Query (Get & Transform)
Power Query là công cụ ETL (Extract, Transform, Load) tích hợp trong Excel, cho phép xử lý dữ liệu phức tạp mà không cần viết công thức.
2.5.1. Bước Cơ Bản
- Select dữ liệu → Data → From Table/Range.
- Trong Power Query Editor, chọn cột chứa chuỗi.
- Vào Home → Split Column → By Delimiter.
- Chọn Delimiter (có sẵn hoặc tùy chỉnh) và Split At:
Each occurrence of the delimiter. - Nhấn OK, sau đó Close & Load.
2.5.2. Tách Nhiều Delimiter
Nếu chuỗi có nhiều ký tự tách khác nhau, bạn có thể:
- Replace Values: Thay thế các ký tự thành một ký tự chung, rồi tách.
- Hoặc Add Custom Column với công thức M:
= Table.TransformColumns(Source, {{"Column1", each Text.SplitAny(_, {"|", ",", ";", " "}), type list}})
Sau khi có danh sách (list), bạn mở rộng (Expand) để đưa các phần tử vào các cột.
2.5.3. Lợi Ích Khi Dùng Power Query
- Tự động cập nhật khi dữ liệu nguồn thay đổi (Refresh).
- Xử lý khối lượng lớn (hàng triệu bản ghi) nhanh hơn so với công thức.
- Ghi lại lịch sử các bước chuyển đổi (đối tượng “Applied Steps”).
2.6. VBA – Tự Động Hóa Tách Chuỗi
Khi bạn cần tách hàng loạt trong một workbook, hoặc muốn xây dựng macro tùy chỉnh, VBA là giải pháp mạnh mẽ.
2.6.1. Hàm Split trong VBA
Function SplitString(ByVal txt As String, ByVal delim As String) As Variant SplitString = Split(txt, delim)
End Function
Bạn có thể gọi hàm này trong worksheet:
=INDEX(SplitString(A2, "|"), 1) ' Lấy phần tử thứ 1
=INDEX(SplitString(A2, "|"), 2) ' Lấy phần tử thứ 2
2.6.2. Macro Tự Động Tách và Điền Dữ Liệu
Sub SplitColumn() Dim rng As Range, cell As Range Dim arr As Variant Dim i As Long, colStart As Long Set rng = Selection ' Chọn cột cần tách colStart = rng.Column + 1 ' Bắt đầu điền vào cột kế tiếp For Each cell In rng If Len(cell.Value) > 0 Then arr = Split(cell.Value, "|") ' Thay "|" bằng delimiter của bạn For i = LBound(arr) To UBound(arr) cell.Offset(0, colStart - cell.Column + i).Value = Trim(arr(i)) Next i End If Next cell
End Sub
- Cách dùng: Chọn cột chứa chuỗi, chạy macro → các phần sẽ được điền vào các cột phía bên phải.
- Ưu điểm: Tự động, có thể mở rộng, áp dụng cho hàng nghìn bản ghi mà không làm chậm Excel.
2.7. So Sánh Các Phương Pháp
Có thể bạn quan tâm: Cách Sắp Xếp Dữ Liệu Trong Excel Theo 2 Cột: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
| Phương pháp | Độ phức tạp | Tự động cập nhật | Hỗ trợ đa delimiter | Phù hợp với dữ liệu lớn | Yêu cầu phiên bản |
|---|---|---|---|---|---|
| Text to Columns | Thấp | Không | Không | Không | Tất cả |
| TEXTSPLIT | Trung bình | Có (dạng công thức) | Có (mảng) | Trung bình | Office 365/2026 |
| LEFT/MID/FIND | Trung bình | Có | Không (cần lồng SUBSTITUTE) | Không | Tất cả |
| FILTERXML | Trung bình | Có | Có (qua SUBSTITUTE) | Trung bình | 2026+ |
| Power Query | Cao | Có | Có | Rất tốt | 2026+ (có Add‑in) |
| VBA | Cao | Có (macro) | Có | Rất tốt | Tất cả (có macro) |
3. Các Bài Tập Thực Tế – Áp Dụng Vào Tình Huống Cụ Thể
3.1. Tách Email Được Ngăn Cách Bởi Dấu Chấm Phẩy
Dữ liệu mẫu (cột A):
| A |
|---|
| john.doe@example.com; jane.smith@company.com; bob@domain.org |
Cách 1 – TEXTSPLIT (Office 365):
=TEXTSPLIT(A2, ";")
Kết quả: Các email sẽ nằm trong các cột B, C, D…
Cách 2 – Power Query:
- Chọn bảng → Data → From Table/Range.
- Chọn cột A → Split Column → By Delimiter → ;.
- Chọn Each occurrence và OK.
- Close & Load.
Cách 3 – VBA (đối với hàng nghìn bản ghi):
Sub SplitEmails() Dim rng As Range, cell As Range, arr As Variant, i As Long Set rng = Range("A2:A1000") For Each cell In rng If Len(cell.Value) > 0 Then arr = Split(cell.Value, ";") For i = LBound(arr) To UBound(arr) cell.Offset(0, i + 1).Value = Trim(arr(i)) Next i End If Next cell
End Sub
3.2. Tách Địa Chỉ Thành Ba Phần
Dữ liệu mẫu (cột B): 123 Main St, Springfield, IL 62704
Mục tiêu:
– Cột C: 123 Main St
– Cột D: Springfield
– Cột E: IL 62704
Sử dụng TEXTSPLIT:
=TEXTSPLIT(B2, ",")
Sau khi tách, cột C và D sẽ có giá trị mong muốn, còn cột E cần tách thêm để tách “IL” và “62704”:
=TEXTSPLIT(TRIM(INDEX(TEXTSPLIT(B2, ","), 3)), " ")
Sử dụng hàm lớp cũ (LEFT, MID, FIND):
' Phần 1: Đường
=TRIM(LEFT(B2, FIND(",", B2)-1)) ' Phần 2: Thành phố
=TRIM(MID(B2, FIND(",", B2)+1, FIND(",", B2, FIND(",", B2)+1) - FIND(",", B2) - 1)) ' Phần 3: Bang + ZIP
=TRIM(RIGHT(B2, LEN(B2) - FIND(",", B2, FIND(",", B2)+1)))
3.3. Tách Mã Sản Phẩm “PROD-2026-001”
Dữ liệu (cột C): PROD-2026-001
Công thức TEXTSPLIT:
=TEXTSPLIT(C2, "-")
Kết quả:
– Cột D: PROD
– Cột E: 2026
– Cột F: 001
Nếu muốn chuyển 001 thành số nguyên, dùng VALUE:
=VALUE(INDEX(TEXTSPLIT(C2, "-"), 3))
3.4. Tách Ngày (YYYY/MM/DD) Thành Năm, Tháng, Ngày
Dữ liệu (cột D): 2026/04/15
Công thức:
=TEXTSPLIT(D2, "/")
Hoặc nếu muốn định dạng ngày chuẩn:
=DATE(INDEX(TEXTSPLIT(D2, "/"),1), INDEX(TEXTSPLIT(D2, "/"),2), INDEX(TEXTSPLIT(D2, "/"),3))
Sau đó định dạng ô thành dd/mm/yyyy hoặc yyyy-mm-dd.
3.5. Tách Thông Tin Nhân Viên “Nguyen Van A|IT|Manager|5000”
Dữ liệu (cột E): Nguyen Van A|IT|Manager|5000
Sử dụng Power Query:
- Data → From Table/Range.
- Split Column → By Delimiter → |.
- Close & Load.
Sử dụng TEXTSPLIT:
=TEXTSPLIT(E2, "|")
Sử dụng VBA (để tạo cột lương dạng số):
Sub SplitEmployee() Dim rng As Range, cell As Range, arr As Variant Set rng = Range("E2:E500") For Each cell In rng arr = Split(cell.Value, "|") cell.Offset(0, 1).Value = arr(0) ' Họ tên cell.Offset(0, 2).Value = arr(1) ' Phòng ban cell.Offset(0, 3).Value = arr(2) ' Chức vụ cell.Offset(0, 4).Value = CLng(arr(3)) ' Lương Next cell
End Sub
4. Xử Lý Các Trường Hợp Đặc Biệt
4.1. Khi Delimiter Xuất Hiện Hai Lần Liên Tiếp (,,)
- Vấn đề:
TEXTSPLITmặc định sẽ trả về ô trống cho phần giữa. - Giải pháp: Sử dụng tham số
ignore_empty:
=TEXTSPLIT(A2, ",", , TRUE) ' Bỏ qua các ô trống
Hoặc dùng FILTER để lọc:
=FILTER(TEXTSPLIT(A2, ","), TEXTSPLIT(A2, ",")<>"")
4.2. Khi Dữ Liệu Có Dấu Nháy (“) hoặc Dấu Gạch Ngang ()
Dấu nháy thường gây lỗi khi dùng Text to Columns. Cách khắc phục:
- Bước 1: Thay dấu nháy bằng ký tự khác tạm thời:
=SUBSTITUTE(A2, """", "~")
- Bước 2: Thực hiện tách.
- Bước 3: Đổi lại ký tự gốc:
=SUBSTITUTE(Kết quả, "~", """")
4.3. Khi Delimiter Không Nhất Quán Trong Cột
Ví dụ: một số ô dùng ,, một số dùng ;, một số dùng |.
Cách 1 – SUBSTITUTE đa ký tự:
=TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ",", "|"), ";", "|"), "/", "|"), "|")
Cách 2 – Power Query:
- Transform > Replace Values: Thay tất cả các delimiter bằng một ký tự chung (
|), sau đó Split Column.
4.4. Khi Cần Tách Nhiều Lần (Nested Split)
Ví dụ: chuỗi "John|Doe,30|Male" muốn tách thành Tên, Họ, Tuổi, Giới tính.
Bước 1: Tách bằng |:
=TEXTSPLIT(A2, "|")
Giả sử kết quả ở cột B và C:
- B2 =
John - C2 =
Doe,30 - D2 =
Male
Bước 2: Tách C2 bằng ,:
=TEXTSPLIT(C2, ",")
Kết quả:
– C2 → Doe
– D2 → 30
Bạn có thể lồng công thức:
=LET(arr, TEXTSPLIT(A2, "|"), name, INDEX(arr,1), surnameAge, TEXTSPLIT(INDEX(arr,2), ","), surname, INDEX(surnameAge,1), age, INDEX(surnameAge,2), gender, INDEX(arr,3), CHOOSE({1,2,3,4}, name, surname, age, gender))
Kết quả trả về một mảng 4 cột: John | Doe | 30 | Male.
5. Kiểm Tra và Xác Thực Kết Quả
5.1. Sử Dụng ISNUMBER, ISERROR, ISBLANK
- Kiểm tra ô trống:
=IF(ISBLANK(B2), "Missing", "OK") - Kiểm tra giá trị số:
=IF(ISNUMBER(C2), "Number", "Not Number") - Kiểm tra lỗi:
=IF(ISERROR(D2), "Error", "Fine")
5.2. Đánh Dấu Bằng Conditional Formatting
- Mục tiêu: Đánh dấu các ô chứa dấu tách còn tồn tại (có nghĩa là tách chưa thành công).
- Công thức:
=ISNUMBER(SEARCH("|", A2))→ Đánh màu đỏ nếu còn|.
5.3. Tổng Hợp Kiểm Tra Với COUNTIF
=COUNTIF(B2:D2, "")
Nếu số ô trống > 0 → có thể có delimiter thừa, hoặc dữ liệu không đủ.
6. Tối Ưu Hoá Hiệu Suất
- Tránh lặp lại tính toán: Sử dụng
LET(Excel 365) để lưu trữ giá trị trung gian. - Giảm phạm vi tính toán: Đừng áp dụng công thức cho toàn bộ cột (A:A) nếu không cần; chỉ áp dụng cho vùng thực tế.
- Sử dụng bảng (Table): Khi dữ liệu thay đổi, công thức tự mở rộng.
- Vận dụng Power Query: Đối với dữ liệu > 50,000 dòng, công thức mảng có thể chậm; Power Query xử lý nhanh hơn.
- Vô hiệu hoá tính năng tự động tính toán (Manual Calculation) khi thực hiện macro lớn, rồi bật lại khi hoàn thành.
7. Tổng Kết – Lựa Chọn Phương Pháp Phù Hợp
| Nhu cầu | Phương pháp đề xuất |
|---|---|
| Tách nhanh 1‑2 cột, dữ liệu ít | Text to Columns hoặc TEXTSPLIT |
| Công thức cần tự động cập nhật | TEXTSPLIT, LEFT/MID/FIND, FILTERXML |
| Dữ liệu đa dạng, nhiều delimiter | Power Query hoặc VBA |
| Xử lý hàng triệu bản ghi | Power Query hoặc VBA (đọc/ghi file CSV) |
| Yêu cầu macro, tự động hoá | VBA (Split + Loop) |
| Phiên bản Excel cũ (2026‑2026) | Text to Columns + hàm cổ điển (LEFT, MID, FIND) hoặc FILTERXML |
Cuối cùng, hiểu rõ dữ liệu và đặt mục tiêu là chìa khóa để chọn công cụ tối ưu. Hãy thử nghiệm trên một mẫu nhỏ, kiểm tra tính chính xác, sau đó mở rộng sang toàn bộ dataset.
8. Tài Nguyên Tham Khảo Thêm
| Tên tài liệu | Link |
|---|---|
| Microsoft Docs – TEXTSPLIT | https://learn.microsoft.com/en-us/office365/Excel/FORMULAS/textsplit-function |
| Power Query – Split Column | https://learn.microsoft.com/en-us/power-query/split-column |
| VBA Split Function | https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function |
| FILTERXML – Extract Data from XML | https://support.microsoft.com/en-us/office/filterxml-function |
| ExcelJet – LEFT, MID, RIGHT | https://exceljet.net/ |
| MyOnlineTrainingHub – Advanced Text Functions | https://www.myonlinetraininghub.com/excel-text-functions |
Kết luận: Việc tách chuỗi trong Excel không còn là một thách thức khi bạn đã nắm vững các công cụ và kỹ thuật ở trên. Hãy lựa chọn phương pháp phù hợp với môi trường làm việc, phiên bản Excel và khối lượng dữ liệu của bạn. Khi đã thành thạo, bạn sẽ tiết kiệm được hàng giờ công sức và đưa ra các quyết định dựa trên dữ liệu chính xác, nhanh chóng. Chúc bạn thành công!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.