Lấy Chuỗi Ký Tự Trong Excel

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

Bạn đang xem: Lấy chuỗi ký tự trong excel


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

Để tách bóc chuỗi vào Excel hay bóc chữ cùng số trong excel, chúng ta thường sử 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ố công thức hoàn toàn có thể phức tạp, nhưng thực tế logic là khá đơn giản, và các ví dụ tiếp sau đây sẽ cung cấp cho chính mình 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 những ô trong Excel, việc đó là xác xác định trí của dấu phân làn trong chuỗi văn bản. Tùy thuộc vào các bước của bạn, điều này rất có thể được thực hiện bằng phương pháp sử dụng hàm search không phân minh 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 bạn có vị trí của dấu phân cách, áp dụng 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 lưu ý ví dụ sau đây:

Giả sử chúng ta có một danh sách những SKU của mẫu Loại-Màu-Kích thước, và bạn có nhu cầu chia bóc 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 dấu nối đầu tiên), chèn cách làm sau vào B2, với sau đó sao chép nó xuống cột:

= LEFT (A2, search (“-“, A2,1) -1)

Trong phương pháp này, hàm search xác định vị trí của vết nối đầu tiên (“-“) vào chuỗi và tác dụng LEFT đã chiết toàn bộ các ký tự còn lại (bạn trừ 1 từ vị trí của vết nối chính vì bạn không thích có dấu nối).

*

Để trích xuất màu sắc (tất cả những ký tự giữa các dấu gạch ốp nối thứ hai và thứ 3), hãy nhập phương pháp sau trong C2, và sau đó sao chép nó xuống những ô 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 có cú pháp sau:

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

Nơi:

Văn phiên bản – khu vực để trích xuất văn bạn dạng từ.Start_num – địa điểm của kí tự trước tiên để trích xuất.Num_chars – số ký kết tự để trích xuất.

Trong công thức trên, văn bản được trích ra tự ô A2, với 2 đối số không giống được tính bằng cách sử dụng 4 hàm search khác:

Số bước đầu (start_num) là địa chỉ của vệt nối đầu tiên +1:

SEARCH (“-“, A2) + 1

Số ký kết tự để trích xuất (num_chars): sự biệt lập giữa vị trí của dấu nối vật dụng hai và dấu nối đầu tiên, trừ đi 1:

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

Để trích xuất form size (tất cả các ký từ bỏ sau lốt nối thiết bị 3), hãy nhập bí quyết sau vào 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 nhiều năm của chuỗi, từ bỏ đó chúng ta trừ đi địa điểm của dấu nối sản phẩm công nghệ hai. Sự biệt lập là số ký tự sau dấu nối đồ vật hai cùng hàm RIGHT triết xuất chúng.

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

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

Làm núm nào để phân loại chuỗi bằng phương pháp ngắt chiếc trong Excel:

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

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

Để trích xuất tên khía cạnh hàng:

=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)

Để trích xuất color 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ố nào để phân loại văn bạn dạng và số trong Excel:

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

Xem thêm: Nên Dùng Office 2013 Hay 2016, Microsoft Office 2019 Hay Office 365

Ví dụ 1. Phân tách 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 với văn phiên bản và số kết hợp, trong số đó một số luôn luôn theo sau văn bản. Bạn muốn phá vỡ những chuỗi ban sơ để văn bạn dạng và số xuất hiện thêm trong những ô riêng biệt biệt, như sau:

*

Để trích xuất các số, thực hiện 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 thích hợp A2 là chuỗi ban đầu, cùng C2 là số trích xuất, như biểu đạt trong hình dưới đây:

*
Công thức hoạt động như rứa nào:

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bản, công thức tìm kiếm mọi 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ừ 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, các bạn sử dụng các hàm LEN với SUBSTITUTE nhằm tìm ra số lần xuất hiện một số nào kia trong chuỗi cội – sửa chữa thay thế số bằng một chuỗi trống rỗng (“”), và tiếp nối trừ đi chiều lâu năm của chuỗi mà không có số kia từ tổng cộng Chiều nhiề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, làm việc này được tiến hành 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 toàn bộ các lần lộ diện 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ừ phía bên cần của chuỗi.

Công thức nhằm trích xuất văn phiên bản (hàm LEFT). Bạn thống kê giám sát bao nhiêu ký tự văn bản chuỗi chứa bằng cách trừ số chữ số tinh chiết (C2) trường đoản cú chiều lâu năm của chuỗi gốc (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 phương án 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ẽ sử dụng công thức sau để xác định vị 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 chứa một hằng số mảng, đó là 1 trong công thức thông thường được xong xuôi theo bí quyết thông thường bằng cách nhấn phím Enter.

Khi địa chỉ của số trước tiên được kiếm tìm thấy, chúng ta cũng có thể tách văn phiên bản 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 (nhớ rằng một số luôn mở ra 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ư biểu đạt 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 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 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 đo lường vị trí của số vật dụng nhất

Bạn 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, làm cho 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 điểm của chúng. Cũng chính vì hằng số mảng cất 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á chỉ trị bé dại nhất, tương ứng với địa chỉ của số trước tiên trong chuỗi ban đầu.

Ngoài ra, cửa hàng 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ố rất có thể với chuỗi ban đầu. Bí quyết này thực hiện vai trò của IFERROR và có thể chấp nhận được chúng tôi kiêng lỗi khi một số nhất định trong hằng số mảng ko được tìm thấy trong chuỗi nguồn. Vào trường vừa lòng này, bí quyết trả về địa điểm “giả mạo” bởi chuỗi chiều dài từ 1 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 nếu như chuỗi cội không chứa bất kỳ số nào, như trong dòng 7 hình ngơi nghỉ 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 công ty chúng tôi có:

5 là cam kết tự thứ 8 trong chuỗi gốc, với 0 là ký tự máy 7, kia là nguyên nhân tại sao mục trước 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 tra cứu thấy trong A2, và vì thế 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 & “0123456789”).

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

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

Nếu bạn đang bóc tách các ô chỗ văn phiên bản xuất hiện nay sau một số, bạn cũng 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 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ư như cách làm mảng từ ví dụ như trước, quanh đó bạn thực hiện hàm LEFT thay vị RIGHT, bởi vì trong trường phù hợp này số luôn xuất hiện thêm ở phía phía bên 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 giải pháp trừ số chữ số từ bỏ tổng chiều dài 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 lúc đầ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 quá trình của bạn yên cầu phải trích xuất tất cả các số từ 1 chuỗi trong định hình ‘number-text-number’, chúng ta cũng có thể sử dụng công thức sau đây được nhắc nhở bởi 1 trong các 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 dạng ban đầu.

*