Cách sử dụng hàm cắt chuỗi có điều kiện trong Excel

Trong bài viết hôm nay, chúng ta sẽ cùng nhau tìm hiểu về cách sử dụng hàm cắt chuỗi có điều kiện trong Excel, cùng đón đọc nhé!

Nội dung bài viết

1 Cách để chia văn bản trong Excel bằng cách dùng công thức
2 Cách để phân chia chuỗi bằng cách ngắt dòng trong Excel
3 Cách để phân chia văn bản và số trong Excel:
- 3.1 Ví dụ 1. Chia chuỗi của loại ‘văn bản + số’
- 3.2 Ví dụ 2. Thực hành chia chuỗi của loại ‘số + văn bản’

Cách để chia văn bản trong Excel bằng cách dùng công thức

Để có thể tách chuỗi trong Excel hoặc là tách chữ và số trong Excel, bạn thường dùng hàm LEFT, RIGHT hoặc là hàm MID kết hợp với hàm FIND hoặc là hàm SEARCH. Lúc đầu, một vài công thức có thể sẽ khá phức tạp, nhưng thực tế theo logic là khá đơn giản, và những ví dụ dưới đây sẽ cung cấp cho bạn một vài đầu mối.

Tách chuỗi bằng cách dùng dấu phẩy, dấu gạch chéo, dấu hai chấm, dấu gạch ngang hoặc là dấu phân cách khác

Khi thực hiện phân chia những ô trong Excel, việc chính đó là xác định vị trí dấu phân cách ở chuỗi văn bản. Tùy vào công việc người dùng, điều này có thể được thực hiện bằng việc dùng hàm Search không phân biệt chữ hoa, chữ thường hoặc là hàm Find có phân biệt chữ hoa và chữ thường. Khi mà bạnn đã có vị trí của dấu phân cách, thì dùng hàm RIGHT, LEFT hoặc là hàm MID để trích xuất phần tương ứng trong chuỗi văn bản.

Để có thể hiểu rõ hơn, bạn hãy xem xét ví dụ bên dưới:

Ví dụ bạn đang có một danh sách những SKU của mẫu Loại-Màu-Kích thước, và bạn muốn chia tách chúng thành 3 cột riêng biệt:

Thực hiện trích xuất tên mục (toàn bộ các ký tự trước dấu nối đầu tiên), bạn chèn công thức sau trong B2, rồi sau đó sao chép nó xuống cột:

= LEFT (A2, search (“-“, A2,1) -1)

Ở công thức này, hàm Search sẽ có chức năng xác định vị trí của dấu nối đầu tiên (“-“) tại chuỗi và chức năng hàm LEFT sẽ chiết toàn bộ những ký tự còn lại (bạn tiến hành trừ 1 từ vị trí của dấu nối vì bạn không muốn có dấu nối).

Thực hiện trích xuất màu sắc (toàn bộ ký tự giữa những dấu gạch nối thứ 2 và thứ 3), hãy thực hiện nhập công thức sau tại ô C2, rồi sau đó sao chép nó xuống những ô khác:

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

Như bạn thấy, hàm MID có cú pháp sau:

MID (văn bản, start_num, num_chars)

Trong đó:

Văn bản: là nơi để trích xuất văn bản từ.
Start_num: là vị trí của kí tự đầu tiên để trích xuất.
Num_chars: là số ký tự để trích xuất.

Ở 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 việc dùng 4 hàm SEARCH khác:

Số bắt đầu (start_num): 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 so với dấu nối đầu tiên, trừ đi 1:

SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1

Thực hiện trích xuất kích thước (toàn bộ những ký tự sau dấu nối thứ 3), hãy thực hiện nhập công thức sau trong D2:

= RIGHT (A2, LEN (A2) – SEARCH (“-“, A2, SEARCH (“-“, A2) + 1))

Ở công thức này, hàm LEN sẽ trả về tổng chiều dài của chuỗi, lúc này bạn thực hiện trừ đi vị trí của dấu nối thứ hai. Sự khác biệt ở đây là số ký tự sau dấu nối thứ hai cùng hàm RIGHT chiết xuất chúng.

