TÁCH SỐ TRONG CHUỖI

Trong bài viết này, timnhaviet.vn sẽ lý giải 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 phiên bản dựa theo dấu phẩy, khoảng tầm trắng hoặc bất kỳ dấu phân làn nào khác, và làm cố nào để phân loại chuỗi thành văn bản và số.Bạn đang xem: Hàm tách số thoát ra khỏi chữ vào excel

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

Để bóc tách chuỗi trong Excel hay bóc tách chữ với số trong excel, các bạn thường sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Dịp đầu, một vài công thức rất có thể phức tạp, nhưng thực tiễn logic là khá đơn giản, và các ví dụ dưới đây sẽ cung cấp cho mình một số đầu mối.

Bạn đang xem: Tách số trong chuỗi

Tách chuỗi bằng dấu phẩy, dấu hai chấm, dấu gạch chéo, dấu gạch ngang hoặc dấu phân cách khác


*

*

Khi phân chia những ô vào Excel, việc chính là xác xác định trí của dấu ngă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 có thể được thực hiện bằng phương pháp 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 bạn có vị trí 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 tương xứng của chuỗi văn bản.

Để hiểu rõ hơn, hãy xem xét ví dụ sau đây:

Giả sử 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 rẽ biệt:


*

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


*

Để trích xuất color (tất cả các ký từ bỏ giữa những dấu gạch ốp nối thứ 2 và sản phẩm công nghệ 3), hãy nhập phương pháp sau trong C2, và 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 – chỗ để trích xuất văn bản từ.Start_num – địa điểm của kí tự trước tiên để trích xuất.Num_chars – số ký tự nhằm trích xuất.

Trong bí quyết trên, văn phiên bản được trích ra từ bỏ ô A2, với 2 đối số khác được tính bằng cách sử dụng 4 hàm tìm kiếm 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ý kết tự để trích xuất (num_chars): sự khác biệt giữa địa điểm của dấu nối thứ hai với dấu nối đầu tiên, trừ đi 1:

SEARCH (“-“, A2, tìm kiếm (“-“, A2) +1) – search (“-“, A2) -1

Để trích xuất kích thước (tất cả những ký từ sau lốt nối máy 3), hãy nhập bí quyết sau vào D2:

= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, 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, trường đoản cú đó bạn trừ đi địa điểm 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 dấu nối vật dụng hai cùng hàm RIGHT chiết xuất chúng.


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

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

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

Để phân chia văn bạn dạng bằng khoảng trắng, hãy sử dụng các công thức giống như như cách làm được minh họa trong lấy ví dụ như trước. Sự khác hoàn toàn duy độc nhất vô nhị là chúng ta cần tính năng CHAR để cung ứng cho ký kết tự ngắt dòng vì các bạn không thể gõ thẳng vào công thức. Trả sử, các ô mà bạn muốn chia nhỏ trông tương tự như như sau:


Lấy công thức từ lấy ví dụ trước và nắm dấu gạch nối (“-“) bằng CHAR (10) trong đó 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 cố kỉnh nào để phân chia văn bạn dạng và số trong Excel:

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

Giả sử bạn có một cột những chuỗi với văn bạn dạng 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 muốn phá vỡ những chuỗi thuở đầu để văn bản và số mở ra trong các ô riêng biệt biệt, như sau:


Để trích xuất những số, sử dụng công thức mảng 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 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, cùng C2 là số trích xuất, như biểu thị trong hình dưới đây:


Công thức vận động như nạm nào:

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bản, bí quyết tìm kiếm mọi số có thể từ 0 mang lại 9 vào chuỗi nguồn, tính số lượng và trả về các ký từ từ ký tự cuối chuỗi ban đầu.

Xem thêm: Ý Nghĩa Cây Bắp Cải Phong Thủy Hợp Tuổi Nào, Bắp Cải Phong Thủy

Và đây là công thức chi tiết phân rã:

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

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

Giải pháp thay thế sửa chữa sẽ sử dụng công thức sau nhằm 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ù bí quyết cũng cất một hằng số mảng, đó là một trong những công thức bình thường được xong xuôi theo biện pháp thông thường bằng cách nhấn phím Enter.

Khi địa điểm của số đầu tiên được tìm kiếm thấy, bạn có thể tách văn bản và số bằng cách sử dụng các công thức LEFT cùng RIGHT rất đơn giản và dễ dàng (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 phù hợp A2 là chuỗi ban đầu, cùng B2 là địa điểm của số đầu tiên, như mô tả trong hình bên 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ới 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ố trang bị 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, làm cho nó tìm kiếm từng số vào hằng số mảng bên trong phiên bản gốc, với trả lại địa điểm của chúng. Bởi vì hằng số mảng cất 10 chữ số, mảng kết quả cũng cất 10 mục.

Hàm MIN lấy mảng công dụng và trả về giá trị bé dại nhất, tương ứng với địa chỉ của số đầu tiên trong chuỗi ban đầu.

Ngoài ra, chúng tôi sử dụng một cấu trúc đặc biệt (A2 và “0123456789”) nhằm ghép mỗi số hoàn toàn có thể với chuỗi ban đầu. Phương pháp này thực hiện 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 kiếm tìm thấy trong chuỗi nguồn. Trong trường phù hợp này, phương pháp trả về địa điểm “giả mạo” bằng chuỗi chiều dài từ là 1 ký từ trở lên. Điều này chất nhận được 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 chuỗi nơi bắt đầu không chứa ngẫu nhiên số nào, như trong mẫu 7 hình ở trên.

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


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

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

Nếu bạn đang tách các ô địa điểm văn phiên bản xuất hiện tại sau một số, bạn cũng có thể trích xuất những số với phương pháp 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ư cách làm mảng từ ví dụ như trước, xung quanh bạn thực hiện hàm LEFT thay bởi RIGHT, chính vì trong trường vừa lòng này số luôn lộ diện ở phía phía bên trái của chuỗi. Một khi bạn đã sở hữu các bé số, trích xuất văn phiên bản 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 những công thức trên, A2 là chuỗi ban đầu và B2 là số trích xuất, như biểu thị 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 đòi hỏi phải trích xuất tất cả các số xuất phát điểm từ 1 chuỗi trong format ‘number-text-number’, chúng ta cũng có thể sử dụng công thức dưới đây được gợi nhắc bởi một trong những 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:” và LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)

Leave a Reply

Your email address will not be published. Required fields are marked *