Hàm tách lấy số trong excel

     

Trong nội dung bài viết này, gift4u.vn sẽ phân tích và lý giải cách bóc các ô trong 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 dấu phẩy, khoảng tầm trắng hoặc ngẫu nhiên dấu chia cách nào khác, và làm cố gắng nào để phân loại chuỗi thành văn phiên bản và số.

Bạn đang xem: Hàm tách lấy số trong excel


Làm thay nào để chia văn bạn dạng trong Excel bằng phương pháp sử dụng công thức:

Để bóc tách chuỗi trong Excel hay tách bóc chữ và số trong excel, chúng ta thường sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Thời điểm đầu, một số công thức có thể phức tạp, nhưng thực tế logic là khá đối kháng giản, và các ví dụ sau đây sẽ cung cấp cho 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 ngăn cách khác

Khi phân chia các ô vào Excel, việc đó là xác định vị trí của dấu ngăn cách 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 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 minh bạch chữ hoa chữ hay hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi chúng ta có vị trí của vệt phân cách, áp dụng 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 chăm chú 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 muốn chia 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 phương pháp sau trong B2, cùng sau đó xào nấu nó xuống cột:

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

Trong cách làm này, hàm search xác định vị trí của vết nối thứ nhất (“-“) trong chuỗi và tác dụng LEFT đã chiết toàn bộ các ký tự sót lại (bạn trừ 1 từ địa điểm của vệt nối chính vì bạn không muốn có vết nối).

*

Để trích xuất màu sắc (tất cả những ký trường đoản cú giữa những dấu gạch nối thứ hai và vật dụng 3), hãy nhập công thức sau trong C2, cùng 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ư chúng ta có thể biết, hàm MID tất 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 bản từ.Start_num – địa điểm của kí tự đầu tiên để trích xuất.Num_chars – số ký kết tự nhằm trích xuất.

Trong công thức trên, văn bản được trích ra trường đoản cú ô A2, với 2 đối số không giống được tính bằng phương pháp sử dụng 4 hàm tìm kiếm khác:

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

SEARCH (“-“, A2) + 1

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

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

Để trích xuất size (tất cả các ký tự sau vết nối thiết bị 3), hãy nhập phương pháp sau trong D2:

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

Trong công thức này, hàm LEN trả về tổng chiều lâu năm của chuỗi, từ bỏ đó chúng ta trừ đi địa chỉ của vết nối thiết bị hai. Sự khác biệt là số cam kết tự sau dấu nối máy hai với hàm RIGHT triết xuất chúng.

*
Trong một biện pháp tương tự, chúng ta cũng có thể phân phân chia cột bởi ngẫu nhiên kí tự như thế nào khác. Toàn bộ bạn đề nghị làm là sửa chữa “-” bởi ký tự phân cách bắt buộc, ví dụ như dấu biện pháp (“”), vết gạch chéo cánh (“/”), dấu 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 tương xứng với số ký kết tự trong vệt phân cách. Trong lấy ví dụ như này, nó là 1 trong dấu nối (1 ký tự). Nếu như dấu phân làn của bạn bao hàm 2 ký tự, ví dụ: lốt phẩy và khoảng tầm trắng, sau đó chỉ cung cấp dấu phẩy (“,”) mang lại hàm SEARCH, và sử dụng +2 với -2 thay vày +1 cùng -1.

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

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

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

Để bắt đầu, ko có giải pháp tổng quát mắng cho tất cả các chuỗi chữ số. Công thức nào để sử dụng dựa vào vào chủng loại chuỗi cố gắng thể. Dưới đây bạn sẽ tìm thấy phương pháp cho 3 kịch bản thường chạm mặt nhất.

Xem thêm: Tattoo Hình Xăm Quan Công Cưỡi Rồng, Ý Nghĩa Hình Xăm Quan Công

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

Giả sử chúng ta có một cột các chuỗi cùng với văn bản và số kết hợp, trong số ấy một số luôn luôn theo sau văn bản. Bạn có nhu cầu phá vỡ các chuỗi ban đầu để văn phiên bản và số lộ diện trong các ô riêng biệt biệt, như sau:

