Trong bài viết này, chúng ta sẽ tìm hiểu cách tách các ô trong Excel bằng công thức. Bạn sẽ học cách tách văn bản dựa theo dấu phẩy, khoảng trắng hoặc bất kỳ dấu phân cách nào khác, và làm thế nào để phân chia chuỗi thành văn bản và số.
Contents
Làm thế nào để chia văn bản trong Excel bằng cách sử dụng công thức:
Để tách chuỗi trong Excel hay tách chữ và số trong excel, bạn thường sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Ban đầu, có một số công thức có thể phức tạp, nhưng thực tế thì khá đơn giản, và các ví dụ sau đây sẽ cung cấp cho bạn một số đầu mối.
Tách chuỗi bằng dấu phẩy, dấu hai chấm, dấu gạch chéo, dấu gạch ngang hoặc dấu phân cách khác
Khi phân chia các ô trong Excel, việc chính là xác định vị trí của dấu phân cách trong chuỗi văn bản. Tùy thuộc vào công việc của bạn, điều này có thể được thực hiện bằng cách sử dụng hàm Search không phân biệt chữ hoa chữ thường hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi bạn có vị trí của dấu phân cách, sử dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi văn bản.
Để hiểu rõ hơn, hãy xem xét ví dụ sau đây:
Giả sử bạn có một danh sách các SKU của mẫu Loại-Màu-Kích thước, và bạn muốn chia tách cột thành 3 cột riêng biệt:
- Để trích xuất tên mục (tất cả các ký tự trước dấu nối đầu tiên), chèn công thức sau trong B2, và sau đó sao chép nó xuống cột:
= LEFT (A2, search ("-", A2,1) -1)
Trong công thức này, hàm Search xác định vị trí của dấu nối đầu tiên (“-“) trong chuỗi và chức năng LEFT sẽ chiết tất cả các ký tự còn lại (bạn trừ 1 từ vị trí của dấu nối bởi vì bạn không muốn có dấu nối).
- Để trích xuất màu sắc (tất cả các ký tự giữa các dấu gạch nối thứ 2 và thứ 3), hãy nhập công thức sau trong C2, và sau đó sao chép nó xuống các ô khác:
=MID(A2, SEARCH("-", A2)+1, SEARCH("-", A2, SEARCH("-",A2)+1)-SEARCH("-",A2)-1)
Như bạn có thể biết, hàm MID có cú pháp sau:
MID (văn bản, start_num, num_chars)
Nơi:
- Văn bản: nơi để trích xuất văn bản từ.
- Start_num: vị trí của kí tự đầu tiên để trích xuất.
- Num_chars: số ký tự để trích xuất.
Trong công thức trên, văn bản được trích ra từ ô A2, và 2 đối số khác được tính bằng cách sử dụng 4 hàm SEARCH khác:
- Số bắt đầu (start_num) là vị trí của dấu nối đầu tiên +1:
SEARCH("-", A2) + 1 - Số ký tự để trích xuất (num_chars): sự khác biệt giữa vị trí của dấu nối thứ hai và dấu nối đầu tiên, trừ đi 1:
SEARCH("-", A2, SEARCH("-", A2) +1) - SEARCH("-", A2) -1
- Để trích xuất kích thước (tất cả các ký tự sau dấu nối thứ 3), hãy nhập công thức sau trong D2:
= RIGHT (A2, LEN (A2) - SEARCH ("-", A2, SEARCH ("-", A2) + 1))
Trong công thức này, hàm LEN trả về tổng chiều dài của chuỗi, từ đó bạn trừ đi vị trí của dấu nối thứ hai. Sự khác biệt là số ký tự sau dấu nối thứ hai và hàm RIGHT chiết xuất chúng.
Trong một cách tương tự, bạn có thể phân chia cột bằng bất kỳ kí tự nào khác. Tất cả bạn phải làm là thay thế “-” bằng ký tự phân cách bắt buộc, ví dụ như dấu cách (” “), dấu gạch chéo (“/”), dấu hai chấm (“;”), dấu chấm phẩy (“;”) và vân vân.
Mẹo: Trong các công thức trên, +1 và -1 tương ứng với số ký tự trong dấu phân cách. Trong ví dụ này, nó là một dấu nối (1 ký tự). Nếu dấu phân cách của bạn bao gồm 2 ký tự, ví dụ: Dấu phẩy và khoảng trắng, sau đó chỉ cung cấp dấu phẩy (“,”) cho hàm SEARCH, và sử dụng +2 và -2 thay vì +1 và -1.
Làm thế nào để phân chia chuỗi bằng cách ngắt dòng trong Excel:
Để chia văn bản bằng khoảng trắng, hãy sử dụng các công thức tương tự như công thức được minh họa trong ví dụ trước. Sự khác biệt duy nhất là bạn cần chức năng CHAR để cung cấp cho ký tự ngắt dòng vì bạn không thể gõ trực tiếp vào công thức. Giả sử, các ô mà bạn muốn chia nhỏ trông tương tự như sau:
-
Lấy công thức từ ví dụ trước và thay dấu gạch nối (“-“) bằng CHAR (10) trong đó 10 là mã ASCII cho dòng cấp dữ liệu.
-
Để trích xuất tên mặt hàng:
=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)
- Để trích xuất màu sắc:
=MID(A2, SEARCH(CHAR(10),A2) + 1, SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) - SEARCH(CHAR(10),A2) - 1)
- Để trích xuất kích thước:
=RIGHT(A2,LEN(A2) - SEARCH(CHAR(10), A2, SEARCH(CHAR(10),A2) + 1))
Và đây là kết quả:
Làm thế nào để phân chia văn bản và số trong Excel:
Để bắt đầu, không có giải pháp tổng quát cho tất cả các chuỗi chữ số. Công thức nào để sử dụng phụ thuộc vào mẫu chuỗi cụ thể. Dưới đây bạn sẽ tìm thấy công thức cho 3 kịch bản thường gặp nhất.
Ví dụ 1. Chia chuỗi của loại ‘văn bản + số’
Giả sử bạn có một cột các chuỗi với văn bản và số kết hợp, trong đó một số luôn luôn theo sau văn bản. Bạn muốn phá vỡ các chuỗi ban đầu để văn bản và số xuất hiện trong các ô riêng biệt, như sau:
Để trích xuất các số, sử dụng công thức mảng sau đây, được hoàn thành bằng cách 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, sử dụng:
=LEFT (A2, LEN(A2) -LEN(C2))
Trường hợp A2 là chuỗi ban đầu, và C2 là số trích xuất.
Công thức hoạt động như thế nào:
- Công thức để trích xuất số: Về cơ bản, công thức tìm kiếm mọi số có thể từ 0 đến 9 trong chuỗi nguồn, tính số lượng và trả về nhiều ký tự từ ký tự cuối chuỗi ban đầu.
- Công thức để trích xuất văn bản: Bạn tính toán bao nhiêu ký tự văn bản chuỗi chứa bằng cách trừ số chữ số chiết xuất từ tổng chiều dài của chuỗi gốc.
Ví dụ 2. Chia chuỗi của loại ‘số + văn bản’
Nếu bạn đang tách các ô nơi văn bản xuất hiện sau một số, bạn có thể trích xuất các số với công thức mảng này (hoàn thành bằng cách nhấn Ctrl + Shift + Enter):
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "")))))
Công thức tương tự như công thức mảng từ ví dụ trước, ngoại trừ bạn sử dụng hàm LEFT thay vì RIGHT, bởi vì trong trường hợp này số luôn xuất hiện ở phía bên trái của chuỗi. Một khi bạn đã có các con số, trích xuất văn bản bằng cách trừ số chữ số từ tổng chiều dài của chuỗi gốc.
Ví dụ 3. Trích xuất chỉ số từ chuỗi số ‘số văn bản’
Nếu công việc của bạn đòi hỏi phải trích xuất tất cả các số từ một chuỗi trong định dạng ‘number-text-number’, bạn có thể sử dụng công thức sau:
=SUMPRODUCT(MID(0 & A2, LARGE(INDEX(ISNUMBER(-MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)) * ROW(INDIRECT("1:" & LEN(A2)))) + 1, 1) * 10 ^ ROW(INDIRECT("1:" & LEN(A2))) / 10)
Trường hợp A2 là chuỗi văn bản ban đầu.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Học Excel Online. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này. Hãy tham gia ngay tại địa chỉ: Học Excel Online
