Trong nội dung bài viết này, blog.hocexcel.online sẽ lý giải cách bóc tách các ô trong Excel bởi công thức. Các bạn sẽ học cách tách 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 chia cách nào khác, với làm rứa nào để phân chia chuỗi thành văn bản và số.
Bạn đang xem: Tách ký tự trong chuỗi excel
Làm nú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 trong Excel hay tách bóc chữ cùng số trong excel, chúng ta thường thực hiện hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Dịp đầ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á đơn giản, và những ví dụ dưới đây sẽ cung cấp cho mình một số đầu mối.Tách chuỗi bằng dấu phẩy, dấu hai chấm, lố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 ô vào 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 trực thuộc vào các bước của bạn, điều này có thể được thực hiện bằng cách sử dụng hàm tìm kiếm không rõ ràng chữ hoa chữ thường hoặc hàm Find gồm phân biệt chữ hoa chữ thường. Một khi bạn có địa chỉ của vế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 chăm chú ví dụ sau đây:
Giả sử bạn có một danh sách các SKU của mẫu mã 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 thương hiệu mục (tất cả những ký từ bỏ trước dấu nối đầu tiên), chèn bí quyết sau trong B2, và sau đó sao chép nó xuống cột:
= LEFT (A2, search (“-“, A2,1) -1)
Trong phương pháp này, hàm tìm kiếm xác xác định trí của dấu nối trước tiên (“-“) trong chuỗi và tác dụng LEFT đang chiết toàn bộ các ký kết tự còn sót lại (bạn trừ 1 từ địa chỉ của vết nối chính vì bạn không thích có dấu nối).
Như chúng ta có thể biết, hàm MID có cú pháp sau:
MID (văn bản, start_num, num_chars)
Nơi:
Văn bạn dạng – chỗ để trích xuất văn bạn dạng từ. Start_num – vị trí của kí tự thứ nhất để trích xuất. Num_chars – số ký kết tự để trích xuất.Trong cách làm 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ước đầu (start_num) là địa chỉ 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 giữa vị trí của vệt nối đồ vật hai cùng 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ý tự sau lốt nối lắp thêm 3), hãy nhập cách làm sau trong D2:= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, A2, search (“-“, A2) + 1))
Trong bí quyết này, hàm LEN trả về tổng chiều lâu năm của chuỗi, tự đó bạn trừ đi địa điểm của dấu nối sản phẩm công nghệ hai. Sự biệt lập là số cam kết tự sau lốt nối đồ vật hai với hàm RIGHT triết xuất chúng.
Mẹo. Trong các công thức trên, +1 với -1 tương ứng với số cam kết tự trong vệt phân cách. Trong ví dụ như này, nó là một trong dấu nối (1 ký kết tự). Giả dụ dấu phân làn của bạn bao gồm 2 cam kết tự, ví dụ: vết phẩy và khoảng chừng trắng, sau đó chỉ cung cấp dấu phẩy (“,”) mang lại hàm SEARCH, và áp dụng +2 với -2 thay vì chưng +1 với -1.
Làm thế nào để phân chia chuỗi bằng cách ngắt mẫu trong Excel:
Để phân chia văn phiên bản bằng khoảng trắng, hãy sử dụng những công thức tựa như như cách làm được minh họa trong lấy ví dụ như trước. Sự khác biệt duy độc nhất là bạn cần tác dụng CHAR để cung cấp cho ký kết tự ngắt dòng vì các bạn không thể gõ thẳng vào công thức. Giả sử, những ô mà bạn có nhu cầu chia nhỏ tuổi trông tựa như như sau:
=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)
Để trích xuất màu sắc 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à đó là kết quả:
Làm cầm cố nào để phân chia văn phiên bản và số vào Excel:
Để bắt đầu, không có giải pháp tổng quát mắng cho tất cả các chuỗi chữ số. Cách làm nào để sử dụng dựa vào vào mẫu chuỗi cố gắng thể. Bên dưới đây bạn sẽ tìm thấy bí quyết cho 3 kịch phiên bản thường gặp gỡ nhất.
Ví dụ 1. Phân tách chuỗi của nhiều loại ‘văn bản + số’
Giả sử các bạn có một cột những chuỗi cùng với văn phiê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ỡ các chuỗi lúc đầu để văn phiên bản và số mở ra trong các ô riêng rẽ biệt, như sau:
Để trích xuất những số, thực hiện 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 vừa lòng A2 là chuỗi ban đầu, với C2 là số trích xuất, như mô tả trong hình dưới đây:
Công thức để trích xuất số (hàm RIGHT). Về cơ bản, phương pháp tìm kiếm các số rất có thể từ 0 mang đến 9 vào chuỗi nguồn, tính con số 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 cụ thể phân rã:
Trước tiên, các bạn sử dụng các hàm LEN và SUBSTITUTE nhằm tìm ra số lần xuất hiện thêm một số nào kia trong chuỗi cội – sửa chữa số bởi một chuỗi rỗng (“”), và sau đó trừ đi chiều dài của chuỗi mà không tồn tại số đó từ toàn bô Chiều lâu năm của chuỗi ban đầu. Cũng chính vì đó là 1 công thức mảng, thao tác làm việc này được triển khai trên mỗi 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 toàn bộ các lần mở ra của tất cả các chữ số vào chuỗi nguồn. Cuối cùng, hàm RIGHT trả về các ký tự tự phía bên bắt buộc của chuỗi.Công thức nhằm trích xuất văn bản (hàm LEFT). Bạn giám sát bao nhiêu ký tự văn bản chuỗi chứa bằng cách trừ số chữ số triết xuất (C2) từ chiều dài của chuỗi gốc (A2). Sau đó, bạn sử dụng hàm LEFT nhằm trả về nhiều 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 sửa chữa thay thế sẽ thực hiện công thức sau nhằm xác xác định trí của số đầu tiên trong chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”))
Mặc dù cách làm cũng chứa một hằng số mảng, đó là một trong công thức bình thường được xong xuôi theo cách 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ó 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 với RIGHT rất đơn giản dễ dàng (nhớ rằng một vài 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 hợp A2 là chuỗi ban đầu, và B2 là địa điểm của số đầu tiên, như biểu lộ trong hình bên dưới đây:
= LEFT (A2, MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 & “0123456789”)) – 1)
phương pháp trích xuất những 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ố đồ vật nhất
Bạn cung ứng 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ó tra cứu từng số trong hằng số mảng bên trong phiên bản gốc, cùng trả lại vị trí của chúng. Chính vì hằng số mảng chứa 10 chữ số, mảng kết quả cũng chứa 10 mục.
Hàm MIN lấy mảng kết quả và trả về giá trị bé dại nhất, khớp ứng với vị trí của số đầu tiên trong chuỗi ban đầu.
Ngoài ra, công ty chúng tôi sử dụng một cấu tạo đặc biệt (A2 & “0123456789”) để 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à có thể chấp nhận được chúng tôi tránh lỗi khi một vài 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 thích hợp này, cách làm trả về vị trí “giả mạo” bởi chuỗi chiều dài từ 1 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 phiên bản và hàm RIGHT trả về một chuỗi rỗng ví như chuỗi cội không chứa ngẫu nhiên số nào, như trong cái 7 hình sinh sống trên.
Ví dụ: đối với chuỗi “Dress 05” vào A2, mảng kết quả 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 tự đồ vật 8 vào chuỗi gốc, với 0 là ký kết tự thiết bị 7, kia là nguyên nhân tại sao mục thứ nhất của mảng tác dụng là “7”, với thứ sáu là “8”. Không có mục nào khác của hằng số mảng được kiếm tìm thấy vào A2, và cho nên vì thế 8 phần không giống của mảng kết quả đại diện cho các vị trí của các chữ số tương xứng trong chuỗi nối (A2 và “0123456789”).
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 bóc các ô vị trí văn phiên bản xuất hiện tại sau một số, bạn cũng có thể trích xuất các 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, xung quanh bạn thực hiện hàm LEFT thay do 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 đã sở hữu các con số, trích xuất văn phiên bản bằng bí quyết trừ số chữ số từ tổng chiều dài 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ư mô tả trong hình dưới đây:
Ví dụ 3. Trích xuất chỉ số trường đoản cú chuỗi số ‘số văn bản’
Nếu quá trình của bạn yên cầu phải trích xuất toàn bộ các số xuất phát từ một chuỗi trong format ‘number-text-number’, bạn có thể sử dụng công thức dưới đây được gợi nhắc bởi một trong 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:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)
Trường phù hợp A2 là chuỗi văn phiên bản ban đầu.
Với từng kiểu tài liệu trong Excel thì họ lại có một đội nhóm hàm riêng nhằm xử lý. Trong bài viết này, bọn chúng mình sẽ hướng dẫn các bạn cách dùng 3 hàm phân tách bóc chuỗi ký kết tự vào Excel là LEFT, RIGHT với MID nhằm xử lý dữ liệu dạng văn bản. Hãy cùng tìm hiểu ngay nhé.
3 hàm phân bóc tách chuỗi ký tự vào Excel
Công thức hàm phân tách chuỗi cam kết tự trong Excel
Tên hàm | Công dụng | Công thức hàm | Chú thích |
Hàm LEFT | Phân bóc tách phần ký kết tự bên trái | LEFT(text, | Text: Là chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất (Bắt buộc).Num_chars: Chỉ rõ số ký tự mà bạn muốn hàm LEFT trích xuất (Tùy chọn). Lưu ý: Num_chars phải lớn hơn hoặc bằng 0.Nếu num_chars lớn rộng độ dài của văn bản, hàm LEFT vẫn trả về toàn bộ văn bản.Nếu bỏ qua num_chars thì quý giá này được đặt giả là 1. |
Hàm RIGHT | Phân tách phần cam kết tự mặt phải | RIGHT(text, | Text: Là chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất (Bắt buộc).Num_chars: Chỉ rõ số ký tự mà bạn muốn hàm RIGHT trích xuất (Tùy chọn). Lưu ý: Số ký tự phải lớn hơn hoặc bằng 0.Nếu số ký tự lớn hơn độ dài của văn bản, hàm RIGHT trả về công dụng là toàn bộ văn bản.Nếu số ký tự được bỏ qua thì nó được giả định là 1. |
Hàm MID | Phân tách bóc phần ký kết tự nghỉ ngơi giữa | MID(text, start_num, num_chars) | Văn bản: Là chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất (Bắt buộc).Start_num: Là vị trí của ký tự thứ nhất mà bạn muốn trích xuất vào văn bản. Ký tự thứ nhất vào chuỗi văn bản có số bắt đầu là 1 (Bắt buộc).Num_chars: Là số ký tự mà bạn muốn hàm MID trả về từ văn bản (Bắt buộc). Lưu ý: Nếu start_num to hơn độ dài của văn bản, thì tác dụng trả về là "" (văn bản trống).Nếu start_num nhỏ dại hơn độ dài của văn phiên bản nhưng start_num cộng num_chars vượt thừa độ nhiều năm văn phiên bản thì giá trị trung bình của số MID sẽ trả về các ký tự mang lại cuối văn bản.Nếu start_num nhỏ dại hơn 1, thì quý giá trung bình của số MID sẽ trả về #VALUE! |
Ví dụ về hàm phân bóc chuỗi cam kết tự trong Excel
Để hoàn toàn có thể hiểu rõ hơn về cách hoạt động của hàm LEFT, hàm RIGHT với hàm MID trong việc phân bóc tách chuỗi cam kết tự bên trên Excel thì chúng ta hãy theo dõi những ví dụ tiếp sau đây của bọn chúng mình nhé.
Ví dụ 1Nội dung văn bản | Yêu cầu |
| ICSE9002 | Tách 4 ký kết tự phía bên trái |
Chúng ta sẽ sử dụng hàm LEFT với công thức sau:
=LEFT(B12, 4) với B12 là tọa độ của ô đựng phần văn bản cần bóc dữ liệu khỏi chuỗi.
hoặc
= LEFT("ICSE9002", 4)
Kết quả mà bọn họ thu được như sau:
Nội dung văn bản | Yêu cầu |
| Trần Văn Hùng | Tách riêng biệt phần tên bao hàm 4 ký kết tự bên phải |
Ở đây bọn họ sẽ áp dụng hàm RIGHT với bí quyết như sau:
=RIGHT(B13,4)
hoặc
=RIGHT(B13,LEN("Hùng"))
Với công thức bên dưới thì chúng ta dùng thêm hàm LEN nhằm đếm số ký kết tự thay bởi vì nhập trực tiếp con số ký tự.
Ví dụ 3
Nội dung văn bản | Yêu cầu |
| 01KHOA125 | Tách chữ KHOA thoát khỏi nội dung |
Trong nội dung văn bạn dạng cho sẵn thì chữ cái đầu tiên trong chữ KHOA nằm ở trong phần thứ 3 của chuỗi văn bản, bọn họ sẽ đề xuất lấy 4 ký tự từ bỏ chuỗi này để triển khai yêu cầu. Vày đó, sinh hoạt đây chúng ta sẽ dùng hàm MID với bí quyết như sau:
=MID(B14, 3, 4) với ô B14 là ô tất cả chứa đoạn văn bạn dạng của đề bài.
Xem thêm: Cách Sử Dụng Hàm Power Trong Excel Là Gì, Hàm Power Và Cách Dùng Hàm Power Trong Excel
Kết quả mà lại hàm trả về hòa hợp yêu mong đề bài:
Nội dung văn bản | Yêu cầu |
| Đống Đa, Hà Nội | Tách chữ thành phố hà nội ra khỏi nội dung |
Vận dụng kiến thức và kỹ năng đã biết ở các ví dụ trên thì làm việc đây bọn họ sẽ thực hiện hàm RIGHT với phương pháp như sau:
=RIGHT(B15, LEN("Hà Nội"))
Kết luận
Như vậy, qua bài viết này chúng ta đã hiểu rằng cách áp dụng 3 hàm vào Excel để phân bóc chuỗi cam kết tự. Chúc chúng ta luôn tiếp thu kiến thức hiệu quả.