*

Để trích xuất các số, áp dụng công thức mảng sau đây, được hoàn 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, và C2 là số trích xuất, như diễn đạt trong hình dưới đây:

*
Công thức chuyể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, cách làm tìm kiếm mọi số rất có thể từ 0 mang lại 9 trong chuỗi nguồn, tính số lượng và trả về những ký từ từ ký kết tự cuối chuỗi ban đầu.

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

Trước tiên, chúng ta sử dụng những hàm LEN và SUBSTITUTE nhằm tìm ra số lần mở ra một số nào kia trong chuỗi gốc – sửa chữa thay thế số bằng một chuỗi trống rỗng (“”), và tiếp đến trừ đi chiều dài của chuỗi mà không tồn tại số đó từ tổng cộng Chiều lâu năm của chuỗi ban đầu. Bởi vì đó là 1 trong công thức mảng, làm việc này được triển khai trên từng 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 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ự trường đoản cú phía bên phải của chuỗi.

Công thức để trích xuất văn phiên bản (hàm LEFT). Bạn đo lường và thống kê bao nhiêu ký kết tự văn phiên bản chuỗi chứa bằng phương pháp trừ số chữ số chiết xuất (C2) tự chiều nhiều năm của chuỗi cội (A2). Sau đó, bạn thực hiện hàm LEFT để trả về nhiều ký tự từ đầu chuỗi.

Một chiến thuật khác (công thức không tồn tại mảng)

Giải pháp thay thế sửa chữa sẽ thực hiện công thức sau để xác xác định trí của số thứ nhất 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 đựng một hằng số mảng, đó là một trong công thức thông thường được hoàn thành theo bí quyết thông thường bằng phương pháp nhấn phím Enter.

Khi vị trí của số trước tiên được tìm kiếm thấy, bạn 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 cùng RIGHT rất đơn giản và dễ dàng (nhớ rằng một số 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 thích hợp A2 là chuỗi ban đầu, với B2 là địa điểm của số đầu tiên, như mô tả trong hình bên dưới đây:

*
Để thải trừ 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 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 & “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 tính toán vị trí của số lắp thêm 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ó tra cứu từng số trong hằng số mảng mặt trong phiên bản gốc, và 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 hiệu quả cũng chứa 10 mục.

Hàm MIN mang mảng công dụng và trả về giá bán trị nhỏ tuổi nhất, tương xứng với vị trí 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 trúc đặc biệt (A2 & “0123456789”) nhằm ghép mỗi số rất có thể với chuỗi ban đầu. Giải pháp này thực hiện vai trò của IFERROR và chất nhận được chúng tôi kiêng lỗi khi một vài nhất định vào hằng số mảng không được kiếm tìm thấy trong chuỗi nguồn. Trong trường phù hợp này, bí quyết trả về vị trí “giả mạo” bởi 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 phiên bản và hàm RIGHT trả về một chuỗi rỗng nếu chuỗi cội không chứa bất kỳ số nào, như trong loại 7 hình ngơi nghỉ trên.

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

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

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

Xem thêm: Máy Hàn Điện Tử Hồng Ký 200A Máy Hàn Que Điện Tử Inverter Hồng Ký Hk200A

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

Nếu các bạn đang tách các ô nơi văn bạn dạng xuất hiện nay sau một số, chúng ta có thể trích xuất những số với cách làm 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ựa như như bí quyết mảng từ lấy một ví dụ trước, kế bên bạn áp dụng hàm LEFT thay bởi RIGHT, bởi vì trong trường đúng theo 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 phương pháp trừ số chữ số từ bỏ 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ư biểu lộ trong hình dưới đây:

*

Ví dụ 3. Trích xuất chỉ số từ chuỗi số ‘số văn bản’

Nếu các bước của bạn đòi hỏi phải trích xuất tất cả các số từ một chuỗi trong format ‘number-text-number’, bạn có thể sử dụng công thức sau đây được gợi ý bởi một trong những những chuyên gia của MrExcel:

= SUMPRODUCT (MID (0 và 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 thích hợp A2 là chuỗi văn bản ban đầu.

*