Trong nội dung bài viết này, tnmthcm.edu.vn sẽ giải thích cách bóc các ô vào Excel bằng công thức. Các bạn sẽ học cách tách bóc văn phiên bản dựa theo vết phẩy, khoảng trắng hoặc ngẫu nhiên dấu phân làn nào khác, với làm cầm cố nào để phân chia chuỗi thành văn bạn dạng và số.
Bạn đang xem: Tách chữ và số trong excel
Làm gắng 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 chữ cùng số trong excel, chúng ta thường á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ố trong những công thức hoàn toàn có thể phức tạp, nhưng thực tế logic là khá đối chọi giản, và những ví dụ sau đâ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, vết hai chấm, vết gạch chéo, vết gạch ngang hoặc dấu phân cách khác
Khi phân chia những ô trong Excel, việc đó là xác xác định trí của dấu chia cách trong chuỗi văn bản. Tùy nằm trong vào công việ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 search không rành mạch chữ hoa chữ thường xuyên hoặc hàm Find gồm phân biệt chữ hoa chữ thường. Một khi bạn 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 khớp ứng của chuỗi văn bản.
Để hiểu rõ hơn, hãy chu đáo 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 có nhu cầu chia bóc tách cột thành 3 cột riêng rẽ biệt:
Để trích xuất thương hiệu mục (tất cả những ký từ trước lốt nối đầu tiên), chèn công thức sau trong B2, với 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 xác định trí của lốt nối trước tiên (“-“) trong chuỗi và tác dụng LEFT sẽ chiết tất cả các ký kết tự còn lại (bạn trừ 1 từ vị trí của dấu nối cũng chính 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ũng 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 dạng – vị trí để trích xuất văn bạn dạng từ.Start_num – địa chỉ của kí tự trước tiên để trích xuất.Num_chars – số ký kết tự để trích xuất.Trong cách làm trên, văn phiên bản được trích ra tự ô A2, và 2 đối số khác đượ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 lốt nối trước 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 điểm của vế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) – search (“-“, A2) -1
Để trích xuất form size (tất cả những ký trường đoản cú sau vệt nối đồ vật 3), hãy nhập công thức sau trong D2:= RIGHT (A2, LEN (A2) – search (“-“, 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ạn trừ đi vị trí của dấu nối trang bị hai. Sự khác hoàn toàn là số cam kết tự sau vết nối đồ vật hai với hàm RIGHT chiết xuất chúng.
Mẹo. Trong những công thức trên, +1 và -1 tương xứng với số ký kết tự trong lốt phân cách. Trong ví dụ này, nó là một trong những dấu nối (1 cam kết tự). Nếu dấu ngăn cách của bạn bao gồm 2 ký tự, ví dụ: vệt phẩy và khoảng trắng, tiếp nối chỉ cung cấp dấu phẩy (“,”) mang lại hàm SEARCH, và thực hiện +2 và -2 thay vày +1 cùng -1.
Làm cố gắng nào để phân loại chuỗi bằng cách ngắt mẫu trong Excel:
Để 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 giống như như bí quyết được minh họa trong ví dụ trước. Sự khác hoàn toàn duy tuyệt nhất là bạn cần công dụng CHAR để hỗ trợ cho cam kết tự ngắt mẫu vì các bạn không thể gõ trực tiếp vào công thức. đưa sử, các ô mà bạn có nhu cầu chia nhỏ dại trông giống như như sau:
=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)
Để trích xuất màu 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 thế nào để phân loại văn phiên bản và số trong Excel:
Để bắt đầu, không có giải pháp tổng quát cho tất cả các chuỗi chữ số. Công thức nào nhằm sử dụng phụ thuộc vào chủng loại chuỗi nỗ lực thể. Bên dưới đây bạn sẽ tìm thấy phương pháp cho 3 kịch bạn dạng thường gặp mặt nhất.
Xem thêm: Top 3 Trường Mầm Non Gần Nhất Gần Trung Hòa, Top 20 Trường Mầm Non Tốt Nhất Gần Trung Hòa
Ví dụ 1. Phân tách chuỗi của một số loại ‘văn bản + số’
Giả sử bạn có một cột những chuỗi với văn bản và số kết hợp, trong những số đó một số luôn luôn theo sau văn bản. Bạn có nhu cầu phá vỡ những chuỗi ban sơ để 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ố, thực hiện 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 thích hợp A2 là chuỗi ban đầu, với C2 là số trích xuất, như biểu đạt trong hình bên 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ầu hết số có thể từ 0 cho 9 vào chuỗi nguồn, tính số lượng và trả về nhiều ký từ từ ký kết tự cuối chuỗi ban đầu.
Và đó là công thức cụ thể phân rã:
Trước tiên, bạn sử dụng những hàm LEN với SUBSTITUTE nhằm tìm ra số lần xuất hiện một số nào đó trong chuỗi cội – thay thế số bằng một chuỗi trống rỗng (“”), và tiếp đến trừ đi chiều lâu năm của chuỗi mà không có số đó từ tổng thể Chiều lâu năm của chuỗi ban đầu. Bởi vì đó là một trong những công thức mảng, làm việc này được triển khai 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 thêm của toàn bộ các chữ số trong chuỗi nguồn.Cuối cùng, hàm RIGHT trả về nhiều ký tự từ bỏ phía bên đề nghị của chuỗi.Công thức nhằm trích xuất văn bản (hàm LEFT). Bạn đo lường và 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ố triết xuất (C2) trường đoản cú chiều dài của chuỗi nơi bắt đầu (A2). Sau đó, bạn sử dụng hàm LEFT nhằm trả về những 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 sẽ sử dụng công thức sau nhằm 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ù công thức cũng đựng một hằng số mảng, đó là một trong những công thức bình thường được kết thúc theo biện pháp thông thường bằng cách nhấn phím Enter.
Khi vị trí của số trước tiên được search thấy, bạn 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 (nhớ rằng một số luôn xuất hiện 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ư biểu đạt trong hình 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ị trí của số thiết 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, tạo cho nó search từng số vào hằng số mảng bên trong bạn dạng gốc, với trả lại vị trí của chúng. Cũng chính vì hằng số mảng chứa 10 chữ số, mảng công dụng cũng cất 10 mục.
Hàm MIN đem mảng tác dụng và trả về giá bán trị bé dại nhất, khớp ứng với địa điểm của số đầu tiên trong chuỗi ban đầu.
Ngoài ra, shop chúng tôi sử dụng một cấu trúc đặc biệt (A2 và “0123456789”) nhằm ghép từng số hoàn toàn có thể với chuỗi ban đầu. Phương pháp 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 số trong những nhất định trong hằng số mảng ko được kiếm tìm thấy trong chuỗi nguồn. Trong trường vừa lòng này, công thức trả về vị trí “giả mạo” bằng chuỗi chiều dài từ là 1 ký tự trở lên. Điều này cho phép 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 như chuỗi cội không chứa ngẫu nhiên số nào, như trong mẫu 7 hình sinh hoạt trên.
Ví dụ: so 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à đây là cách cửa hàng chúng tôi có:
5 là ký tự lắp thêm 8 trong chuỗi gốc, với 0 là cam kết tự sản phẩm công nghệ 7, đó là lý do tại sao mục trước 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 search thấy trong A2, và cho nên vì vậy 8 phần không giống của mảng tác dụng đại diện cho các vị trí của các chữ số tương ứng trong chuỗi nối (A2 và “0123456789”).
Ví dụ 2. Phân tách chuỗi của các loại ‘số + văn bản’
Nếu các bạn đang tách bóc 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ương tự như phương pháp mảng từ ví dụ trước, xung quanh bạn sử dụng hàm LEFT thay bởi vì RIGHT, chính vì trong trường hợp này số luôn lộ diện ở phía phía bên trái của chuỗi. Một khi bạn đã có các con số, trích xuất văn bạn dạng bằng biện pháp trừ số chữ số từ 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 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 công việc của bạn yên cầu 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’, bạn cũng có thể sử dụng công thức dưới đây được gợi ý bởi một trong những 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ẾP (“1:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)
Trường hòa hợp A2 là chuỗi văn bản ban đầu.
