LỌC SỐ TRONG CHUỖI EXCEL

Trong nội dung bài viết này, choigame.me đang giải thích biện pháp tách các ô vào Excel bởi cách làm. Quý Khách đã học giải pháp tách văn bản dựa trên dấu phẩy, khoảng tầm Trắng hoặc bất kỳ vết phân làn như thế nào không giống, cùng làm cho thế làm sao để phân chia chuỗi thành vnạp năng lượng bạn dạng cùng số.

Bạn đang xem: Lọc số trong chuỗi excel


Làm vậy nào để chia văn uống bản vào Excel bằng cách sử dụng công thức:

Để tách chuỗi trong Excel hay bóc tách chữ và số vào excel, chúng ta thường xuyên áp dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. lúc đầu, một số phương pháp rất có thể phức hợp, tuy nhiên thực tế xúc tích là khá đơn giản và dễ dàng, với các ví dụ tiếp sau đây đang cung cấp cho bạn một số mối manh.

Tách chuỗi bởi lốt phẩy, vệt nhì chnóng, lốt gạch men chéo cánh, lốt gạch ốp ngang hoặc che dấu phân cách khác

lúc phân chia các ô trong Excel, câu hỏi chính là xác xác định trí của vết ngăn cách trong chuỗi vnạp năng lượng phiên bản. Tùy thuộc vào quá trình của bạn, điều đó hoàn toàn có thể được triển khai bằng phương pháp áp dụng hàm Search ko rõ ràng chữ hoa chữ thường xuyên hoặc hàm Find tất cả phân biệt chữ hoa chữ thường xuyên. Một khi chúng ta tất cả địa chỉ của lốt ngăn cách, sử dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi văn phiên bản.

Để làm rõ rộng, hãy chú ý ví dụ sau đây:

Giả sử các bạn tất cả một list các SKU của mẫu mã Loại-Màu-Kích thước, cùng bạn muốn phân tách tách cột thành 3 cột riêng biệt biệt:

*


*

*

Để trích xuất thương hiệu mục (toàn bộ các ký kết từ bỏ trước dấu nối đầu tiên), cyếu công thức sau trong B2, và tiếp đến xào luộc nó xuống cột:

= LEFT (A2, tìm kiếm (“-“, A2,1) -1)

Trong cách làm này, hàm Search xác định vị trí của vết nối đầu tiên (“-“) trong chuỗi với tính năng LEFT đang tách toàn bộ các ký kết từ bỏ sót lại (chúng ta trừ 1 trường đoản cú vị trí của vệt nối chính vì các bạn không thích bao gồm dấu nối).

*

Để trích xuất màu sắc (tất cả những ký trường đoản cú thân những dấu gạch ốp nối thứ hai và vật dụng 3), hãy nhập phương pháp sau vào C2, và kế tiếp xào nấu nó xuống các ô khác:

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

*

Nlỗi chúng ta cũng có thể biết, hàm MID có cú pháp sau:

MID (văn bạn dạng, start_num, num_chars)

Nơi:

Văn phiên bản – địa điểm để trích xuất văn uống phiên bản trường đoản cú.Start_num – vị trí của kí từ bỏ 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 được trích ra từ bỏ ô A2, và 2 đối số không giống được xem bằng phương pháp áp dụng 4 hàm SEARCH khác:

Số bước đầu (start_num) là địa điểm của vết nối thứ nhất +1:

SEARCH (“-“, A2) + 1

Số cam kết từ nhằm trích xuất (num_chars): sự khác biệt thân vị trí của vệt nối trang bị hai với lốt nối trước tiên, trừ đi 1:

SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1

Để trích xuất kích cỡ (toàn bộ các cam kết tự sau lốt nối vật dụng 3), hãy nhập bí quyết sau vào D2:

= RIGHT (A2, LEN (A2) – SEARCH (“-“, A2, SEARCH (“-“, A2) + 1))

Trong phương pháp này, hàm LEN trả về tổng chiều lâu năm của chuỗi, tự kia các bạn trừ đi địa chỉ của vệt nối sản phẩm công nghệ nhị. Sự biệt lập là số ký kết tự sau dấu nối trang bị nhì và hàm RIGHT tinh chiết chúng.

*
Trong một cách tương tự, chúng ta có thể phân chia cột vì ngẫu nhiên kí trường đoản cú như thế nào không giống. Tất cả chúng ta nên làm là sửa chữa thay thế “-” bởi cam kết từ phân làn cần, ví như dấu bí quyết (“”), vệt gạch chéo cánh (“/”), vệt nhị chấm (“;”), dấu chấm phẩy (“;”) cùng vân vân.

