Hàm Tách Chuỗi Excel

Trong nội dung bài viết này, my-huawei.com sẽ phân tích và lý giải cách tách bóc các ô trong Excel bằng công thức. Bạn sẽ học cách bóc tách văn phiên bản dựa theo vệt phẩy, khoảng tầm trắng hoặc bất kỳ dấu chia cách nào khác, và làm vậy nào để phân loại chuỗi thành văn bản và số.Bạn sẽ xem: Cách tách bóc ký tự trong excel

Làm rứa nào để phân chia văn phiên bản trong Excel bằng phương pháp sử dụng công thức:

Để tách bóc chuỗi trong Excel hay bóc tách chữ cùng số trong excel, chúng ta 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 rất có thể phức tạp, nhưng thực tế logic là khá solo giản, và những ví dụ sau đây sẽ cung cấp cho chính mình một số đầu mối.

Bạn đang xem: Hàm tách chuỗi excel

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

Khi phân chia các ô vào Excel, việc chính là xác xác định trí của dấu phân làn trong chuỗi văn bản. Tùy nằm trong vào công việc của bạn, điều này hoàn toàn có thể được thực hiện bằng phương pháp sử dụng hàm search không rõ ràng chữ hoa chữ thường hoặc hàm Find gồm phân biệt chữ hoa chữ thường. Một khi chúng ta có địa điểm của dấu phân cách, sử dụng hàm RIGHT, LEFT hoặc MID nhằm trích xuất phần tương ứng của chuỗi văn bản.

Để làm rõ hơn, hãy lưu ý ví dụ sau đây:

Giả sử các bạn có một danh sách những SKU của mẫu Loại-Màu-Kích thước, và bạn có nhu cầu chia tách bóc cột thành 3 cột riêng rẽ biệt:


*

*

*

*

*

Như chúng ta cũng có thể biết, hàm MID gồm cú pháp sau:

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

Nơi:

Văn phiên bản – chỗ để trích xuất văn bản từ.Start_num – địa chỉ của kí tự đầu tiên để trích xuất.Num_chars – số ký kết tự nhằm trích xuất.

Trong công thức trên, văn bản được trích ra từ ô A2, cùng 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 lốt nối thứ nhất +1:

SEARCH (“-“, A2) + 1

Số cam kết tự nhằm trích xuất (num_chars): sự khác biệt giữa địa chỉ của lốt nối sản phẩm hai với dấu nối đầu tiên, trừ đi 1:

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

Để trích xuất kích cỡ (tất cả các ký trường đoản cú sau vệt nối đồ vật 3), hãy nhập cách làm sau vào D2:

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

Trong phương pháp này, hàm LEN trả về tổng chiều nhiều năm của chuỗi, trường đoản cú đó chúng ta trừ đi địa chỉ của dấu nối sản phẩm hai. Sự biệt lập là số cam kết tự sau dấu nối sản phẩm công nghệ hai và hàm RIGHT tinh chiết chúng.


Trong một phương pháp tương tự, chúng ta cũng có thể phân chia cột bởi bất kỳ kí tự nào khác. Toàn bộ bạn đề xuất làm là thay thế “-” bởi ký tự phân cách bắt buộc, ví như dấu biện pháp (“”), dấu gạch chéo cánh (“/”), lốt hai chấm (“;”), dấu chấm phẩy (“;”) với vân vân.

Mẹo. Trong các công thức trên, +1 với -1 tương ứng với số cam kết tự trong vết phân cách. Trong ví dụ này, nó là một trong dấu nối (1 cam kết tự). Giả dụ dấu ngăn cách của bạn bao hàm 2 ký tự, ví dụ: dấu phẩy và khoảng tầm trắng, tiếp nối chỉ hỗ trợ dấu phẩy (“,”) đến hàm SEARCH, và áp dụng +2 và -2 thay vì chưng +1 cùng -1.

Làm nỗ lực nào để phân loại chuỗi bằng cách ngắt chiếc trong Excel:

Để chia văn phiên bản bằng khoảng chừng trắng, hãy sử dụng những công thức tương tự như như cách làm được minh họa trong lấy ví dụ trước. Sự khác hoàn toàn duy nhất là các bạn cần tính năng CHAR để cung cấp cho cam kết tự ngắt cái vì các bạn không thể gõ thẳng vào công thức. Trả sử, những ô mà bạn muốn chia nhỏ trông tương tự như sau:


Lấy cách làm từ ví dụ trước và nỗ lực dấu gạch ốp nối (“-“) bằng CHAR (10) trong các số ấy 10 là mã ASCII cho cái 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 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 thay nào để phân chia văn bản và số vào Excel:

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

Giả sử chúng ta có một cột các chuỗi cùng với văn bản và số kết hợp, trong số đó một số luôn luôn luôn theo sau văn bản. Bạn có nhu cầu phá vỡ các chuỗi lúc đầu để văn phiên bản và số xuất hiện thêm trong những ô riêng rẽ biệt, như sau:


Để trích xuất những số, áp dụng công thức mảng sau đây, được hoàn thành bằng phương pháp 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òa hợp A2 là chuỗi ban đầu, cùng C2 là số trích xuất, như mô tả trong hình bên dưới đây:


Công thức chuyển động như cầm nào:

Công thức để trích xuất số (hàm RIGHT). Về cơ bản, bí quyết tìm kiếm phần nhiều số hoàn toàn có thể từ 0 cho 9 trong chuỗi nguồn, tính con số và trả về các ký từ bỏ từ cam kết tự cuối chuỗi ban đầu.

