TÁCH KÝ TỰ TRONG CHUỖI EXCEL

Trong nội dung bài viết này, benorevaumolo.online sẽ 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 tách văn phiên bản dựa theo dấu phẩy, khoảng tầm trắng hoặc ngẫu nhiên dấu chia cách nào khác, và làm thế nào để phân loại chuỗi thành văn bạn dạng và số.

Bạn đang xem: Tách ký tự trong chuỗi excel


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

Để tách chuỗi trong Excel hay bóc tách chữ với số trong excel, các bạn thường áp dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Dịp đầu, một số trong những 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à những ví dụ sau đây sẽ cung cấp cho bạn một số đầu mối.

Tách chuỗi bởi dấu phẩy, vệt hai chấm, vết gạch chéo, lốt gạch ngang hoặc dấu phâ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 phân cách trong chuỗi văn bản. Tùy ở trong vào công việc của bạn, điều này rất có thể được thực hiện bằng cách sử dụng hàm search không tách biệt chữ hoa chữ thường xuyên hoặc hàm Find bao gồm phân biệt chữ hoa chữ thường. Một khi chúng ta có địa chỉ 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 khớp ứ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 những SKU của chủng loại Loại-Màu-Kích thước, và bạn có nhu cầu chia 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ý từ bỏ trước dấu nối đầu tiên), chèn bí quyết sau trong B2, với sau đó sao chép 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 xác định trí của vết nối trước tiên (“-“) trong chuỗi và chức năng LEFT đang chiết toàn bộ các ký kết tự sót lại (bạn trừ 1 từ địa điểm của vệt nối cũng chính vì bạn không muốn có vệt nối).

*

Để trích xuất color (tất cả các ký từ giữa những dấu gạch men nối thứ hai và sản phẩm 3), hãy nhập phương pháp sau vào C2, cùng sau đó coppy nó xuống các ô khác:

=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 bao gồm cú pháp sau:

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

Nơi:

Văn bạn dạng – địa điểm để trích xuất vă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ông thức trên, văn phiê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à địa điểm của vệt nối thứ nhất +1:

SEARCH (“-“, A2) + 1

Số ký kết tự để trích xuất (num_chars): sự khác biệt giữa vị trí của vết nối máy hai và 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 thước (tất cả các ký từ bỏ sau vệt nối thiết bị 3), hãy nhập cách làm sau vào D2:

= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, A2, search (“-“, A2) + 1))

Trong công thức này, hàm LEN trả về tổng chiều dài của chuỗi, từ bỏ đó bạn trừ đi vị trí của lốt nối vật dụng hai. Sự khác hoàn toàn là số ký kết tự sau lốt nối đồ vật hai cùng hàm RIGHT chiết xuất chúng.

*
Trong một phương pháp tương tự, bạn có thể phân phân tách cột bởi ngẫu nhiên kí tự làm sao khác. Tất cả bạn buộc phải làm là thay thế sửa chữa “-” bằng ký tự phân làn bắt buộc, ví dụ như dấu cách (“”), vệt gạch chéo (“/”), vết hai chấm (“;”), lốt chấm phẩy (“;”) với vân vân.

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

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

Để phân tách 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ư cách làm được minh họa trong ví dụ trước. Sự khác hoàn toàn duy độc nhất là các bạn cần chức năng CHAR để cung ứng cho ký tự ngắt loại vì chúng ta không thể gõ trực tiếp vào công thức. Giả sử, những ô mà bạn có nhu cầu chia nhỏ tuổi trông giống như như sau:

*
Lấy phương pháp từ ví dụ như trước và cố dấu gạch nối (“-“) bởi CHAR (10) trong số đó 10 là mã ASCII cho mẫu cấp dữ 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 vắt nào để phân loại văn bản và số trong Excel:

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

Xem thêm: Cách Đổi 1 Tệ Bằng Bao Nhiêu Vnd, 1 Tệ Bằng Bao Nhiêu Tiền Việt Nam

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

Giả sử chúng ta có một cột những chuỗi cùng với văn bạn dạng và số kết hợp, trong các 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 lúc đầu để văn phiên bản và số mở ra trong các ô riêng biệt biệt, như sau:

*

Để trích xuất các 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, với C2 là số trích xuất, như bộc lộ trong hình bên dưới đây:

*
Công thức hoạt động như nú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 số lượng và trả về nhiều ký từ bỏ từ cam kết tự cuối chuỗi ban đầu.

Và đó là công thức cụ thể phân rã:

Trước tiên, chúng ta sử dụng những hàm LEN với SUBSTITUTE nhằm tìm ra số lần xuất hiện một số nào đó trong chuỗi cội – thay thế sửa chữa số bằng một chuỗi rỗng (“”), và tiếp đến trừ đi chiều lâu năm của chuỗi mà không có số đó từ tổng thể Chiều lâu năm của chuỗi ban đầu. Cũng chính vì đó là 1 trong những công thức mảng, thao tác làm việc này được thực hiện trên mỗi 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 mở ra của toàn bộ các chữ số vào chuỗi nguồn.Cuối cùng, hàm RIGHT trả về những ký tự từ bỏ phía bên bắt buộc của chuỗi.

Công thức để trích xuất văn bản (hàm LEFT). Bạn giám sát và đo lường bao nhiêu ký tự văn bản chuỗi chứa bằng cách trừ số chữ số chiết xuất (C2) từ chiều dài của chuỗi nơi bắt đầu (A2). Sau đó, bạn áp dụng hàm LEFT nhằm trả về các ký tự từ trên đầu chuỗi.

Một giải pháp khác (công thức không tồn tại mảng)

Giải pháp sửa chữa thay thế sẽ thực hiện công thức sau để xác xác định trí của số trước tiên trong chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”))

Mặc dù công thức cũng đựng một hằng số mảng, đó là 1 trong công thức bình thường được ngừng theo giải pháp thông thường bằng cách nhấn phím Enter.

Khi vị trí của số trước tiên được tìm kiếm thấy, bạn cũng có thể tách văn bạn dạng và số bằng cách sử dụng các công thức LEFT với RIGHT rất đơn giản (nhớ rằng một số luôn lộ diệ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 thích hợp A2 là chuỗi ban đầu, và B2 là địa điểm của số đầu tiên, như biểu thị trong hình bên dưới đây:

*
Để thải trừ cột helper giữ địa chỉ số bắt đầu, bạn có thể 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:

= 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 & “0123456789”)) + 1)

Công thức thống kê giám sát vị trí của số sản phẩm công nghệ 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 kiếm từng số vào 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. Cũng chính vì hằng số mảng chứa 10 chữ số, mảng tác dụng cũng đựng 10 mục.

Hàm MIN mang mảng công dụng và trả về giá bán trị nhỏ nhất, tương xứng với địa điểm của số thứ nhất trong chuỗi ban đầu.

Ngoài ra, shop 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 triển khai vai trò của IFERROR và được cho phép chúng tôi kị lỗi khi một số nhất định trong hằng số mảng ko được tìm kiếm thấy trong chuỗi nguồn. Vào trường 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à một ký tự trở lên. Điều này cho phép hàm LEFT trích xuất văn bản và hàm RIGHT trả về một chuỗi rỗng ví như chuỗi gốc không chứa bất kỳ số nào, như trong chiếc 7 hình nghỉ ngơi trên.

Ví dụ: đối với chuỗi “Dress 05” trong A2, mảng công dụng 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ự sản phẩm 8 vào chuỗi gốc, với 0 là cam kết tự máy 7, đó là tại sao tại sao mục đầu tiên của mảng công dụng là “7”, cùng thứ sáu là “8”.Không 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à do đó 8 phần khác của mảng hiệu quả đại diện cho những vị trí của những chữ số tương xứng trong chuỗi nối (A2 và “0123456789”).

*
Và cũng chính vì 7 là giá trị bé dại nhất vào mảng kết quả, vì chưng hàm MIN trả về, và họ nhận được địa điểm của số trước tiên (0) vào chuỗi văn bạn dạng ban đầu.

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

Nếu chúng ta đang bóc các ô khu vực văn bản xuất hiện nay sau một số, chúng ta có thể trích xuất các số với công thức 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 giống như như phương pháp mảng từ lấy ví dụ như trước, ngoài bạn sử dụng hàm LEFT thay do 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 đã sở hữu các con số, trích xuất văn bạn dạng bằng phương pháp trừ số chữ số từ tổng chiều lâ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 thuở đầu và B2 là số trích xuất, như miêu tả trong hình dưới đây:

*

Ví dụ 3. Trích xuất chỉ số từ bỏ 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ố xuất phát điểm từ 1 chuỗi trong định dạng ‘number-text-number’, bạn có thể sử dụng công thức dưới đây được gợi nhắc bởi 1 trong những 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)

Trường hòa hợp A2 là chuỗi văn bạn dạng ban đầu.

*