Thành Thạo Excel: 8 Công Thức Chuyên Gia Để Tách Ô Theo Dấu Phân Cách

Giới Thiệu

Hướng dẫn này sẽ chỉ cho bạn cách tách một ô theo dấu phân cách (delimiter) bằng công thức trong Excel. Dấu phân cách là ký tự tách các đoạn dữ liệu trong chuỗi văn bản. Trong bài viết này, chúng tôi sẽ trình bày nhiều cách khác nhau để tách ô theo dấu phân cách sử dụng các công thức trong Excel.

File Thực Hành

Bạn có thể tải file thực hành từ liên kết bên dưới để làm theo các ví dụ.

Dữ Liệu Mẫu

Cơ sở của ví dụ là dữ liệu liên quan đến sinh viên (Tên, Mã SV, Khóa Học, Thành Phố). Sử dụng dữ liệu này, chúng tôi sẽ trình bày các phương pháp hoạt động trong các hoàn cảnh khác nhau. Tất cả ví dụ sẽ được lưu trữ trong các sheet riêng biệt.

8 Cách Khác Nhau Để Tách Ô Theo Dấu Phân Cách Bằng Công Thức Trong Excel

Bạn có thể cần chia ô trong Excel trong một số trường hợp. Điều này thường xảy ra khi sao chép thông tin từ Internet, cơ sở dữ liệu, hoặc đồng nghiệp. Ví dụ điển hình là khi bạn có tên đầy đủ và muốn tách thành họ và tên đệm/ten.

1. Kết Hợp Các Hàm Chuỗi Với Hàm SEARCH Để Tách Văn Bản Phân Cách Bởi Dấu Gạch Ngang

Bước đầu tiên để tách theo dấu phân cách là xác định vị trí của dấu phân cách đó. Khi đã xác định vị trí, bạn có thể dễ dàng tách từ một trong hai bên. Chúng ta sẽ dùng hàm SEARCH để tìm dấu phân cách, sau đó trích xuất giá trị bằng hàm LEFT, MID hoặc RIGHT.

1.1. Kết Hợp Hàm LEFT Và SEARCH

Hàm LEFT có 2 tham số: văn bản và số ký tự. Văn bản đã biết, số ký tự sẽ dùng hàm SEARCH để tính.

Các Bước Thực Hiện:

• Chọn ô chứa kết quả, nhập công thức:

• Nhấn Enter.
• Kéo Fill Handle (dấu + nhỏ góc dưới bên phải ô) xuống để sao chép công thức cho phạm vi còn lại, hoặc nhấn đúp vào dấu + để AutoFill.
• Kết quả: Phần bên trái dấu gạch ngách được tách ra.

🔎 Giải Thích Công Thức: Hàm SEARCH trả về vị trí của dấu gạch ngang. Trừ đi 1 để loại bỏ chính dấu gạch ngang, hàm LEFT lấy các ký tự từ đầu đến vị trí đó.

1.2. Kết Hợp Hàm MID Và SEARCH

Để lấy giá trị ở giữa (giữa 2 dấu gạch ngang), dùng kết hợp MID và SEARCH.

Các Bước Thực Hiện:

• Chọn ô, nhập công thức:

• Nhấn Enter.
• Kéo Fill Handle hoặc double-click để sao chép xuống.
• Kết quả: Các giá trị giữa được tách ra.

🔎 Giải Thích: SEARCH tìm vị trí bắt đầu (sau dấu gạch ngang đầu) và vị trí kết thúc (dấu gạch ngang thứ 2). MID trích xuất chuỗi con dựa trên vị trí bắt đầu và độ dài.

1.3. Kết Hợp Hàm RIGHT, LEN, Và SEARCH

Để tách phần cuối cùng (sau dấu gạch ngang cuối cùng).

Các Bước Thực Hiện:

• Chọn ô, nhập công thức:

• Nhấn Enter.
• Kéo Fill Handle hoặc double-click.
• Kết quả: Giá trị cuối cùng được tách ra.

🔎 Giải Thích: LEN trả về độ dài toàn chuỗi. Trừ đi vị trí dấu gạch ngang cuối cùng (tìm bằng SEARCH lồng nhau) được số ký tự phía sau. RIGHT lấy đúng số ký tự đó.

💡 Lưu Ý: Bạn có thể tách theo ký tự bất kỳ bằng cách thay "-" bằng dấu phân cách mong muốn.

2. Kết Hợp Công Thức Để Tách Văn Bản Theo Ngắt Dòng (Line Break)

Tương tự phần 1, chỉ cần thêm hàm CHAR(10) (mã ASCII của ngắt dòng) vào công thức.

2.1. Kết Hợp LEFT, SEARCH, CHAR

Công Thức: =LEFT(B5, SEARCH(CHAR(10),B5,1)-1)

Thực hiện tương tự: Nhập công thức → Enter → Kéo Fill Handle.

2.2. Kết Hợp MID, SEARCH, CHAR

Công Thức: =MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)

