Tách Dữ Liệu Trong Excel

Trong nội dung bài viết này, i-google-map.com vẫn lý giải giải pháp tách bóc những ô vào Excel bởi bí quyết. quý khách hàng đang học tập cách tách vnạp năng lượng bản dựa theo dấu phẩy, khoảng chừng trắng hoặc ngẫu nhiên vệt chia cách làm sao khác, cùng có tác dụng cầm như thế nào nhằm phân loại chuỗi thành văn uống bạn dạng với số.

Bạn đang xem: Tách dữ liệu trong excel

Làm cụ như thế nào để phân chia văn bạn dạng vào Excel bằng cách thực hiện công thức:

Để bóc tách chuỗi vào Excel hay bóc tách chữ với số trong excel, bạn hay thực hiện hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. trước khi, một số phương pháp có thể phức hợp, dẫu vậy thực tế xúc tích và ngắn gọn là khá dễ dàng, cùng những ví dụ dưới đây sẽ hỗ trợ cho mình một vài làm mối.

Tách chuỗi bằng dấu phẩy, lốt hai chnóng, dấu gạch men chéo cánh, dấu gạch ốp ngang hoặc giấu ngăn cách khác


*

*

khi phân loại các ô trong Excel, bài toán chính là xác xác định trí của vết chia cách vào chuỗi văn bạn dạng. Tùy nằm trong vào các bước của công ty, vấn đề đó rất có thể được triển khai bằng phương pháp áp dụng hàm Search không biệt lập chữ hoa chữ thường hoặc hàm Find tất cả rành mạch chữ hoa chữ thường. Một khi chúng ta có vị trí của vết phân cách, áp dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi văn uống bản.

Để nắm rõ rộng, hãy để ý ví dụ sau đây:

Giả sử các bạn gồm một list các SKU của mẫu Loại-Màu-Kích thước, cùng bạn có nhu cầu phân tách tách cột thành 3 cột riêng biệt biệt:

*

Để trích xuất tên mục (toàn bộ các ký kết tự trước lốt nối đầu tiên), cnhát bí quyết sau trong B2, cùng tiếp nối xào nấu nó xuống cột:

