Hàm tách chữ và số trong excel

Trong bài viết này, belyvn.com sẽ phân tích và lý giải cách bóc các ô vào Excel bởi công thức. Bạn sẽ học cá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à làm ráng nào để phân loại chuỗi thành văn bản và số.

Bạn đang xem: Hàm tách chữ và số trong excel

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

Để tách chuỗi trong Excel hay tách bóc chữ và số trong excel, các bạn 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 gian đầu, một vài công thức có thể phức tạp, nhưng thực tế logic là khá solo giản, và những ví dụ dưới đâ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, dấu hai chấm, lốt gạch chéo, lốt gạch ngang hoặc dấu ngăn cách khác

Khi phân chia những ô trong Excel, việc chính là xác định vị trí của dấu ngăn 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 rất 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 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 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 ứ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ử 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 muốn chia tách bóc cột thành 3 cột riêng biệt:

Để trích xuất tên mục (tất cả những ký từ bỏ trước dấu 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ông thức này, hàm search xác định vị 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ự còn lại (bạn trừ 1 từ địa chỉ của lố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ả những ký từ giữa những dấu gạch men nối thứ 2 và đồ vật 3), hãy nhập công thức sau trong C2, với sau đó xào nấu nó xuống các ô khác:

=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1

Như bạn cũng 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 bạn dạng – nơi để trích xuất văn phiên bản từ.Start_num – địa chỉ của kí tự đầu tiên để trích xuất.Num_chars – số ký tự nhằm trích xuất.

Trong công thức trên, văn bạn dạng được trích ra từ ô A2, cùng 2 đối số không giống đượ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à địa chỉ của lốt nối trước tiên +1:

SEARCH (“-“, A2) + 1

Số cam kết tự nhằm trích xuất (num_chars): sự khác biệt giữa vị trí của vệt nối lắp thêm 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 vết nối sản phẩm công nghệ 3), hãy nhập cách làm sau vào D2:

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

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

Trong một phương pháp tương tự, chúng ta cũng 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 nên làm là thay thế “-” bởi ký tự ngăn cách bắt buộc, ví dụ như dấu cách (“”), dấu gạch chéo cánh (“/”), vết hai chấm (“;”), dấu chấm phẩy (“;”) và vân vân.

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

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

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

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

Để bắt đầu, không có chiến thuật tổng quát tháo 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 mẫu chuỗi nạm thể. Bên dưới đây bạn sẽ tìm thấy công thức cho 3 kịch phiên bản thường chạm mặt nhất.

Xem thêm: Công Thức Căn Bậc 2 Trong Excel Với Hàm Sqrt, Cách Viết Căn Bậc 2 Trong Excel

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

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

Công thức chuyển động như cụ nào:

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

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

Trước tiên, các bạn sử dụng những hàm LEN cùng 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 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ố đó từ toàn bô Chiều dài của chuỗi ban đầu. Bởi vì đó là 1 trong công thức mảng, thao tác này được triển khai 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 toàn bộ các chữ số trong chuỗi nguồn.Cuối cùng, hàm RIGHT trả về những ký tự từ phía bên đề nghị 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 tính toán bao nhiêu cam kết tự văn phiên bản chuỗi chứa bằng cách trừ số chữ số tinh chiết (C2) từ bỏ chiều nhiều năm của chuỗi cội (A2). Sau đó, bạn sử dụng hàm LEFT nhằm trả về các ký tự từ trên đầu chuỗi.

Một chiến thuật khác (công thức không có 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 định vị trí của số thứ nhất 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 đự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 địa chỉ của số đầu tiên được tìm kiếm thấy, chúng ta cũng có thể tách văn phiên bản và số bằng cách sử dụng những công thức LEFT và RIGHT rất dễ dàng và đơn giản (nhớ rằng một trong những 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 hợp A2 là chuỗi ban đầu, với B2 là vị trí của số đầu tiên, như mô tả trong hình bên dưới đây:

Để đào thải cột helper giữ địa điểm số bắt đầu, chúng ta 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 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 giám sát vị trí của số máy nhất

Bạn cung cấp 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, với trả lại địa điểm của chúng. Chính 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 lấy mảng hiệu quả và trả về giá chỉ trị nhỏ dại nhất, tương ứ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 mỗi số hoàn toàn có thể với chuỗi ban đầu. Cách này tiến hành vai trò của IFERROR và cho phép chúng tôi kị lỗi khi một trong những nhất định vào hằng số mảng ko được tìm thấy vào chuỗi nguồn. Vào trường hợp này, công thức trả về địa chỉ “giả mạo” bằng chuỗi chiều dài từ một 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 dạng 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 bất kỳ số nào, như trong dòng 7 hình làm việc trên.

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

5 là ký tự thiết bị 8 trong chuỗi gốc, với 0 là cam 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 kết quả là “7”, và thứ sáu là “8”.Không tất cả mục nào không giống của hằng số mảng được search thấy trong A2, và vì thế 8 phần khác của mảng công dụng đạ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á bán trị nhỏ dại nhất trong mảng kết quả, bởi 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 phiên bản ban đầu.

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

Nếu bạn đang bóc tách các ô nơi văn phiên bản xuất hiện sau một số, chúng ta 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 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ư bí quyết mảng từ ví dụ như trước, ngoại trừ bạn sử dụng hàm LEFT thay bởi RIGHT, cũng chính vì trong trường phù hợp này số luôn xuất hiện ở 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 phiên bản bằng giải pháp trừ số chữ số từ 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 thuở đầ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ố trường đoản cú 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 toàn bộ các số xuất phát điểm từ một chuỗi trong định hình ‘number-text-number’, chúng ta cũng có thể sử dụng công thức dưới đây được nhắc nhở bởi một trong những chuyên viên của MrExcel:

= SUMPRODUCT (MID (0 và 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)

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