2.3. Kết Hợp RIGHT, LEN, CHAR, SEARCH

Công Thức: =RIGHT(B5, LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))

3. Tách Ô Theo Mẫu Chuỗi: Văn Bản Theo Sau Là Số

Ví dụ: Cột chứa "Tên Sinh Viên" và "Mã SV" liền nhau (VD: "NguyenVanA123"), cần tách thành 2 cột.

3.1. Kết Hợp RIGHT, SUM, LEN, SUBSTITUTE Để Lấy Số

Công Thức: =RIGHT(B5, SUM(LEN(B5) - LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))

Giải Thích: SUBSTITUTE thay thế từng chữ số 0-9 bằng rỗng, LEN so sánh độ dài trước/sau để đếm số lượng chữ số. SUM tổng hợp, RIGHT lấy đúng số ký tự đó từ cuối chuỗi.

3.2. Kết Hợp LEFT Và LEN Để Lấy Văn Bản

Sau khi có cột số (giả sử cột D), dùng: =LEFT(B5, LEN(B5)-LEN(D5))

4. Tách Ô Theo Mẫu: Số Theo Sau Là Văn Bản

Ngược lại phần 3: Số ở đầu, văn bản ở sau. Dùng LEFT để lấy số, RIGHT để lấy văn bản.

4.1. Kết Hợp LEFT, SUM, LEN, SUBSTITUTE

=LEFT(B5, SUM(LEN(B5) - LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))

4.2. Kết Hợp RIGHT Và LEN

=RIGHT(B5, LEN(B5)-LEN(C5)) (C5 là cột số vừa tách).

5. Tách Ngày Tháng Từ Ô Bằng RIGHT, LEN, FIND, SUBSTITUTE

Áp dụng khi ngày tháng nằm ở cuối chuỗi.

Công Thức: =RIGHT(B5, LEN(B5) - FIND(" ", SUBSTITUTE(B5, " ", " ", LEN(B5) - LEN(SUBSTITUTE(B5, " ", "")) - 2)))

💡 Lưu Ý: Chỉ hoạt động khi ngày tháng ở cuối chuỗi.

6. Kết Hợp FILTERXML Và SUBSTITUTE

Hàm FILTERXML trích xuất dữ liệu từ XML. Kết hợp với SUBSTITUTE để biến dấu phân cách thành thẻ XML.

Công Thức (lấy mục thứ 2 phân cách bằng dấu phẩy): =FILTERXML("" & SUBSTITUTE(B5, ",", "") & "", "//s[2]")

Thay số trong //s[2] để lấy mục khác (1, 3, 4...).

7. Sử Dụng Hàm TEXTSPLIT (Excel 365/2021+)

Cách ngắn gọn, hiện đại nhất. Tách thành mảng tràn (spill) ra các ô liền kề.

Công Thức: =TEXTSPLIT(B5, ",")

💡 Lưu Ý: Cần đủ cột trống bên cạnh, nếu không sẽ báo lỗi #SPILL!.

8. Kết Hợp TRIM, MID, SUBSTITUTE, REPT, LEN

Kỹ thuật kinh điển "giả lập cột" bằng cách thay dấu phân cách thành chuỗi khoảng trắng rất dài.

Công Thức: =TRIM(MID(SUBSTITUTE($B5, "|", REPT(" ", LEN($B5))), (C$4-1)*LEN($B5)+1, LEN($B5)))

Trong đó ô C4 chứa số thứ tự cột cần tách (1, 2, 3...). Kéo ngang và dọc để tách toàn bộ.

Tách Ô Theo Dấu Phân Cách Bằng Tính Năng "Text To Columns" (Không Dùng Công Thức)

Excel có sẵn tính năng này tại tab Data → nhóm Data Tools → Text to Columns.

Các Bước:

1. Chọn cột/ô dữ liệu.
2. Chọn Text to Columns.
3. Chọn Delimited → Next.
4. Chọn dấu phân cách (Tab, Semicolon, Comma, Space, hoặc Other tùy chỉnh). Xem trước kết quả ở dưới.
5. Next → Chọn định dạng cột (General, Text, Date...) → Finish.

Kết Luận

Trên đây là 8 phương pháp công thức và 1 tính năng tích hợp để tách ô theo dấu phân cách trong Excel. Tùy phiên bản Excel và độ phức tạp dữ liệu mà chọn phương pháp phù hợp (Ưu tiên TEXTSPLIT nếu có Excel 365). Hy vọng bài viết hữu ích. Hãy để lại bình luận nếu có thắc mắc!

Bài Viết Liên Quan

• Cách Tách Một Ô Thành Hai Trong Excel (5 Phương Pháp)
• Cách Chia Một Ô Thành Hai Theo Chéo Và Ngang
• VBA Tách Chuỗi Thành Nhiều Cột Trong Excel
• Excel VBA: Tách Chuỗi Theo Ký Tự (6 Ví Dụ)
• Cách Tạo 2 Dòng Trong 1 Ô Excel (4 Phương Pháp)