= LEFT (A2, tìm kiếm (“-“, 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 (“-“) vào chuỗi và chức năng LEFT vẫn tách tất cả các ký kết từ bỏ còn lại (các bạn trừ 1 trường đoản cú địa điểm của vết nối bởi vì bạn không muốn bao gồm lốt nối).

*

Để trích xuất Color (tất cả các ký kết trường đoản cú thân các vệt gạch ốp nối thứ hai và thứ 3), hãy nhập cách làm sau trong C2, và tiếp nối xào nấu nó xuống những ô khác:

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

*

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

MID (vnạp năng lượng bạn dạng, start_num, num_chars)

Nơi:

Vnạp năng lượng bản – chỗ để trích xuất văn uống bản từ bỏ.Start_num – địa điểm của kí từ thứ nhất để trích xuất.Num_chars – số cam kết trường đoản cú nhằm trích xuất.

Trong công thức bên trên, vnạp năng lượng bạn dạng được trích ra từ ô A2, với 2 đối số không giống được tính bằng phương pháp áp dụng 4 hàm SEARCH khác:

Số ban đầu (start_num) là địa điểm của vết nối trước tiên +1:

SEARCH (“-“, A2) + 1

Số ký trường đoản cú nhằm trích xuất (num_chars): sự khác hoàn toàn thân địa điểm của vết nối sản phẩm công nghệ nhị cùng vết nối trước tiên, trừ đi 1:

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

Để trích xuất form size (toàn bộ các ký tự sau dấu nối sản phẩm 3), hãy nhập phương pháp sau trong D2:

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

Trong cách làm này, hàm LEN trả về tổng chiều lâu năm của chuỗi, từ bỏ kia bạn trừ đi địa điểm của vệt nối máy hai. Sự biệt lập là số cam kết tự sau lốt nối thứ hai với hàm RIGHT triết xuất chúng.

*
Trong một giải pháp giống như, chúng ta cũng có thể phân chia cột do ngẫu nhiên kí trường đoản cú làm sao không giống. Tất cả chúng ta yêu cầu làm là sửa chữa “-” bằng cam kết trường đoản cú ngăn cách yêu cầu, ví như vết cách (“”), lốt gạch ốp chéo (“/”), vết nhì chấm (“;”), lốt chnóng phẩy (“;”) cùng vân vân.

Mẹo. Trong những cách làm bên trên, +1 cùng -1 tương xứng cùng với số ký kết từ bỏ vào lốt chia cách. Trong ví dụ này, nó là 1 trong lốt nối (1 cam kết tự). Nếu vệt chia cách của bạn bao gồm 2 ký trường đoản cú, ví dụ: Dấu phẩy và khoảng chừng Trắng, tiếp nối chỉ hỗ trợ lốt phẩy (“,”) mang đến hàm SEARCH, cùng sử dụng +2 với -2 thế bởi vì +1 cùng -1.

Làm cầm như thế nào nhằm phân loại chuỗi bằng cách ngắt mẫu trong Excel:

Để phân tách vnạp năng lượng bạn dạng bởi khoảng tầm white, hãy sử dụng các bí quyết giống như như phương pháp được minch họa vào ví dụ trước. Sự biệt lập tốt nhất là bạn phải tác dụng CHAR để hỗ trợ đến ký từ bỏ ngắt cái vì các bạn quan yếu gõ thẳng vào bí quyết. Giả sử, những ô nhưng mà bạn có nhu cầu phân tách nhỏ trông giống như như sau:

*
Lấy phương pháp từ ví dụ trước cùng nạm lốt gạch nối (“-“) bằng CHAR (10) trong những số ấy 10 là mã ASCII mang đến dòng cung cấp tài liệu.

Để trích xuất tên phương diện 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 gắng làm sao để phân loại văn uống bản và số vào Excel:

Để ban đầu, không tồn tại phương án tổng thể cho tất cả những chuỗi chữ số. Công thức như thế nào nhằm áp dụng phụ thuộc vào mẫu chuỗi cụ thể. Dưới phía trên các bạn sẽ tra cứu thấy phương pháp mang lại 3 kịch bạn dạng thường chạm chán tuyệt nhất.

Xem thêm: Phim Tam Sinh Tam Thế Thập Lý Đào Hoa Dương Mịch, Tam Sinh Tam Thế Thập Lý Đào Hoa

Ví dụ 1. Chia chuỗi của nhiều loại ‘vnạp năng lượng bạn dạng + số’

Giả sử bạn tất cả một cột những chuỗi với vnạp năng lượng bản và số phối kết hợp, trong các số ấy một số luôn luôn luôn theo sau vnạp năng lượng phiên bản. Quý Khách mong mỏi phá đổ vỡ các chuỗi lúc đầu nhằm văn uống bạn dạng cùng số mở ra trong các ô lẻ tẻ, nhỏng sau:

*

Để trích xuất những số, áp dụng bí quyết mảng tiếp 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 vnạp năng lượng phiên bản, sử dụng:

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

Trường đúng theo A2 là chuỗi lúc đầu, và C2 là số trích xuất, như biểu hiện trong hình bên dưới đây:

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

Công thức để trích xuất số (hàm RIGHT). Về cơ bạn dạng, cách làm tìm kiếm tìm đông đảo số hoàn toàn có thể từ bỏ 0 cho 9 vào chuỗi nguồn, tính số lượng với trả về nhiều ký trường đoản cú từ bỏ ký trường đoản cú cuối chuỗi ban sơ.

Và đó là bí quyết chi tiết phân rã:

trước hết, các bạn áp dụng những hàm LEN cùng SUBSTITUTE để tìm thấy chu kỳ xuất hiện một số làm sao kia trong chuỗi cội – sửa chữa thay thế số bằng một chuỗi rỗng (“”), với tiếp đến trừ đi chiều lâu năm của chuỗi mà lại không tồn tại số đó tự tổng số Chiều lâu năm của chuỗi thuở đầu. Bởi vày đó là một trong những cách làm 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 tất cả các lần lộ diện 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ý kết tự từ phía mặt phải của chuỗi.

Công thức để trích xuất vnạp năng lượng phiên bản (hàm LEFT). quý khách tính toán bao nhiêu ký kết từ vnạp năng lượng bản 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 gốc (A2). Sau đó, các bạn áp dụng hàm LEFT nhằm trả về các cam kết từ từ trên đầu chuỗi.

Một chiến thuật khác (cách làm không có mảng)

Giải pháp thay thế sẽ áp dụng công thức sau để xác định vị 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 cho dù công thức cũng chứa một hằng số mảng, kia là 1 trong những công thức thông thường được xong theo cách thông thường bằng phương pháp thừa nhận phím Enter.

lúc vị trí của số trước tiên được tìm kiếm thấy, bạn có thể tách bóc vnạp năng lượng phiên bản với số bằng phương pháp thực hiện các phương pháp LEFT và RIGHT vô cùng đơn giản dễ dàng (hãy nhờ rằng một số trong những luôn xuất hiện thêm sau vnạp năng lượng bản):

Để trích xuất văn uống bản:

= LEFT (A2, B2-1)

Để trích xuất số:

=RIGHT(B2, LEN(A1)-B2+1)

Trường thích hợp A2 là chuỗi ban sơ, với B2 là địa chỉ của số trước tiên, nlỗi biểu thị trong hình bên dưới đây:

*
Để loại trừ cột helper duy trì địa điểm số bước đầu, bạn 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 vnạp năng lượng bản:

= 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 những 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 thù địa chỉ của số vật dụng nhất

Quý khách hàng cung ứng 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 nên nó kiếm tìm từng số vào hằng số mảng bên phía trong phiên bản nơi bắt đầu, và trả lại địa điểm 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 chứa 10 mục.

Hàm MIN mang mảng hiệu quả với trả về quý hiếm nhỏ tuổi độc nhất vô nhị, tương xứng cùng với địa điểm của số đầu tiên trong chuỗi thuở đầu.

Trong khi, chúng tôi thực hiện một cấu tạo đặc biệt quan trọng (A2 và “0123456789”) để ghnghiền từng số có thể với chuỗi ban đầu. Cách này tiến hành vai trò của IFERROR với có thể chấp nhận được Cửa Hàng chúng tôi tách lỗi Khi một số trong những khăng khăng trong 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ề vị trí “mang mạo” bằng chuỗi chiều nhiều năm từ 1 ký từ trở lên. Như vậy cho phép hàm LEFT trích xuất vnạp năng lượng bản và hàm RIGHT trả về một chuỗi trống rỗng nếu chuỗi cội không đựng ngẫu nhiên số làm sao, nhỏng trong loại 7 hình sinh sống bên trên.

Ví dụ: so 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à đó là bí quyết chúng tôi có:

5 là cam kết từ bỏ thiết bị 8 vào chuỗi nơi bắt đầu, và 0 là cam kết từ bỏ sản phẩm 7, chính là nguyên nhân tại vì sao mục trước tiên của mảng tác dụng là “7”, cùng sản phẩm công nghệ sáu là “8”.Không bao gồm mục làm sao khác của hằng số mảng được tìm thấy trong A2, và vì vậy 8 phần khác của mảng tác dụng thay mặt cho những vị trí của những chữ số tương xứng vào chuỗi nối (A2 & “0123456789”).

*
Và bởi vì 7 là quý hiếm bé dại tuyệt nhất trong mảng hiệu quả, do hàm MIN trả về, cùng chúng ta nhận ra địa chỉ của số trước tiên (0) trong chuỗi vnạp năng lượng bạn dạng lúc đầu.

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

Nếu nhiều người đang tách bóc các ô chỗ văn uống bạn dạng lộ diện sau một vài, chúng ta cũng có thể trích xuất những số với cách làm mảng này (xong xuôi bằng cách dấ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ư bí quyết mảng trường đoản cú ví dụ trước, xung quanh bạn áp dụng hàm LEFT thay vày RIGHT, cũng chính vì vào trường hợp này số luôn luôn lộ diện sinh hoạt phía phía trái của chuỗi. Một lúc bạn đã có các con số, trích xuất văn uống phiên bản bằng phương pháp trừ số chữ số trường đoản cú 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 bên trên, A2 là chuỗi lúc đầu cùng B2 là số trích xuất, như biểu thị trong hình dưới đây:

*

ví dụ như 3. Trích xuất chỉ số từ bỏ chuỗi số ‘số vnạp năng lượng bản’

Nếu công việc của doanh nghiệp đòi hỏi phải trích xuất toàn bộ những số từ một chuỗi vào định dạng ‘number-text-number’, bạn có thể áp dụng công thức dưới đây được lưu ý bởi một trong số những Chuyên Viên 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: “và LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)

Trường đúng theo A2 là chuỗi văn bản thuở đầu.

*