Trong bài viết này, mình sẽ giải thích 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ố.
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, bạn thường sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Lúc đầu, một số công thức có thể phức tạp, nhưng thực tế logic là 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) – 1 (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ởi 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, như thể hiện trong hình dưới đây:
Công thức hoạt động như thế nào:
Công thức để trích xuất số (hàm RIGHT). 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.
Và đây là công thức chi tiết phân rã:
- Trước tiên, bạn sử dụng các hàm LEN và SUBSTITUTE để tìm ra số lần xuất hiện một số nào đó trong chuỗi gốc – thay thế số bằng một chuỗi rỗng (“”), và sau đó trừ đi chiều dài của chuỗi mà không có số đó từ tổng số Chiều dài của chuỗi ban đầu. Bởi vì đó là một công thức mảng, thao tác này được thực hiện trên mỗi số trong hằng mảng:
LEN (A2) -LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9 “},” “)
- Tiếp theo, hàm SUM thêm tất cả các lần xuất hiện của tất cả các chữ số trong chuỗi nguồn.
- Cuối cùng, hàm RIGHT trả về nhiều ký tự từ phía bên phải của chuỗi.
Công thức để trích xuất văn bản (hàm LEFT). 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 (C2) từ chiều dài của chuỗi gốc (A2). Sau đó, bạn sử dụng hàm LEFT để trả về nhiều ký tự từ đầu chuỗi.
Một giải pháp khác (công thức không có mảng)
Giải pháp thay thế sẽ sử dụng công thức sau để xác định vị trí của số đầu tiên trong chuỗi: = MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A2 & “0123456789”))
Mặc dù công thức cũng chứa một hằng số mảng, đó là một công thức bình thường được hoàn thành theo cách thông thường bằng cách nhấn phím Enter.
Khi vị trí của số đầu tiên được tìm thấy, bạn có thể tách văn bản và số bằng cách sử dụng các công thức LEFT và RIGHT rất đơn giản (nhớ rằng một số luôn xuất hiện sau văn bản):
- Để trích xuất văn bản:
= LEFT (A2, B2-1)
- Để trích xuất số:
=RIGHT(B2, LEN(A1)-B2+1)
Trường hợp A2 là chuỗi ban đầu, và B2 là vị trí của số đầu tiên, như thể hiện trong hình dưới đây:
Để loại bỏ cột helper giữ vị trí số bắt đầu, bạn có thể nhúng hàm MIN vào các hàm LEFT và RIGHT:
- Công thức trích xuất văn bản:
= LEFT (A2, MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A2 & “0123456789”)) – 1)
- Công thức trích xuất các số:
= RIGHT (A2, LEN (A2) -MIN (SEARCH({0,1,2,3,4,5,6,7,8,9}, A2 & “0123456789”)) + 1)
Công thức tính toán vị trí của số thứ nhất
Bạn cung cấp hằng số mảng {0,1,2,3,4,5,6,7,8,9} trong đối số find_text của hàm SEARCH, làm cho nó tìm từng số trong hằng số mảng bên trong bản gốc, và trả lại vị trí của chúng. Bởi vì hằng số mảng chứa 10 chữ số, mảng kết quả cũng chứa 10 mục.
Hàm MIN lấy mảng kết quả và trả về giá trị nhỏ nhất, tương ứng với vị trí của số đầu tiên trong chuỗi ban đầu.
Ngoài ra, chúng tôi sử dụng một cấu trúc đặc biệt (A2 & “0123456789”) để ghép mỗi số có thể với chuỗi ban đầu. Cách này thực hiện vai trò của IFERROR và cho phép chúng tôi tránh lỗi khi một số nhất định trong hằng số mảng không được tìm thấy trong chuỗi nguồn. Trong trường hợp này, công thức trả về vị trí “giả mạo” bằng chuỗi chiều dài từ 1 ký tự trở lên. Điều này cho phép hàm LEFT trích xuất văn bản và hàm RIGHT trả về một chuỗi rỗng nếu chuỗi gốc không chứa bất kỳ số nào, như trong dòng 7 hình ở trên.
Ví dụ: đối với chuỗi “Dress 05” trong A2, mảng kết quả là {7,10,11,12,13,8,15,16,17,18}. Và đây là cách chúng tôi có:
- 5 là ký tự thứ 8 trong chuỗi gốc, và 0 là ký tự thứ 7, đó là lý do tại sao mục đầu tiên của mảng kết quả là “7”, và thứ sáu là “8”.
- Không có mục nào khác của hằng số mảng được tìm thấy trong A2, và do đó 8 phần khác của mảng kết quả đại diện cho các vị trí của các chữ số tương ứng trong chuỗi nối (A2 & “0123456789”).
Và bởi vì 7 là giá trị nhỏ nhất trong mảng kết quả, do hàm MIN trả về, và chúng ta nhận được vị trí của số đầu tiên (0) trong chuỗi văn bản ban đầu.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:
= RIGHT(A2, LEN (A2) -LEN (B2))
Trong các công thức trên, A2 là chuỗi ban đầu và B2 là số trích xuất, như thể hiện trong hình dưới đây:
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 đây được gợi ý bởi một trong những chuyên gia của MrExcel:
= SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)) * ROW (TRỰC TIẾP (“1:” & LEN (A2) (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.
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