Ở một cách tương tự, bạn có thể tiến hành phân chia cột bởi bất cứ kí tự nào khác. Toàn bộ mọi thứ bạn phải làm là thay thế “-” bằng ký tự phân cách bắt buộc, chẳng hạn như dấu cách (“”), dấu gạch chéo (“/”), dấu chấm phẩy (“;”), dấu hai chấm (“;”) và vân vân.

Mẹo. Tại các công thức trên, +1 và -1 sẽ tương ứng với số ký tự trong dấu phân cách. Ở ví dụ này, nó là một dấu nối (1 ký tự). Nếu như dấu phân cách của bạn gồm 2 ký tự, ví dụ như: Dấu phẩy và khoảng trắng, tiếp đó chỉ cung cấp dấu phẩy (“,”) cho hàm SEARCH, và dùng +2 và -2 thay vì +1 và -1.

Cách để phân chia chuỗi bằng cách ngắt dòng trong Excel

Để thực hiện chia văn bản bằng khoảng trắng, hãy dùng những công thức tương tự như công thức đã được minh họa ở ví dụ trước. Sự khác biệt duy nhất ở đây là bạn cần chức năng CHAR để có thể cung cấp cho ký tự ngắt dòng bởi vì bạn không thể tiến hành gõ trực tiếp vào công thức. Ví dụ, những ô mà bạn muốn chia nhỏ trông tương tự như bên dưới:

Thực hiện lấy công thức từ ví dụ trước và sau đó thay dấu gạch nối (“-“) bằng CHAR (10), với 10 là mã ASCII cho dòng cấp dữ liệu.

Thực hiện trích xuất tên mặt hàng:

=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)

Thực hiện 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)

Thực hiện trích xuất kích thước:

=RIGHT(A2,LEN(A2) – SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))

Và dưới đây là kết quả thu được:

Cách để phân chia văn bản và số trong Excel:

Để bắt đầu, sẽ không có giải pháp tổng quát cho toàn bộ chuỗi chữ số. Công thức nào để dùng sẽ phụ thuộc vào mẫu chuỗi cụ thể. Các ví dụ dưới đây, bạn sẽ tìm thấy công thức cho 3 kịch bản hay gặp nhất.

Ví dụ 1. Chia chuỗi của loại ‘văn bản + số’

Ví dụ bạn có một cột những chuỗi với văn bản và số kết hợp. Trong đó, một số luôn luôn theo phía sau văn bản. Bạn đang muốn phá vỡ những chuỗi ban đầu để văn bản và số được xuất hiện trong những ô riêng biệt, như bảng bên dưới:

Thực hiện trích xuất các số, bạn dùng công thức mảng sau đây, được hoàn thành bằng việc nhấn tổ hợp phím Ctrl + Shift + Enter:

= RIGHT (A2, SUM (LEN (A2) – LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7” , “8”, “9”}, “”))))

Thực hiện trích xuất văn bản, bạn dùng:

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

Trong trường hợp ô A2 là chuỗi ban đầu, và ô C2 là số trích xuất, hãy tham khảo hình minh họa hình bên dưới:

Xem thêm: Hướng dẫn làm tròn đến hàng nghìn trong Excel

Công thức hoạt động như thế nào:

Công thức để trích xuất số (hàm RIGHT). Xét về cơ bản, công thức để tìm kiếm mọi số có thể từ 0 đến 9 ở chuỗi nguồn, tính số lượng rồi trả về nhiều ký tự từ ký tự cuối chuỗi ban đầu.

Và bên dưới là công thức chi tiết phân rã:

Đầu tiên, bạn dùng những hàm LEN và SUBSTITUTE để tiến hành tìm ra số lần xuất hiện một số nào đó ở chuỗi gốc – thay thế số bởi một chuỗi rỗng (“”), và tiếp đó 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 đầu tiên. Vì đó là một công thức mảng, nên thao tác này được thực hiện trên mỗi số nằm trong hằng mảng:

LEN (A2) -LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9 “},” “)

Tiếp đến, hàm SUM thêm toàn bộ các lần xuất hiện của tất cả những chữ số trong chuỗi nguồn.
Cuối cùng là hàm RIGHT giúp 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 thực hiện tính toán bao nhiêu ký tự văn bản chuỗi chứa bằng việc trừ số chữ số chiết xuất (C2) từ chiều dài của chuỗi gốc (A2). Tiếp đó, bạn dùng hàm LEFT để thực hiện trả về nhiều ký tự từ đầu chuỗi.