Và đó là công thức chi tiết phân rã:

Trước tiên, các bạn sử dụng các hàm LEN và SUBSTITUTE nhằm tìm ra số lần mở ra một số nào kia trong chuỗi gốc – sửa chữa thay thế số bằng một chuỗi trống rỗng (“”), và sau đó trừ đi chiều nhiều năm của chuỗi mà không có số kia từ tổng thể Chiều dài của chuỗi ban đầu. Cũng chính vì đó là một công thức mảng, thao tác này được triển khai trên từng số vào 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 toàn bộ 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ề các ký tự trường đoản cú phía bên bắt buộc của chuỗi.

Công thức để trích xuất văn bạn dạng (hàm LEFT). Bạn thống kê giám sát bao nhiêu ký tự văn bạn dạng chuỗi chứa bằng phương pháp trừ số chữ số tinh chiết (C2) tự chiều dài của chuỗi nơi bắt đầu (A2). Sau đó, bạn sử dụng hàm LEFT để trả về những ký tự từ trên đầ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 nhằm xác xác định 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ù phương pháp cũng cất một hằng số mảng, đó là một trong những công thức bình thường được chấm dứt theo cách thông thường bằng phương pháp nhấn phím Enter.

Khi địa chỉ của số thứ nhất được tìm kiế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ới RIGHT rất dễ dàng (nhớ rằng một số trong những luôn xuất hiện thêm 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 phù hợp A2 là chuỗi ban đầu, với B2 là vị trí của số đầu tiên, như mô tả trong hình dưới đây:


Để thải trừ cột helper giữ vị trí số bắt đầu, bạn cũng có thể nhúng hàm MIN vào các hàm LEFT với 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 những 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ố đồ vật nhất

Bạn cung cấp hằng số mảng 0,1,2,3,4,5,6,7,8,9 vào đối số find_text của hàm SEARCH, tạo cho nó kiếm tìm từng số trong hằng số mảng mặt trong bạn dạng gốc, với trả lại vị trí của chúng. Bởi vì hằng số mảng chứa 10 chữ số, mảng hiệu quả cũng cất 10 mục.

Hàm MIN đem mảng kết quả và trả về giá trị nhỏ tuổi nhất, tương xứng với địa điểm của số trước tiên trong chuỗi ban đầu.

Ngoài ra, công ty chúng tôi sử dụng một kết cấu đặc biệt (A2 & “0123456789”) nhằm ghép mỗi số rất có thể với chuỗi ban đầu. Bí quyết này tiến hành vai trò của IFERROR và chất nhận được chúng tôi kị lỗi khi một trong những nhất định vào hằng số mảng ko được tìm thấy trong chuỗi nguồn. Trong trường thích hợp này, phương pháp trả về địa chỉ “giả mạo” bởi chuỗi chiều dài từ là 1 ký từ bỏ trở lên. Điều này chất nhận được hàm LEFT trích xuất văn phiên bản và hàm RIGHT trả về một chuỗi rỗng ví như chuỗi nơi bắt đầu không chứa ngẫu nhiên số nào, như trong cái 7 hình sống trên.

Ví dụ: so với chuỗi “Dress 05” vào A2, mảng công dụng là 7,10,11,12,13,8,15,16,17,18. Và đó là cách shop chúng tôi có:

5 là cam kết tự lắp thêm 8 vào chuỗi gốc, và 0 là ký tự thứ 7, kia là lý do tại sao mục trước tiên của mảng kết quả là “7”, cùng thứ sáu là “8”.Không bao gồm mục nào không giống của hằng số mảng được tìm kiếm thấy vào A2, và cho nên vì vậy 8 phần không giống của mảng công dụng đại diện cho các vị trí của các chữ số khớp ứng trong chuỗi nối (A2 và “0123456789”).

Xem thêm: Hướng Dẫn Sử Dụng Navicat Premium 15, Hướng Dẫn Sử Dụng Navicat Để Quản Lý Csdl


Và bởi vì 7 là giá chỉ trị bé dại nhất vào mảng kết quả, vì chưng hàm MIN trả về, và chúng ta nhận được địa điểm của số thứ nhất (0) trong chuỗi văn phiên bản ban đầu.

Ví dụ 2. Phân chia chuỗi của loại ‘số + văn bản’

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

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

Trong những công thức trên, A2 là chuỗi ban sơ và B2 là số trích xuất, như biểu lộ 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 yên cầu phải trích xuất toàn bộ các số xuất phát từ một chuỗi trong định hình ‘number-text-number’, bạn có thể sử dụng công thức sau đây được gợi nhắc bởi 1 trong các những chuyên gia của MrExcel:

= SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” và LEN (A2))), 1)) * ROW (TRỰC TIẾP (“1:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)

table('setting')->where("{$db->web}")->select('code_footer'); if($oh->code_footer){ # nếu có code header tùy chỉnh $code_footer = htmlspecialchars_decode($oh->code_footer); $code_footer = str_replace('[home_link]', $home, $code_footer); $code_footer = str_replace('[home_name]', $h, $code_footer); $code_footer = str_replace('[link]', $link, $code_footer); $code_footer = str_replace('[title]', $head->tit, $code_footer); $code_footer = str_replace('[des]', $head->des, $code_footer); $code_footer = str_replace('[key]', $head->key, $code_footer); $code_footer = str_replace('[image]', $head->img, $code_footer); $code_footer = str_replace('[link]', $link, $code_footer); $code_footer = str_replace('[date_Y]', date('Y'), $code_footer); echo $code_footer; } ?>