Tách Ký Tự Trong Chuỗi Excel

     

Trong bài viết này, all4kids.edu.vn sẽ lý giải cách tách bóc các ô vào Excel bởi công thức. Các bạn sẽ học cách tách văn bản dựa theo lốt phẩy, khoảng chừng trắng hoặc ngẫu nhiên dấu phân cách nào khác, cùng làm nạm nào để phân chia chuỗi thành văn phiên bản và số.

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


Làm nắm nào để chia văn bạn dạng trong Excel bằng cách sử dụng công thức:

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

Tách chuỗi bằng dấu phẩy, vệ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 ô trong Excel, việc chính là xác định vị trí của dấu chia cách trong chuỗi văn bản. Tùy ở trong vào quá trình 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 phân biệt chữ hoa chữ thường xuyên 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 dấu 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 xứng của chuỗi văn bản.

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

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

*


*

*

Để trích xuất tên mục (tất cả các ký tự trước lốt nối đầu tiên), chèn cách làm 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ách làm này, hàm search xác xác định trí của vệt nối thứ nhất (“-“) vào chuỗi và công dụng LEFT đã chiết toàn bộ các cam kết tự sót lại (bạn trừ 1 từ vị trí của lốt nối cũng chính vì bạn không thích có lốt nối).

*

Để trích xuất màu sắc (tất cả những ký tự giữa những dấu gạch men nối thứ hai và thứ 3), hãy nhập cách làm sau vào C2, với sau đó xào luộc nó xuống các ô khác:

=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 phiên bản – nơi để trích xuất văn phiên bản từ.Start_num – vị trí 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 bí quyết trên, văn phiên bản được trích ra tự ô A2, cùng 2 đối số không giống được tính bằng cách sử dụng 4 hàm search khác:

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

SEARCH (“-“, A2) + 1

Số ký tự nhằm trích xuất (num_chars): sự khác hoàn toàn 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 size (tất cả những ký trường đoản cú sau vệt nối đồ vật 3), hãy nhập công thức sau trong D2:

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

Trong bí quyết này, hàm LEN trả về tổng chiều lâu năm của chuỗi, trường đoản cú đó chúng ta trừ đi địa chỉ của vệt nối thứ hai. Sự biệt lập là số cam kết tự sau vết nối lắp thêm hai cùng hàm RIGHT triết xuất chúng.

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

Mẹo. Trong các công thức trên, +1 với -1 tương xứng với số ký tự trong dấu 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ự). Giả dụ dấu phân làn của bạn bao hàm 2 ký kết tự, ví dụ: vết phẩy và khoảng chừng trắng, kế tiếp chỉ cung ứng dấu phẩy (“,”) mang lại hàm SEARCH, và áp dụng +2 cùng -2 thay vì +1 với -1.

Làm nuốm nào để phân chia chuỗi bằng cách ngắt dòng trong Excel:

Để chia văn bản bằng khoảng trắng, hãy sử dụng những công thức tương tự như như công thức được minh họa trong ví dụ trước. Sự biệt lập duy nhất là bạn cần chức năng CHAR để hỗ trợ cho cam kết tự ngắt chiếc vì chúng ta không thể gõ trực tiếp vào công thức. đưa sử, các ô mà bạn muốn chia bé dại trông tương tự như như sau:

*
Lấy cách làm từ lấy một ví dụ trước và nỗ lực dấu gạch nối (“-“) bởi CHAR (10) trong những số đó 10 là mã ASCII cho dòng 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:

=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 loại văn bản và số vào Excel:

Để bắt đầu, không có chiến thuật tổng quát cho toàn bộ các chuỗi chữ số. Cách làm nào nhằm sử dụng nhờ vào vào chủng loại chuỗi rứa thể. Dưới đây các bạn sẽ tìm thấy cách làm cho 3 kịch bạn dạng thường gặp gỡ nhất.

Xem thêm: Giới Thiệu Khái Quát Về Văn Miếu Quốc Tử Giám Hà Nội Trước Khi Ghé Thăm

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

