Trong bài viết này, binhchanhhcm.edu.vn sẽ giải thích cách bóc các ô trong Excel bởi công thức. Bạn sẽ học cách tách bóc văn bạn dạng dựa theo vệt phẩy, khoảng chừng trắng hoặc bất kỳ dấu phân làn nào khác, và làm nuốm nào để phân loại chuỗi thành văn bản và số.
Bạn đang xem: Tách text trong excel
Làm vắt nào để phân tách văn bạn dạng trong Excel bằng phương pháp sử dụng công thức:
Để bóc tách chuỗi trong Excel hay bóc chữ với số vào excel, bạn thường sử dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. Lúc đầu, một số công thức hoàn toàn có thể phức tạp, nhưng thực tiễn logic là khá đối kháng giản, và các ví dụ dưới đây sẽ cung cấp cho bạn một số đầu mối.
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 ngăn cách khác
Khi phân chia những ô vào 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 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ữ hay hoặc hàm Find tất cả phân biệt chữ hoa chữ thường. Một khi chúng ta có vị trí của vệt phân cách, thực hiện hàm RIGHT, LEFT hoặc MID để trích xuất phần tương xứng của chuỗi văn bản.
Để nắm rõ hơn, hãy chăm chú 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ách làm sau vào B2, và sau đó sao chép 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 thứ nhất (“-“) trong chuỗi và công dụng LEFT sẽ chiết tất cả các ký kết tự còn lại (bạn trừ 1 từ địa chỉ của lốt nối cũng chính 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ư bạn 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 – nơi để trích xuất văn phiê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 bí quyết trên, văn bản được trích ra tự ô A2, với 2 đối số khác được tính bằng cách sử dụng 4 hàm tìm kiếm khác:
Số bước đầu (start_num) là địa điểm của vết nối trước tiên +1:SEARCH (“-“, A2) + 1
Số cam kết tự để trích xuất (num_chars): sự biệt lập giữa vị trí của vệt nối sản phẩm công nghệ hai và dấu nối đầu tiên, trừ đi 1:SEARCH (“-“, A2, tìm kiếm (“-“, A2) +1) – search (“-“, A2) -1
Để trích xuất form size (tất cả các ký từ bỏ sau vết nối thiết bị 3), hãy nhập phương pháp sau trong D2:= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, A2, tìm kiếm (“-“, 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 vị trí của vệt nối lắp thêm hai. Sự biệt lập là số ký tự sau dấu nối vật dụng hai cùng hàm RIGHT tinh chiết chúng.
Mẹo. Trong số công thức trên, +1 và -1 khớp ứng với số ký 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 ký kết tự). Ví như dấu ngăn cách của bạn bao gồm 2 cam kết tự, ví dụ: lốt phẩy và khoảng trắng, kế tiếp chỉ cung cấp dấu phẩy (“,”) mang lại hàm SEARCH, và áp dụng +2 cùng -2 thay vày +1 và -1.
Làm cố kỉnh nào để phân loại chuỗi bằng phương pháp ngắt mẫu trong Excel:
Để phân chia văn bản bằng khoảng tầm trắng, hãy sử dụng những công thức tương tự như phương pháp được minh họa trong ví dụ trước. Sự biệt lập duy tốt nhất là bạn cần tác dụng CHAR để hỗ trợ cho cam kết tự ngắt cái vì chúng ta không thể gõ thẳng vào công thức. Giả sử, các ô mà bạn muốn chia nhỏ tuổi trông tựa như 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 cầm nào để phân chia văn bạn dạng và số trong Excel:
Để bắt đầu, ko 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 nhằm sử dụng dựa vào vào mẫu mã chuỗi chũm thể. Dưới đây các bạn sẽ tìm thấy phương pháp cho 3 kịch bạn dạng thường gặp gỡ nhất.
Xem thêm: Cách Thêm Ô Trong Word 2010, Thêm Ô, Hàng Hoặc Cột Vào Bảng
Ví dụ 1. Chia chuỗi của loại ‘văn bạn dạng + số’
Giả sử các bạn có một cột những chuỗi với văn phiê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 có nhu cầu phá vỡ những chuỗi ban đầu để văn phiên bản và số xuất hiện trong các ô riêng biệt biệt, như sau:
Để trích xuất những số, sử 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à C2 là số trích xuất, như mô tả trong hình bên dưới đây:
Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bản, cách làm tìm kiếm đều số rất có thể từ 0 mang đến 9 vào chuỗi nguồn, tính số lượng và trả về các ký tự từ ký tự cuối chuỗi ban đầu.
Và đấy là công thức cụ thể phân rã:
Trước tiên, chúng ta sử dụng các hàm LEN với SUBSTITUTE nhằm tìm ra số lần xuất hiện thêm một số nào kia trong chuỗi gốc – sửa chữa thay thế số bởi một chuỗi rỗng (“”), và tiếp đến trừ đi chiều dài của chuỗi mà không tồn tại số đó từ tổng số Chiều dài của chuỗi ban đầu. Bởi vì đó là 1 trong công thức mảng, thao tác làm việc này được tiến hành 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 thêm của tất cả các chữ số trong chuỗi nguồn.Cuối cùng, hàm RIGHT trả về những ký tự từ bỏ phía bên buộc phải của chuỗi.Công thức để trích xuất văn phiên bản (hàm LEFT). Bạn giám sát và đo lường bao nhiêu ký kết tự văn bạn dạng chuỗi chứa bằng cách trừ số chữ số chiết xuất (C2) từ chiều lâu năm của chuỗi nơi bắt đầu (A2). Sau đó, bạn thực hiện hàm LEFT để trả về những ký tự từ đầu chuỗi.
Một phương án khác (công thức không tồn tại mảng)
Giải pháp thay thế sửa chữa sẽ sử dụng công thức sau nhằm xác xác định trí của số thứ nhất trong chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 & “0123456789”))
Mặc dù cách làm cũng cất một hằng số mảng, đó là 1 trong công thức bình thường được xong xuôi theo biện pháp thông thường bằng phương pháp nhấn phím Enter.
Khi địa chỉ của số đầu tiên được tra cứu thấy, bạn cũng có thể tách văn phiên bản và số bằng phương pháp sử dụng những công thức LEFT với RIGHT rất đơn giản (nhớ rằng một số trong những 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ới B2 là vị trí 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 & “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 thống kê giám sát vị trí của số thứ nhất
Bạn hỗ trợ 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, tạo cho nó tìm kiếm từng số trong hằng số mảng mặt trong bản gốc, cùng trả lại địa điểm của chúng. Cũng chính vì hằng số mảng đựng 10 chữ số, mảng kết quả cũng cất 10 mục.
Hàm MIN rước mảng công dụng và trả về giá bán trị nhỏ tuổi nhất, tương ứng với địa chỉ của số đầu tiên trong chuỗi ban đầu.
Ngoài ra, shop chúng tôi sử dụng một cấu tạo đặc biệt (A2 & “0123456789”) nhằm ghép từng số có thể với chuỗi ban đầu. Cách này triển khai 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 trong hằng số mảng không được search thấy trong chuỗi nguồn. Trong trường đúng theo này, bí quyết trả về địa chỉ “giả mạo” bằng chuỗi chiều dài từ một ký trường đoản cú trở lên. Điều này được cho phép 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 giả dụ chuỗi cội không chứa bất kỳ 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 hiệu quả là 7,10,11,12,13,8,15,16,17,18. Và đây là cách công ty chúng tôi có:
5 là cam kết tự trang bị 8 vào chuỗi gốc, với 0 là cam kết tự thiết bị 7, kia là tại sao tại sao mục thứ nhất của mảng công dụng là “7”, và thứ sáu là “8”.Không có mục nào không giống của hằng số mảng được search thấy vào 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 những chữ số tương xứng trong chuỗi nối (A2 & “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 ô địa điểm văn phiên bản xuất hiện sau một số, bạn 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 tương tự như bí quyết mảng từ ví dụ như trước, kế bên bạn thực hiện hàm LEFT thay vì chưng RIGHT, chính vì trong trường thích hợp này số luôn mở ra ở phía phía 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 biện pháp trừ số chữ số trường đoản cú 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 thuở đầu và B2 là số trích xuất, như miêu tả trong hình bên 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 quá trình của bạn đòi hỏi phải trích xuất tất cả các số xuất phát điểm từ một chuỗi trong định dạng ‘number-text-number’, bạn có thể sử dụng công thức dưới đây được nhắc nhở bởi 1 trong 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)
Trường vừa lòng A2 là chuỗi văn bạn dạng ban đầu.