Mẹo. Trong các cách làm bên trên, +1 cùng -1 tương xứng cùng với số ký trường đoản cú vào vệt ngăn cách. Trong ví dụ này, nó là 1 trong những vết nối (1 ký kết tự). Nếu lốt phân làn của doanh nghiệp bao gồm 2 cam kết trường đoản cú, ví dụ: Dấu phẩy với khoảng tầm white, tiếp nối chỉ cung ứng dấu phẩy (“,”) mang đến hàm SEARCH, với sử dụng +2 và -2 gắng bởi vì +1 với -1.

Làm núm làm sao nhằm phân chia chuỗi bằng cách ngắt dòng trong Excel:

Để chia văn uống bạn dạng bằng khoảng chừng Trắng, hãy áp dụng những công thức tựa như nlỗi phương pháp được minc họa vào ví dụ trước. Sự khác biệt độc nhất là bạn cần công dụng CHAR nhằm cung cấp cho cam kết từ bỏ ngắt cái vày chúng ta chẳng thể gõ thẳng vào công thức. Giả sử, những ô cơ mà bạn muốn phân tách nhỏ trông tựa như nhỏng sau:

*
Lấy cách làm từ bỏ ví dụ trước cùng nạm vết gạch nối (“-“) bởi CHAR (10) trong các số đó 10 là mã ASCII cho mẫu cấp cho dữ liệu.

Để trích xuất thương hiệu phương diện 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 như thế nào để phân loại văn uống bản và số vào Excel:

Để bước đầu, không có giải pháp tổng thể đến toàn bộ những chuỗi chữ số. Công thức nào để thực hiện nhờ vào vào mẫu mã chuỗi cụ thể. Dưới phía trên bạn sẽ search thấy bí quyết mang lại 3 kịch bạn dạng hay chạm chán độc nhất.

Xem thêm: Hướng Dẫn Cách Bật Nút Home Trên Iphone, Cách Bật Nút Home Ảo Trên Ios 13 Và Ios 14

lấy một ví dụ 1. Chia chuỗi của các loại ‘vnạp năng lượng bản + số’

Giả sử chúng ta gồm một cột những chuỗi cùng với vnạp năng lượng bạn dạng cùng số phối hợp, trong những số ấy một trong những luôn luôn luôn luôn theo sau vnạp năng lượng bạn dạng. Quý khách hàng ý muốn phá tan vỡ các chuỗi ban sơ để vnạp năng lượng phiên bản và số lộ diện trong những ô riêng biệt, nlỗi sau:

*

Để trích xuất những số, thực hiện bí quyết mảng tiếp sau đây, được hoàn thành bằng cách dìm 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 vnạp năng lượng phiên bản, sử dụng:

= LEFT (A2, LEN (A2) -LEN (C2))

Trường thích hợp A2 là chuỗi lúc đầu, và C2 là số trích xuất, nhỏng thể hiện vào hình dưới đây:

*
Công thức chuyển động như vậy nào:

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ phiên bản, công thức tìm kiếm tìm gần như số rất có thể trường đoản cú 0 mang đến 9 trong chuỗi mối cung cấp, tính số lượng cùng trả về nhiều cam kết từ tự cam kết tự cuối chuỗi ban sơ.

Và đấy là cách làm chi tiết phân rã:

Thứ nhất, bạn thực hiện các hàm LEN và SUBSTITUTE để tìm ra mốc giới hạn xuất hiện một vài như thế nào đó vào chuỗi cội – sửa chữa thay thế số bằng một chuỗi trống rỗng (“”), và kế tiếp trừ đi chiều nhiều năm của chuỗi mà không tồn tại số đó trường đoản cú tổng số Chiều lâu năm của chuỗi ban đầu. Bởi vì chưng đó là 1 công thức mảng, thao tác làm việc này được thực hiện bên 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 tất cả các lần mở ra của tất cả những chữ số vào chuỗi mối cung cấp.Cuối thuộc, hàm RIGHT trả về các cam kết tự trường đoản cú phía bên buộc phải của chuỗi.

Công thức nhằm trích xuất vnạp năng lượng phiên bản (hàm LEFT). Bạn tính toán thù bao nhiêu cam kết từ bỏ văn bản chuỗi cất bằng cách trừ số chữ số triết xuất (C2) tự chiều lâu năm của chuỗi nơi bắt đầu (A2). Sau kia, chúng ta á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 (bí quyết không có mảng)

Giải pháp sửa chữa thay thế vẫn áp dụng bí quyết sau nhằm xác định vị trí của số trước tiên vào chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 & “0123456789”))