Một cách làm khác (công thức không có mảng)

Giải pháp thay thế sẽ dùng công thức sau đây để tiến hành xác định vị trí của số đầu tiên nằm 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ó chứa một hằng số mảng, đó là một công thức bình thường và được hoàn thành theo cách thông thường bằng việc nhấn phím Enter.

Khi vị trí của số đầu tiên đã được tìm thấy, bạn có thể tiến hành tách văn bản và số bằng việc dùng công thức hàm LEFT và RIGHT rất đơn giản (lưu ý rằng một số luôn xuất hiện sau văn bản):

Thực hiện trích xuất văn bản:

= LEFT (A2, B2-1)

Thực hiện 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, tham khảo hình minh họa bên dưới:

Để thực hiện loại bỏ cột helper giữ vị trí số bắt đầu, bạn có thể tiến hành nhúng hàm MIN vào những hàm LEFT và RIGHT:

Công thức trích xuất văn bản như sau:

= 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ố như sau:

= 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 thực hiệ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, sau đó làm cho nó tìm từng số trong hằng số mảng ở bên trong bản gốc, và tiếp đó 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 sẽ chứa 10 mục.

Hàm MIN sẽ lấy mảng kết quả và sau đó trả về giá trị nhỏ nhất, sẽ tương ứng với vị trí của số đầu tiên trong chuỗi ban đầu.

Ngoài ra, bạn dùng một cấu trúc đặc biệt (A2 & “0123456789”) để ghép mỗi số có thể cùng với chuỗi ban đầu. Cách này có sử dụng hàm IFERROR và cho phép bạn tránh lỗi trong trường hợp một số nhất định ở hằng số mảng không được tìm thấy ở chuỗi nguồn. Với trường hợp này, công thức sẽ 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 sẽ cho phép hàm LEFT trích xuất văn bản, hàm RIGHT thì trả về một chuỗi rỗng nếu như chuỗi gốc không có chứa bất kỳ số nào, như mô tả trong dòng 7 hình ở trên.

Ví dụ: với chuỗi “Dress 05” tại A2, thì mảng kết quả là {7,10,11,12,13,8,15,16,17,18}. Và đây là cách bạn có:

5 là ký tự thứ 8 nằm trong chuỗi gốc, và 0 là ký tự thứ 7, đây là lý do vì 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 khác của hằng số mảng tìm thấy trong A2, và vì thế 8 phần khác của mảng kết quả đại diện cho những vị trí của những chữ số tương ứng tại chuỗi nối (A2 & “0123456789”).

Và vì 7 là giá trị nhỏ nhất nằm trong mảng kết quả, bởi vì hàm MIN trả về, và bạn nhận được vị trí số đầu tiên (0) ở chuỗi văn bản ban đầu.

Ví dụ 2. Thực hành chia chuỗi của loại ‘số + văn bản’

Nếu như bạn đang thực hiện tách những ô nơi văn bản xuất hiện sau một số, thì bạn có thể trích xuất những số với công thức mảng này (hoàn thành bằng việc nhấn tổ hợp phím 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 này tương tự như công thức mảng ở ví dụ phía trước, ngoại việc bạn dùng hàm LEFT thay cho hàm RIGHT, bởi vì ở trường hợp này số luôn xuất hiện ở bên trái của chuỗi. Khi bạn đã có những 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 chuỗi gốc, công thức như sau:

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

Ở những công thức trên, ô A2 là chuỗi ban đầu và ô B2 là số trích xuất, được thể hiện ở bảng bên dưới:

Ví dụ 3. Thực hiện trích xuất chỉ số từ chuỗi số ‘số văn bản’

Nếu như công việc của bạn đòi hỏi phải thực hiện trích xuất toàn bộ số từ một chuỗi ở định dạng ‘number-text-number’, bạn có thể dùng công thức sau đây, nó được gợi ý bởi một trong các 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)

Trong trường hợp A2 là chuỗi văn bản ban đầu.