Ký Tự * Trong Excel

     

Trong nội dung bài viết này, bossvietnam.vn sẽ lý giải cách bóc các ô trong Excel bằng công thức. Các bạn sẽ học cách bóc văn phiên bản dựa theo vệt phẩy, khoảng chừng trắng hoặc bất kỳ dấu phân làn nào khác, cùng làm gắng nào để phân chia chuỗi thành văn phiên bản và số.Bạn đã xem: Cách bóc tách ký tự vào excel

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

Để bóc chuỗi trong Excel hay tách bóc chữ cùng số trong excel, bạn thường thực hiện hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Cơ hội đầu, một vài 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à những ví dụ tiếp sau đây sẽ cung cấp cho bạn một số đầu mối.

Bạn đang xem: Ký tự * trong excel

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 phân làn khác

Khi phân chia những ô trong Excel, việc chính là xác định vị trí của dấu phâ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 phương pháp sử dụng hàm tìm kiếm không phân minh chữ hoa chữ thường xuyên hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi chúng ta có địa điểm của lốt phân cách, áp dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi văn bản.

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

Giả sử các 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 muốn chia bóc tách cột thành 3 cột riêng rẽ biệt:


*

*

*

*

*

Như bạn 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 phiên bản – nơi để trích xuất vă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ự để trích xuất.

Trong bí quyết trên, văn bạn dạng được trích ra từ ô 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ố bắt đầu (start_num) là vị trí của vết nối đầu tiên +1:

SEARCH (“-“, A2) + 1

Số ký kết tự nhằm trích xuất (num_chars): sự khác hoàn toàn giữa địa chỉ của dấu nối sản phẩm hai và 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 form size (tất cả các ký từ bỏ sau vệt nối sản phẩm công nghệ 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 dài của chuỗi, trường đoản cú đó bạn trừ đi địa điểm của dấu nối trang bị hai. Sự khác biệt là số ký tự sau dấu nối lắp thêm hai cùng hàm RIGHT triết xuất chúng.


Trong một biện pháp tương tự, bạn 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 bắt buộc làm là thay thế sửa chữa “-” bằng ký tự ngăn cách bắt buộc, ví dụ như dấu cách (“”), 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 những 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à 1 trong những dấu nối (1 ký kết tự). Giả dụ dấu chia cách của bạn bao hàm 2 ký tự, ví dụ: vệt phẩy và khoảng trắng, tiếp đến chỉ hỗ trợ dấu phẩy (“,”) mang lại hàm SEARCH, và thực hiện +2 và -2 thay vị +1 và -1.

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

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


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

Ví dụ 1. Phân chia chuỗi của một số loại ‘văn phiên bản + 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ố đó 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 sơ để văn bản và số mở ra trong các ô riêng rẽ biệt, như sau:


Để trích xuất các số, áp dụng công thức mảng sau đây, được trả 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 đúng theo A2 là chuỗi ban đầu, cùng C2 là số trích xuất, như miêu tả trong hình bên dưới đây:


Công thức hoạt động như nuốm nào:

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bản, công thức tìm kiếm phần đông số rất 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.

Xem thêm: Diễn Văn Khai Giảng Của Hiệu Trưởng, Diễn Văn Khai Giảng Năm Học 2021

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 thêm một số nào đó trong chuỗi nơi bắt đầu – sửa chữa thay thế số bằng một chuỗi rỗng (“”), và sau đó trừ đi chiều nhiều năm 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. Chính vì đó là 1 trong những công thức mảng, thao tác này được thực hiệ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ả các chữ số trong chuỗi nguồn.Cuối cùng, hàm RIGHT trả về nhiều ký tự tự phía bên nên của chuỗi.

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

Một phương án khác (công thức không có mảng)

Giải pháp sửa chữa thay thế sẽ áp dụng 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 cất một hằng số mảng, đó là một công thức bình thường được kết thúc theo phương 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 search thấy, bạn cũng có thể tách văn bản và số bằng cách sử dụng những công thức LEFT cùng RIGHT rất dễ dàng (nhớ rằng một số 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 phù hợp A2 là chuỗi ban đầu, với B2 là địa điểm của số đầu tiên, như biểu đạt trong hình dưới đây:


Để sa thải cột helper giữ địa chỉ số bắt đầu, bạn cũng có thể nhúng hàm MIN vào những 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 các số:

= RIGHT (A2, LEN (A2) -MIN (SEARCH(0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”)) + 1)

Công thức thống kê giám sát vị trí của số máy 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ó tìm 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. Bởi vì hằng số mảng đựng 10 chữ số, mảng hiệu quả cũng chứa 10 mục.

Hàm MIN rước mảng tác dụng và trả về giá trị nhỏ nhất, tương xứng với địa điểm 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 tạo đặc biệt (A2 & “0123456789”) nhằm ghép từng số rất 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 cho phép chúng tôi tránh lỗi khi một vài nhất định trong hằng số mảng không được tra cứu thấy vào chuỗi nguồn. Vào trường hợp này, phương pháp 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 có thể chấp 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 trường hợp chuỗi nơi bắt đầu không chứa ngẫu nhiên số nào, như trong dòng 7 hình ở trên.

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

5 là cam kết tự lắp thêm 8 trong chuỗi gốc, cùng 0 là ký kết tự đồ vật 7, đó là lý do tại sao mục thứ nhất của mảng tác dụng là “7”, và thứ sáu là “8”.Không có mục nào không giống của hằng số mảng được tìm thấy vào A2, và cho nên vì thế 8 phần khác của mảng tác dụng đại diện cho những vị trí của những chữ số khớp ứng trong chuỗi nối (A2 và “0123456789”).

Xem thêm: 3000691338 - Trường Thpt Nguyễn Huệ


Và chính vì 7 là giá chỉ trị nhỏ nhất vào mảng kết quả, bởi 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.

Ví dụ 2. Phân 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 ô chỗ văn bạn dạng xuất hiện nay sau một số, bạn 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, không tính bạn thực hiện hàm LEFT thay vày RIGHT, bởi vì trong trường phù hợp này số luôn lộ diện ở phía bên trái của chuỗi. Một khi bạn đã có các bé 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 sơ và B2 là số trích xuất, như trình bày trong hình bên dưới đây:

Ví dụ 3. Trích xuất chỉ số tự 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ừ một chuỗi trong định hình ‘number-text-number’, chúng ta có thể sử dụng công thức tiếp sau đây được gợi nhắc bởi 1 trong những chuyên viên của MrExcel:

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