Tách chuỗi ký tự trong excel
Trong bài viết này, yeahflashback.com sẽ phân tích và lý giải cách tách các ô vào Excel bởi công thức. Bạn sẽ học cách bóc tách văn phiên bản dựa theo dấu phẩy, khoảng tầm trắng hoặc bất kỳ dấu phân làn nào khác, với làm thế nào để phân loại chuỗi thành văn bản và số.
Bạn đang xem: Tách chuỗi ký tự trong excel
Làm gắng nào để phân 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ố vào excel, chúng ta thường thực hiện hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. Thời điểm đầu, một số trong những công thức có thể phức tạp, nhưng thực tiễn logic là khá đối chọi giản, và các ví dụ tiếp sau đây sẽ cung cấp cho mình một số đầu mối.
Tách chuỗi bởi dấu phẩy, vết hai chấm, lốt gạch chéo, lốt gạch ngang hoặc dấu chia cách khác
Khi phân chia những ô trong Excel, việc đó là xác xác định trí của dấu chia cách trong chuỗi văn bản. Tùy ở trong vào các bước của bạn, điều này hoàn toàn có thể được thực hiện bằng cách sử dụng hàm tìm kiếm không khác nhau chữ hoa chữ thường hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi chúng ta có vị trí của lốt phân cách, thực hiện 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 chu đáo ví dụ sau đây:
Giả sử các 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 bóc tách cột thành 3 cột riêng biệt:
Để trích xuất thương hiệu mục (tất cả các ký trường đoản cú trước vệt nối đầu tiên), chèn phương pháp sau trong B2, và sau đó coppy nó xuống cột:
= LEFT (A2, search (“-“, A2,1) -1)
Trong bí quyết này, hàm tìm kiếm xác định vị trí của dấu nối trước tiên (“-“) vào chuỗi và tính năng LEFT đã chiết tất cả các cam kết tự còn sót lại (bạn trừ 1 từ địa chỉ của vệt nối bởi vì bạn không thích có dấu nối).
=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1
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 bản – địa điểm để trích xuất văn phiên bản từ.Start_num – địa chỉ của kí tự trước tiên để trích xuất.Num_chars – số ký kết tự nhằm trích xuất.Trong cách làm trên, văn bạn dạng được trích ra trường đoản cú ô A2, cùng 2 đối số không giống được tính bằng phương pháp sử dụng 4 hàm tìm kiếm khác:
Số bước đầu (start_num) là địa chỉ của dấu nối trước tiên +1:SEARCH (“-“, A2) + 1
Số cam kết tự nhằm trích xuất (num_chars): sự biệt lập giữa vị trí của vệt nối trang bị hai cùng dấu nối đầu tiên, trừ đi 1:SEARCH (“-“, A2, search (“-“, A2) +1) – tìm kiếm (“-“, A2) -1
Để trích xuất kích cỡ (tất cả những ký từ sau dấu nối trang bị 3), hãy nhập phương pháp sau vào D2:= RIGHT (A2, LEN (A2) – search (“-“, A2, tìm kiếm (“-“, A2) + 1))
Trong công thức này, hàm LEN trả về tổng chiều dài của chuỗi, trường đoản cú đó chúng ta trừ đi vị trí của lốt nối máy hai. Sự khác biệt là số ký tự sau dấu nối lắp thêm hai cùng hàm RIGHT chiết xuất chúng.
Mẹo. Trong số công thức trên, +1 với -1 tương ứng với số cam kết tự trong lốt phân cách. Trong lấy ví dụ này, nó là 1 trong những dấu nối (1 ký kết tự). Giả dụ dấu phân cách của bạn bao gồm 2 ký kết tự, ví dụ: vết phẩy và khoảng trắng, tiếp nối chỉ cung cấp dấu phẩy (“,”) cho hàm SEARCH, và sử dụng +2 và -2 thay do +1 và -1.
Làm thế nào để phân chia chuỗi bằng cách ngắt cái trong Excel:
Để phân chia văn bản bằng khoảng tầm trắng, hãy sử dụng các công thức tương tự như như cách làm được minh họa trong lấy ví dụ như trước. Sự khác biệt duy độc nhất vô nhị là chúng ta cần công dụng CHAR để cung cấp cho cam kết tự ngắt mẫu vì bạn không thể gõ trực tiếp vào công thức. Giả sử, các ô mà bạn có nhu cầu chia nhỏ dại trông tương tự như sau:
=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 núm nào để phân chia văn phiên bản và số vào Excel:
Để bắt đầu, ko có chiến thuật tổng quát mắng cho toàn bộ các chuỗi chữ số. Công thức nào để sử dụng dựa vào vào mẫu mã chuỗi vắt 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 mặt nhất.
Xem thêm: Cách Cài Đặt Excel 2007 Chi Tiết Bằng Hình Ảnh, Cài Đặt Office 2007
Ví dụ 1. Phân chia chuỗi của nhiều loại ‘văn bạn dạng + số’
Giả sử bạn có một cột những chuỗi cùng với văn bản và số kết hợp, trong những số ấy một số luôn luôn theo sau văn bản. Bạn muốn phá vỡ những chuỗi thuở đầu để văn bản và số mở ra trong các ô riêng biệt, như sau:
Để trích xuất những số, áp dụng công thức mảng sau đây, được trả 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 vừa lòng A2 là chuỗi ban đầu, với C2 là số trích xuất, như thể hiện trong hình dưới đây:
Công thức để trích xuất số (hàm RIGHT). Về cơ bản, công thức tìm kiếm đầy đủ số có thể từ 0 đến 9 trong chuỗi nguồn, tính số lượng và trả về những ký trường đoản cú từ cam kết 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 những hàm LEN và SUBSTITUTE để tìm ra số lần lộ diện một số nào đó trong chuỗi nơi bắt đầu – thay thế số bằng một chuỗi trống rỗng (“”), và sau đó trừ đi chiều lâu năm của chuỗi mà không tồn tại số kia 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 làm việc này được thực hiện 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 tất cả các lần xuất hiện thêm của toàn bộ các chữ số vào chuỗi nguồn.Cuối cùng, hàm RIGHT trả về nhiều ký tự tự phía bên nên của chuỗi.Công thức để trích xuất văn bạn dạng (hàm LEFT). Bạn đo lường bao nhiêu ký kết tự văn phiên bản chuỗi chứa bằng cách trừ số chữ số tinh chiết (C2) tự chiều dài của chuỗi cội (A2). Sau đó, bạn sử dụng hàm LEFT để trả về những ký tự từ trên đầu chuỗi.
Một phương án khác (công thức không có mảng)
Giải pháp thay thế sẽ thực hiện công thức sau nhằm 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 và “0123456789”))
Mặc dù phương pháp cũng cất một hằng số mảng, đó là một trong công thức thông thường được chấm dứt theo giải pháp thông thường bằng cách nhấn phím Enter.
Khi địa điểm của số trước tiên được search thấy, bạn có thể tách văn bạn dạng và số bằng phương pháp sử dụng những công thức LEFT cùng RIGHT rất dễ dàng và đơn giản (nhớ rằng một vài 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 phù hợp A2 là chuỗi ban đầu, cùng B2 là địa điểm của số đầu tiên, như diễn đạt trong hình bên dưới đây:
= LEFT (A2, MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 và “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 và “0123456789”)) + 1)
Công thức tính toán vị trí của số thứ nhất
Bạn cung ứng 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 nên nó tìm từng số vào hằng số mảng bên trong bản gốc, với trả lại địa chỉ của chúng. Bởi vì hằng số mảng chứa 10 chữ số, mảng công dụng cũng đựng 10 mục.
Hàm MIN đem mảng hiệu quả và trả về giá chỉ 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, chúng tôi sử dụng một cấu trúc đặc biệt (A2 & “0123456789”) nhằm ghép mỗi số có thể với chuỗi ban đầu. Biện pháp này thực hiện vai trò của IFERROR và cho phép chúng tôi kiêng lỗi khi một số nhất định vào hằng số mảng ko được tra cứu thấy vào chuỗi nguồn. Trong trường phù hợp này, phương pháp trả về địa điểm “giả mạo” bởi chuỗi chiều dài từ là 1 ký từ bỏ trở lên. Điều này có thể chấp nhận được hàm LEFT trích xuất văn bản và hàm RIGHT trả về một chuỗi rỗng trường hợp chuỗi gốc không chứa ngẫu nhiên số nào, như trong loại 7 hình ngơi nghỉ 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 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à cam kết tự lắp thêm 7, kia là tại sao tại sao mục đầu tiên của mảng tác dụng là “7”, với thứ sáu là “8”.Không gồm mục nào khác của hằng số mảng được tìm kiếm thấy vào A2, và do đó 8 phần không giống của mảng kết quả đại diện cho các vị trí của những chữ số tương ứng trong chuỗi nối (A2 và “0123456789”).
Ví dụ 2. Chia chuỗi của nhiều loại ‘số + văn bản’
Nếu các bạn đang bóc các ô nơi văn bản xuất hiện sau một số, chúng ta cũng có thể trích xuất những số với bí quyết 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 giống như như phương pháp mảng từ ví dụ trước, ngoài bạn sử dụng hàm LEFT thay vị RIGHT, bởi vì trong trường vừa lòng này số luôn xuất hiện ở 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 phiên bản bằng phương 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 các công thức trên, A2 là chuỗi lúc đầu và B2 là số trích xuất, như bộc lộ trong hình dưới đây:
Ví dụ 3. Trích xuất chỉ số trường đoản cú chuỗi số ‘số văn bản’
Nếu các bước của bạn đòi hỏi phải trích xuất toàn bộ các số từ một chuỗi trong định hình ‘number-text-number’, bạn cũng có thể sử dụng công thức dưới đây được gợi ý bởi 1 trong những những chuyên viên 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:” và LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)
Trường vừa lòng A2 là chuỗi văn phiên bản ban đầu.
