Trong nội dung bài viết này, blog.hocexcel.online sẽ giải thích cách tách các ô vào 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 lốt phẩy, khoảng chừng trắng hoặc ngẫu nhiên dấu phân cách nào khác, và làm nắm 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 cầm 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ữ và số vào excel, các bạn thường áp dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Thời gian đầ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á 1-1 giản, và các ví dụ tiếp sau đây sẽ cung cấp cho mình một số đầu mối.Tách chuỗi bởi dấu phẩy, vệt hai chấm, vết gạch chéo, vệt gạch ngang hoặc dấu chia cách khác
Khi phân chia các ô 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 ở trong vào công việ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 rõ ràng chữ hoa chữ thường hoặc hàm Find bao gồm phân biệt chữ hoa chữ thường. Một khi chúng ta 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 xứng của chuỗi văn bản.
Để làm rõ hơn, hãy xem xét 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 tách cột thành 3 cột riêng biệt 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 cách làm sau trong B2, và sau đó xào luộc nó xuống cột:
= LEFT (A2, search (“-“, 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 trước tiên (“-“) vào chuỗi và công dụng LEFT đang chiết toàn bộ các cam kết tự còn lại (bạn trừ 1 từ địa điểm của vết nối bởi vì bạn không muốn có vết nối).
=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1
Như chúng ta 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 phiên bản từ.Start_num – địa chỉ của kí tự trước tiên để trích xuất.Num_chars – số cam kết tự nhằm trích xuất.Trong công thức trên, văn bản được trích ra từ ô A2, và 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ước đầu (start_num) là địa điểm của dấu nối đầu tiên +1:SEARCH (“-“, A2) + 1
Số cam 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 sản phẩm công nghệ hai và 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ả những ký trường đoản cú sau dấu nối lắp thêm 3), hãy nhập công thức sau trong 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 dài của chuỗi, tự đó các bạn trừ đi địa chỉ của vết nối thứ hai. Sự biệt lập là số ký tự sau vết nối sản phẩm hai và hàm RIGHT tinh chiết chúng.
Mẹo. Trong các công thức trên, +1 với -1 tương xứng với số ký tự trong vệt phân cách. Trong ví dụ như này, nó là một trong những dấu nối (1 ký kết tự). Giả dụ dấu chia cách của bạn bao gồm 2 ký tự, ví dụ: dấu phẩy và khoảng trắng, tiếp đến chỉ hỗ trợ dấu phẩy (“,”) cho hàm SEARCH, và sử dụng +2 cùng -2 thay bởi +1 cùng -1.
Làm rứa nào để phân chia chuỗi bằng cách ngắt loại trong Excel:
Để phân chia văn bản bằng khoảng trắng, hãy sử dụng các công thức tựa như như phương pháp được minh họa trong lấy ví dụ như trước. Sự khác hoàn toàn duy tuyệt nhất là các bạn cần tính năng CHAR để hỗ trợ cho cam kết tự ngắt loại vì chúng ta 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ư sau:
=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 ráng nào để phân chia văn phiên bản và số vào 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ố. Cách làm nào nhằm sử dụng phụ thuộc vào vào mẫu mã chuỗi ráng thể. Dưới đây bạn sẽ tìm thấy công thức cho 3 kịch bản thường gặp nhất.
Ví dụ 1. Phân tách chuỗi của các loại ‘văn bạn dạng + số’
Giả sử các bạn có một cột những chuỗi với văn phiên bản và số kết hợp, trong đó một số luôn luôn luôn theo sau văn bản. Bạn có nhu cầu phá vỡ các chuỗi thuở đầu để văn bản và số mở ra trong những ô riêng biệt, như sau:
Để trích xuất những số, áp 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à C2 là số trích xuất, như trình bày trong hình dưới đây:
Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bản, phương pháp tìm kiếm hồ hết số hoàn toàn có thể từ 0 cho 9 trong chuỗi nguồn, tính số lượng và trả về các ký từ từ cam kết tự cuối chuỗi ban đầu.
Và đấy là công thức cụ thể phân rã:
Trước tiên, chúng ta sử dụng các hàm LEN với SUBSTITUTE để tìm ra số lần mở ra một số nào kia trong chuỗi nơi bắt đầu – thay thế sửa chữa số bởi một chuỗi 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. Cũng chính vì đó là một 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 xuất hiện của tất cả 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 phải của chuỗi.Công thức để trích xuất văn bản (hàm LEFT). Bạn đo lường bao nhiêu cam kết tự văn bản chuỗi chứa bằng cách trừ số chữ số triết xuất (C2) trường đoản cú chiều lâ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 phương án 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ẽ áp dụng 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 & “0123456789”))
Mặc dù bí quyết cũng đựng một hằng số mảng, đó là một trong công thức thông thường được kết thúc theo biện pháp thông thường bằng phương pháp nhấn phím Enter.
Khi địa chỉ của số thứ nhất được tìm kiếm thấy, chúng ta có thể tách văn bản và số bằng phương pháp sử dụng các công thức LEFT với 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 vừa lòng A2 là chuỗi ban đầu, với B2 là vị trí của số đầu tiên, như trình bày trong hình bên dưới đây:
= 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à đ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, làm cho nó tra cứu từng số vào hằng số mảng bên trong bản gốc, và trả lại địa điểm của chúng. Chính vì hằng số mảng cất 10 chữ số, mảng hiệu quả cũng cất 10 mục.
Hàm MIN lấy mảng kết quả và trả về giá chỉ trị bé dại nhất, khớp ứng với địa chỉ của số thứ nhất trong chuỗi ban đầu.
Ngoài ra, cửa hàng chúng tôi sử dụng một cấu tạo đặc biệt (A2 và “0123456789”) nhằm ghép từng số rất 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 kiêng lỗi khi một số nhất định vào hằng số mảng ko được tìm kiếm thấy vào chuỗi nguồn. Trong trường hợp này, bí quyết trả về vị trí “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 và hàm RIGHT trả về một chuỗi rỗng ví như chuỗi gốc không chứa ngẫu nhiên 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” trong A2, mảng tác 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ý kết tự lắp thêm 8 trong chuỗi gốc, và 0 là cam kết tự thứ 7, đó là vì sao tại sao mục đầu tiên của mảng hiệu quả là “7”, cùng 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 tìm kiếm thấy trong A2, và cho nên vì vậy 8 phần không giống của mảng hiệu quả đại diện cho những vị trí của các chữ số tương ứng trong chuỗi nối (A2 & “0123456789”).
Ví dụ 2. Phân chia chuỗi của loại ‘số + văn bản’
Nếu chúng ta đang bóc tách các ô khu vực văn bạn dạng xuất hiện sau một số, chúng ta 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 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ư phương pháp mảng từ ví dụ trước, ko kể bạn thực hiện hàm LEFT thay vị RIGHT, chính vì trong trường thích hợp này số luôn xuất hiện ở phía bên trái của chuỗi. Một khi bạn đã có các nhỏ số, trích xuất văn bản bằng giải pháp trừ số chữ số từ bỏ tổng chiều nhiề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 sơ và B2 là số trích xuất, như biểu đạt 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ông việc của bạn đòi hỏi phải trích xuất tất cả các số xuất phát từ 1 chuỗi trong định dạng ‘number-text-number’, chúng ta có thể sử dụng công thức sau đây được nhắc nhở bởi 1 trong những những chuyên viên của Mr
Excel:
= 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)
Trường vừa lòng A2 là chuỗi văn bản ban đầu.
Sử dụng hàm bóc tách số ra khỏi chuỗi vào Excel giúp bạn quản lý dữ liệu, ID của người tiêu dùng một giải pháp dễ dàng. Vậy hàm này được sử dụng như vậy nào? Mời các bạn đọc xem thêm các nội dung cụ thể thông qua bài xích viết.
1. Hàm bóc tách số thoát ra khỏi chuỗi vào Excel
Ví dụ: mang đến bảng tài liệu sau, yêu thương cầu bóc mã số sinh viên cùng tên sinh viên thành những dữ liệu riêng biệt.
- cách 1: Áp dụng công thức =FIND(0;1;2;3;4;5;6;7;8;9;C4). Hàm này chúng ra đang tìm từ số 0 - 9 trong chuỗi C4.
Tách số thoát ra khỏi chuỗi trong Excel - Hình 1
- bước 2: Kéo thả loài chuột để hiển thị kết quả ở những ô phíasau.
Tách số thoát khỏi chuỗi trong Excel - Hình 2
- cách 3: Đối với số đông ô tính hiện tại lỗi VALUE, chúng ta cũng có thể nhấn đúp chuột vào ô trước tiên và thêm &"0123456789" vào cuối hàm. Làm việc này giúp bạn nối thêm chuỗi tự 0-9. Như vậy, bạn sẽ có cách làm như sau: =FIND(1;2;3;4;5;6;7;8;9;C4&"0123456789")
Tách số ra khỏi chuỗi vào Excel - Hình 3
- cách 4: Thêm hàm Min ở phía ko kể và thừa nhận Enter. lúc này kết trái trả về là địa điểm tìm thấy cùng là vị trí nhỏ tuổi nhất. Có nghĩa là trong chuỗi C4 trường đoản cú 0-9 để kiếm được vị trí thứ nhất và trả về.
Tách số thoát khỏi chuỗi trong Excel - Hình 4
- cách 5: Để xem phương pháp chạy của hàm này, bạn chọn vào ô vừa tìm địa điểm số với nhấn chọn vào Formulas -> chọn Evaluate Arrows.
Tách số thoát ra khỏi chuỗi vào Excel - Hình 5
2. Hàm Right bóc tách số thoát khỏi chuỗi vào Excel
Ngoài ra, để bóc số thoát khỏi chuỗi trong Excel, bạn vận dụng hàm RIGHT.
Công thức:=RIGHT(chuỗi bạn phải tách; LEN(chuỗi bạn cần tách) - Vị trí bắt đầu số + 1)
Ví dụ minh họa: dùng hàm RIGHT để tách bóc số ra khỏi chuỗi trong Excel
- cách 1: Áp dụng công thức: =RIGHT(C4;LEN(C4)-H4+1).
Tách số ra khỏi chuỗi vào Excel - Hình 6
Khi áp dụng vào lấy ví dụ trên, ta được hàm như sau:
=RIGHT(chuỗi nên tách;LEN(chuỗi cần tách);+1-MIN(FIND(0;1;2;3;4;5;6;7;8;9,chuỗicần bóc & "0123456789"))).
Tách số thoát khỏi chuỗi trong Excel - Hình 7
3. Tách bóc số thoát khỏi chuỗi vào Excel bởi CODE VBA
Ví dụ: mang lại bảng tài liệu sau, yêu thương cầu tách số chứng minh thư của từng người thoát ra khỏi chuỗi vào cột thông tin.
Tách số thoát khỏi chuỗi bằng CODE VBA - Hình 8
- cách 1: open sổ Microsoft Visual Basic for Applications bằng tổ hợp phím alternative text + F11. Tiếp theo chọn Insert -> chọn Module.
Tách số thoát ra khỏi chuỗi bởi CODE VBA - Hình 9
- cách 2: coppy toàn bộ đoạn code sau bằng tổ hợp phím CTRL + C.
- bước 3: Dán đoạn code bằng phím Ctrl + V vào cửa sổ Module.
Xem thêm: Cậu Ấm Đối Đầu Cô Chiêu - Cuộc Sống Xa Hoa Của Những Cậu Ấm Cô Chiêu
Tách số thoát khỏi chuỗi bằng CODE VBA - Hình 10
- bước 4: trở lại trang tính, kế tiếp áp dụng hàm: = Extract
Number(B2)
- bước 5: coppy công thức cho các đối tượng người sử dụng trong bảng và nhận được công dụng như sau:
Tách số thoát ra khỏi chuỗi bởi CODE VBA - Hình 11
4. Tổng kết
Qua nội dung bài viết trên, cya.edu.vn đã thuộc bạn mày mò về hàm bóc tách số thoát khỏi chuỗi trong Excel. Ngoài những kiến thức trên, bạn đọc rất có thể tham khảo các khóa huấn luyện tin học văn phòng và công sở Online trên cya.edu.vn để cải thiện kỹ năng Excel của mình.