Tách Ô trong Excel bằng Công Thức: Hướng Dẫn Chi Tiết

Tách dữ liệu trong Excel là một thao tác thường xuyên, đặc biệt khi xử lý dữ liệu phức tạp. Bài viết này từ Thủ Thuật sẽ hướng dẫn bạn cách tách ô trong Excel bằng công thức, giúp bạn dễ dàng phân chia văn bản dựa trên dấu phẩy, khoảng trắng, hoặc bất kỳ ký tự phân cách nào khác. Chúng ta cũng sẽ tìm hiểu cách tách chuỗi thành văn bản và số.

Minh họa tách chuỗi văn bản trong Excel

Tách Chuỗi theo Dấu Phẩy, Dấu Hai Chấm, Dấu Gạch Chéo, hoặc Ký Tự Phân Cách Khác

Nguyên tắc chính khi tách ô trong Excel là xác định vị trí của ký tự phân cách. Bạn có thể sử dụng hàm SEARCH (không phân biệt chữ hoa chữ thường) hoặc FIND (phân biệt chữ hoa chữ thường). Sau khi xác định vị trí, dùng hàm LEFT, RIGHT, hoặc MID để trích xuất phần văn bản mong muốn.

Ví dụ: Tách chuỗi SKU dạng “Loại-Màu-Kích thước” thành 3 cột riêng biệt.

1. Trích xuất “Loại” (trước dấu gạch nối đầu tiên):

=LEFT(A2,SEARCH("-",A2,1)-1)

Hàm SEARCH tìm vị trí dấu gạch nối đầu tiên. Hàm LEFT trích xuất các ký tự bên trái vị trí này (trừ đi 1 để loại bỏ dấu gạch nối).

Sử dụng hàm LEFT để trích xuất ký tự trước dấu gạch nối đầu tiên

2. Trích xuất “Màu” (giữa dấu gạch nối thứ hai và thứ ba):

=MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1)

Hàm MID trích xuất ký tự từ vị trí bắt đầu (vị trí dấu gạch nối đầu tiên + 1) với độ dài xác định (khoảng cách giữa dấu gạch nối thứ hai và thứ nhất, trừ đi 1).

Sử dụng hàm MID để trích xuất ký tự giữa dấu gạch nối thứ hai và thứ ba

3. Trích xuất “Kích thước” (sau dấu gạch nối thứ ba):

=RIGHT(A2,LEN(A2)-SEARCH("-",A2,SEARCH("-",A2)+1))

Hàm LEN tính tổng chiều dài chuỗi. Hàm SEARCH tìm vị trí dấu gạch nối thứ hai. Hàm RIGHT trích xuất các ký tự bên phải vị trí này.

Sử dụng hàm RIGHT để trích xuất ký tự sau dấu gạch nối thứ ba

Tương tự, bạn có thể thay “-” bằng ký tự phân cách khác như dấu cách (” “), dấu gạch chéo (“/”), dấu hai chấm (“:”), dấu chấm phẩy (“;”).

Tách Chuỗi bằng Ký Tự Xuống Dòng

Để tách văn bản theo ký tự xuống dòng, sử dụng hàm CHAR(10) (mã ASCII cho ký tự xuống dòng) thay cho ký tự phân cách trong các công thức trên.

Ví dụ:

Các ô cần được tách bằng khoảng trắng

=LEFT(A2,SEARCH(CHAR(10),A2,1)-1)
=MID(A2,SEARCH(CHAR(10),A2)+1,SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1)-SEARCH(CHAR(10),A2)-1)
=RIGHT(A2,LEN(A2)-SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1))

Tách chuỗi bằng ký tự xuống dòng

Tách Văn Bản và Số

Việc tách văn bản và số phụ thuộc vào cấu trúc của chuỗi. Dưới đây là một số ví dụ phổ biến:

Ví dụ 1: Chuỗi “Văn bản + Số”

Tách chuỗi thành văn bản và số

Trích xuất số (công thức mảng – nhấn Ctrl + Shift + Enter):

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

Trích xuất văn bản:

=LEFT(A2,LEN(A2)-LEN(C2))

Ví dụ 2: Chuỗi “Số + Văn bản”

Tách cột chuỗi với số đứng trước văn bản

Trích xuất số (công thức mảng – nhấn Ctrl + Shift + Enter):

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

Trích xuất văn bản:

=RIGHT(A2,LEN(A2)-LEN(B2))

Ví dụ 3: Trích xuất số từ chuỗi “Số-Văn bản-Số”

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),0),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Kết luận

Bài viết đã hướng dẫn chi tiết cách tách ô trong Excel bằng công thức, từ đơn giản đến phức tạp. Hy vọng những kiến thức này sẽ giúp bạn xử lý dữ liệu trong Excel hiệu quả hơn. Hãy khám phá thêm các bài viết khác của Thủ Thuật để nâng cao kỹ năng Excel của bạn!