Giả sử bạn có một cột những chuỗi cùng với văn phiên bản và số kết hợp, trong các số đó một số luôn luôn luôn theo sau văn bản. Bạn có nhu cầu phá vỡ những chuỗi thuở đầu để văn phiên bản và số xuất hiện trong những ô riêng 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 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 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 hoạt động như vắt nào:

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 gần như số hoàn toàn có thể từ 0 đến 9 vào chuỗi nguồn, tính con số và trả về các ký trường đoản cú từ cam kết 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 những hàm LEN với SUBSTITUTE để tìm ra số lần xuất hiện một số nào kia trong chuỗi nơi bắt đầu – sửa chữa số bởi một chuỗi rỗng (“”), và tiếp nối trừ đi chiều lâu năm của chuỗi mà không tồn tại số kia từ tổng thể Chiều dài của chuỗi ban đầu. Chính vì đó là một trong công thức mảng, làm việc này được triển khai trên mỗi số trong 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 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ự từ phía bên nên của chuỗi.

Công thức để trích xuất văn bản (hàm LEFT). Bạn tính toán bao nhiêu ký tự văn phiên 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 đó, bạn áp dụng hàm LEFT để trả về nhiều ký tự từ đầ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 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ù cách làm cũng chứa một hằng số mảng, đó là một trong những công thức thông thường được 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 tra cứu thấy, chúng ta cũng có thể tách văn bạn dạng và số bằng phương pháp sử dụng các công thức LEFT và RIGHT rất dễ dàng và đơn giản (nhớ rằng một vài 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 vừa lòng A2 là chuỗi ban đầu, và B2 là địa chỉ của số đầu tiên, như biểu lộ trong hình dưới đây:

*
Để vứt bỏ cột helper giữ địa điểm số bắt đầu, bạn cũng có thể nhúng hàm MIN vào các hàm LEFT cùng 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 thống kê giám sát vị trí của số trang bị nhất

Bạn hỗ trợ 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, làm cho nó kiếm tìm từng số trong hằng số mảng bên trong bản gốc, và trả lại vị trí của chúng. Chính vì hằng số mảng đựng 10 chữ số, mảng kết quả cũng đựng 10 mục.

Hàm MIN rước mảng kết quả và trả về giá trị bé dại nhất, khớp ứ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 cấu tạo đặc biệt (A2 và “0123456789”) để ghép từng số rất có thể với chuỗi ban đầu. Bí quyết này triển khai vai trò của IFERROR và cho phép chúng tôi kị lỗi khi một vài nhất định vào hằng số mảng không được tìm kiếm thấy trong chuỗi nguồn. Trong trường thích hợp này, bí quyết trả về vị trí “giả mạo” bằng chuỗi chiều dài từ là 1 ký tự 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 nếu như chuỗi nơi bắt đầu không chứa ngẫu nhiên số nào, như trong dòng 7 hình sinh sống trên.

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

*
Và cũng chính vì 7 là giá chỉ trị nhỏ tuổ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ố trước tiên (0) vào chuỗi văn bạn dạng ban đầu.

Xem thêm: Hướng Dẫn Làm Mứt Khoai Lang, Cách Làm Mứt Khoai Lang Dẻo Và Giòn Ngon Tuyệt

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

Nếu các bạn đang bóc tách các ô địa điểm văn bạn dạng xuất hiện tại sau một số, bạn có thể trích xuất những 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ựa như như công thức mảng từ lấy ví dụ trước, xung quanh bạn áp dụng hàm LEFT thay vày RIGHT, chính vì trong trường hòa hợp này số luôn mở ra ở phía bên trái của chuỗi. Một khi bạn đã có các bé số, trích xuất văn phiên bản bằng giải 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 các công thức trên, A2 là chuỗi ban đầu và B2 là số trích xuất, như diễn tả trong hình bên 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 đòi hỏi phải trích xuất toàn bộ các số xuất phát từ 1 chuỗi trong format ‘number-text-number’, bạn có thể sử dụng công thức sau đây được lưu ý bởi 1 trong 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:” 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 bản ban đầu.

*