Mặc dù phương pháp cũng đựng một hằng số mảng, đó là 1 phương pháp thông thường được chấm dứt Theo phong cách thường thì bằng cách dấn phím Enter.

lúc vị trí của số thứ nhất được search thấy, chúng ta cũng có thể tách bóc văn uống phiên bản cùng số bằng cách áp dụng các cách làm LEFT và RIGHT cực kỳ dễ dàng (hãy nhớ là một số trong những luôn lộ diện sau văn uống bản):

Để trích xuất văn uống 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 sơ, cùng B2 là vị trí của số thứ nhất, nlỗi diễn đạt vào hình dưới đây:

*
Để thải trừ cột helper giữ lại địa điểm số bước đầ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 uống 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 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 tính toán vị trí của số đồ vật nhất

quý khách hàng hỗ trợ hằng số mảng 0,1,2,3,4,5,6,7,8,9 trong đối số find_text của hàm SEARCH, khiến cho nó kiếm tìm từng số trong hằng số mảng phía bên trong bạn dạng gốc, và trả lại địa điểm của bọn chúng. Bởi vì chưng hằng số mảng chứa 10 chữ số, mảng tác dụng cũng cất 10 mục.

Hàm MIN đem mảng kết quả và trả về quý giá nhỏ độc nhất vô nhị, tương ứng với địa điểm của số đầu tiên vào chuỗi lúc đầu.

Ngoài ra, công ty chúng tôi áp dụng một cấu trúc đặc trưng (A2 & “0123456789”) nhằm ghxay từng số hoàn toàn có thể với chuỗi thuở đầu. Cách này tiến hành phương châm của IFERROR cùng được cho phép Shop chúng tôi tránh lỗi lúc một số nhất mực vào hằng số mảng không được tìm kiếm thấy trong chuỗi nguồn. Trong ngôi trường đúng theo này, công thức trả về địa chỉ “trả mạo” bởi chuỗi chiều dài từ 1 ký trường đoản cú trngơi nghỉ lên. Điều này có thể chấp nhận được hàm LEFT trích xuất vnạp năng lượng bạn dạng với hàm RIGHT trả về một chuỗi rỗng trường hợp chuỗi cội không chứa bất kỳ số làm sao, nhỏng trong loại 7 hình sinh hoạt bên 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à đây là cách chúng tôi có:

5 là cam kết trường đoản cú máy 8 vào chuỗi cội, và 0 là cam kết từ trang bị 7, chính là nguyên do tại vì sao mục trước tiên của mảng tác dụng là “7”, và máy sáu là “8”.Không gồm mục như thế nào không giống của hằng số mảng được search thấy vào A2, với cho nên 8 phần không giống của mảng công dụng thay mặt cho những địa chỉ của những chữ số tương xứng vào chuỗi nối (A2 & “0123456789”).

*
Và cũng chính vì 7 là quý giá nhỏ tuổi duy nhất vào mảng công dụng, do hàm MIN trả về, cùng bọn họ nhận ra địa chỉ của số đầu tiên (0) vào chuỗi văn bản ban đầu.

lấy ví dụ 2. Chia chuỗi của một số loại ‘số + vnạp năng lượng bản’

Nếu nhiều người đang bóc tách các ô vị trí vnạp năng lượng bản lộ diện sau một vài, chúng ta có thể trích xuất các số cùng với cách làm mảng này (xong xuôi 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ương tự như công thức mảng tự ví dụ trước, ngoại trừ chúng ta áp dụng hàm LEFT gắng do RIGHT, cũng chính vì vào trường hợp này số luôn xuất hiện thêm sinh hoạt phía phía bên trái của chuỗi. Một Khi bạn đã sở hữu những con số, trích xuất văn uống bạn dạng bằng cách trừ số chữ số trường đoản cú tổng chiều lâu năm của chuỗi gốc:

= RIGHT(A2, LEN (A2) -LEN (B2))

Trong các bí quyết bên trên, A2 là chuỗi ban đầu với B2 là số trích xuất, như mô tả vào hình dưới đây:

*

lấy một ví dụ 3. Trích xuất chỉ số từ chuỗi số ‘số văn uống bản’

Nếu quá trình của doanh nghiệp đòi hỏi buộc phải trích xuất toàn bộ những số từ một chuỗi trong format ‘number-text-number’, chúng ta có thể áp dụng công thức dưới đây được gợi nhắc vì một trong số những Chuyên Viên của MrExcel:

= SUMPRODUCT (MID (0 và A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)) * ROW (TRỰC TIẾPhường (“1:” & 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 dạng thuở đầu.

*