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

     

Trong nội dung bài viết này, dulichnangdanang.com sẽ phân tích và lý giải cách bóc tách các ô vào Excel bởi công thức. Bạn sẽ học cách tách bóc văn phiên bản dựa theo lốt phẩy, khoảng chừng trắng hoặc ngẫu nhiên dấu chia cách nào khác, với làm chũm nào để phân chia chuỗi thành văn phiên bản và số.Bạn đang xem: Hàm bóc tách ký tự trong excel

Làm nạm 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:

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

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

Tách chuỗi bởi dấu phẩy, vệt hai chấm, lố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 đó là xác xác định trí của dấu chia cách trong chuỗi văn bản. Tùy thuộc vào công việc của bạn, điều này có thể được thực hiện bằng phương pháp sử dụng hàm tìm kiếm không minh bạch chữ hoa chữ thường xuyên hoặc hàm Find gồm phân biệt chữ hoa chữ thường. Một khi bạn có địa điểm của dấu phân cách, áp dụng hàm RIGHT, LEFT hoặc MID nhằm trích xuất phần khớp ứng của chuỗi văn bản.

Để hiểu rõ hơn, hãy chu đáo ví dụ sau đây:

Giả sử các bạn 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 tách cột thành 3 cột riêng biệt:


*

*

*

*

*

Như chúng ta cũng 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 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 phương pháp trên, văn bạn dạng được trích ra tự ô A2, và 2 đối số khác được tính bằng phương pháp sử dụng 4 hàm search khác:

Số bắt đầu (start_num) là vị trí của dấu nối trước tiên +1:

SEARCH (“-“, A2) + 1

Số ký kết tự nhằm trích xuất (num_chars): sự khác hoàn toàn giữa địa điểm của lốt nối lắp thêm hai với 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ả các ký tự sau dấu nối máy 3), hãy nhập bí quyết sau trong D2:

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

Trong bí quyết này, hàm LEN trả về tổng chiều nhiều năm của chuỗi, từ đó chúng ta trừ đi địa điểm của lốt nối sản phẩm công nghệ hai. Sự biệt lập là số ký kết tự sau vết nối trang bị hai với hàm RIGHT triết xuất chúng.


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

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

Làm vậy nào để phân chia chuỗi bằng cách ngắt mẫu trong Excel:

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


Lấy bí quyết từ lấy ví dụ như trước và rứa dấu gạch nối (“-“) bởi CHAR (10) trong số ấy 10 là mã ASCII cho chiếc 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 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à đó là kết quả:


Làm cầm nào để phân chia văn phiên bản và số vào Excel:

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

Giả sử bạn có một cột các chuỗi cùng với văn bản và số kết hợp, trong các số ấy một số luôn luôn luôn theo sau văn bản. Bạn muốn phá vỡ các chuỗi lúc đầu để văn bạn dạng và số mở ra trong các ô riêng rẽ 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 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 đúng theo A2 là chuỗi ban đầu, cùng C2 là số trích xuất, như diễn tả trong hình bên dưới đây:


Công thức chuyển động như thay 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 các số có thể từ 0 đến 9 vào chuỗi nguồn, tính con số và trả về các ký tự từ cam kết tự cuối chuỗi ban đầu.

Xem thêm: Ăn Sầu Riêng Kỵ Gì? Không Ăn Sầu Riêng Với Gì Để Tránh Gây Hại Cho Sức Khỏe

Và đây 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 thêm một số nào đó trong chuỗi cội – sửa chữa số bởi một chuỗi trống rỗng (“”), và tiếp nối trừ đi chiều nhiều năm của chuỗi mà không có số đó từ tổng số Chiều lâu năm của chuỗi ban đầu. Chính vì đó là 1 trong những 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 mở ra 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ự trường đoản cú phía bên yêu cầu của chuỗi.

Công thức nhằm trích xuất văn bạn dạng (hàm LEFT). Bạn giám sát bao nhiêu ký tự văn bạn dạng chuỗi chứa bằng phương pháp 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 sử dụng hàm LEFT nhằm trả về nhiều 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 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 chấm dứt theo cách thông thường bằng phương pháp nhấn phím Enter.

Khi địa điểm của số đầu tiên được search thấy, bạn có thể tách văn bạn dạng và số bằng cách sử dụng những công thức LEFT và RIGHT rất dễ dàng (nhớ rằng một số trong những 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 đúng theo A2 là chuỗi ban đầu, và B2 là địa chỉ của số đầu tiên, như biểu thị trong hình dưới đây:


Để loại bỏ 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 giám sát và đo lường 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, tạo nên nó tìm từng số vào hằng số mảng bên trong bản gốc, cùng trả lại địa điểm của chúng. Bởi vì hằng số mảng đựng 10 chữ số, mảng công dụng cũng đựng 10 mục.

Hàm MIN đem mảng tác dụng và trả về giá chỉ trị nhỏ tuổi nhất, tương xứng với vị trí của số thứ nhất 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ố có thể với chuỗi ban đầu. Phương pháp này triển khai vai trò của IFERROR và có thể chấp nhận được chúng tôi né lỗi khi một vài nhất định trong hằng số mảng ko được tìm thấy vào chuỗi nguồn. Vào trường vừa lòng này, cách làm trả về vị trí “giả mạo” bởi chuỗi chiều dài từ là một ký từ bỏ trở lên. Điều này 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 ví như chuỗi cội không chứa bất kỳ số nào, như trong loại 7 hình làm việc trên.

Ví dụ: so với chuỗi “Dress 05” vào A2, mảng tác dụng là 7,10,11,12,13,8,15,16,17,18. Và đấy là cách shop chúng tôi có:

5 là ký kết tự sản phẩm 8 vào chuỗi gốc, với 0 là ký kết tự trang bị 7, kia là nguyên nhân tại sao mục trước tiên của mảng hiệu quả là “7”, với thứ sáu là “8”.Không bao 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 kết quả đại diện cho những vị trí của các chữ số khớp ứng trong chuỗi nối (A2 & “0123456789”).

Xem thêm: Cách Tạo Ảnh 3X4 Online Miễn Phí, Tốt Nhất Trên Android, Ios


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

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

Nếu các bạn đang bóc tách các ô nơi văn bản xuất hiện sau một số, bạn có thể trích xuất các 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ư như bí quyết mảng từ lấy ví dụ như trước, không tính bạn áp dụng hàm LEFT thay do 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 biện 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 thuở đầu và B2 là số trích xuất, như biểu lộ trong hình bên 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 yên cầu phải trích xuất toàn bộ các số xuất phát từ 1 chuỗi trong định dạng ‘number-text-number’, bạn cũng có thể sử dụng công thức dưới đây được gợi ý bởi 1 trong các 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)