Trong bài viết này, bloginar.netinar.net đang phân tích và lý giải cách bóc tách những ô vào Excel bởi cách làm. quý khách đang học phương pháp bóc vnạp năng lượng phiên bản dựa theo vệt phẩy, khoảng chừng white hoặc ngẫu nhiên vệt phân cách như thế nào khác, cùng có tác dụng vắt làm sao để phân chia chuỗi thành văn bạn dạng và số.
Bạn đang xem: Lấy chuỗi ký tự trong excel
Làm thay nào nhằm phân chia văn phiên bản trong Excel bằng phương pháp áp dụng công thức:
Để bóc tách chuỗi vào Excel xuất xắc tách bóc chữ với số vào excel, bạn hay sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. lúc đầu, một trong những phương pháp hoàn toàn có thể phức tạp, nhưng mà thực tiễn súc tích là tương đối dễ dàng và đơn giản, cùng những ví dụ tiếp sau đây sẽ cung cấp cho mình một số trong những mối manh.
Tách chuỗi bằng lốt phẩy, vệt nhì chấm, vệt gạch chéo cánh, vết gạch ốp ngang hoặc có thể dấu phân cách khác
Khi phân loại những ô trong Excel, vấn đề đó là xác định vị trí của vết phân cách vào chuỗi văn bản. Tùy thuộc vào công việc của bạn, điều này hoàn toàn có thể được tiến hành bằng cách thực hiện hàm Search ko sáng tỏ chữ hoa chữ thường hoặc hàm Find tất cả rõ ràng chữ hoa chữ thường. Một khi chúng ta có vị trí của vệt phân làn, thực hiện hàm RIGHT, LEFT hoặc MID nhằm trích xuất phần tương xứng của chuỗi vnạp năng lượng bạn dạng.
Để hiểu rõ hơn, hãy chăm chú ví dụ sau đây:
Giả sử bạn tất cả một list các SKU của mẫu Loại-Màu-Kích thước, và bạn muốn phân tách tách cột thành 3 cột riêng biệt biệt:
= LEFT (A2, tìm kiếm (“-“, A2,1) -1)
Trong cách làm này, hàm Search xác xác định trí của vệt nối thứ nhất (“-“) vào chuỗi và công dụng LEFT vẫn tách tất cả những ký kết từ còn sót lại (bạn trừ 1 trường đoản cú địa chỉ của lốt nối bởi vì bạn không thích có vệt nối).
=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1
Nlỗi bạn cũng có thể biết, hàm MID bao gồm cú pháp sau:
MID (vnạp năng lượng bản, start_num, num_chars)
Nơi:
Văn uống bạn dạng – địa điểm để trích xuất văn bản trường đoản cú.Start_num – địa chỉ của kí tự thứ nhất để trích xuất.Num_chars – số ký kết trường đoản cú nhằm trích xuất.Trong phương pháp bên trên, văn bản được trích ra từ bỏ ô A2, với 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ố ban đầ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ừ bỏ nhằm trích xuất (num_chars): sự khác biệt thân địa chỉ của vệt nối sản phẩm công nghệ hai và vết nối thứ nhất, trừ đi 1:SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1
Để trích xuất size (tất cả những ký trường đoản cú sau vết nối lắp thêm 3), hãy nhập cách làm 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, trường đoản cú đó các bạn trừ đi địa chỉ của vết nối máy nhị. Sự biệt lập là số ký trường đoản cú sau lốt nối vật dụng nhị với hàm RIGHT tinh chiết chúng.
Mẹo. Trong những cách làm trên, +1 với -1 khớp ứng cùng với số cam kết tự trong dấu chia cách. Trong ví dụ này, nó là 1 vết nối (1 cam kết tự). Nếu lốt chia cách của người tiêu dùng bao gồm 2 ký kết từ, ví dụ: Dấu phẩy cùng khoảng White, tiếp nối chỉ cung cấp dấu phẩy (“,”) mang lại hàm SEARCH, với áp dụng +2 cùng -2 rứa vì chưng +1 với -1.
Làm thế làm sao để phân chia chuỗi bằng cách ngắt mẫu vào Excel:
Để phân chia văn uống bản bởi khoảng trắng, hãy thực hiện những phương pháp tương tự như công thức được minh họa vào ví dụ trước. Sự khác hoàn toàn nhất là bạn phải chức năng CHAR nhằm cung cấp mang đến ký kết từ ngắt mẫu bởi vì chúng ta cấp thiết gõ thẳng vào bí quyết. Giả sử, những ô mà bạn muốn chia bé dại trông tương tự như nhỏng 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à đây là kết quả:
Làm nắm nào nhằm phân loại văn uống bạn dạng và số vào Excel:
Để bắt đầu, không có chiến thuật bao quát mang đến tất cả những chuỗi chữ số. Công thức nào để áp dụng nhờ vào vào chủng loại chuỗi rõ ràng. Dưới trên đây bạn sẽ tìm thấy bí quyết cho 3 kịch bản thường gặp mặt duy nhất.
Xem thêm: Cách Viết Phân Số Trong Excel, Hướng Dẫn Đơn Giản Dễ Hiểu Nhất
lấy ví dụ 1. Chia chuỗi của loại ‘văn uống phiên bản + số’
Giả sử các bạn tất cả một cột các chuỗi với vnạp năng lượng bản với số phối hợp, trong các số ấy một số luôn luôn luôn luôn theo sau văn bản. Bạn mong mỏi phá vỡ lẽ các chuỗi ban đầu nhằm văn bản và số lộ diện trong số ô riêng lẻ, nlỗi sau:
Để trích xuất những số, thực hiện công thức mảng tiếp sau đây, được chấm dứt bằng phương pháp thừa 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 uống phiên bản, sử dụng:
= LEFT (A2, LEN (A2) -LEN (C2))
Trường hòa hợp A2 là chuỗi ban sơ, cùng C2 là số trích xuất, nlỗi miêu tả trong hình dưới đây:
Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bạn dạng, phương pháp kiếm tìm kiếm gần như số có thể từ bỏ 0 cho 9 trong chuỗi nguồn, tính con số và trả về nhiều cam kết từ trường đoản cú cam kết tự cuối chuỗi lúc đầu.
Và đây là cách làm cụ thể phân rã:
Đầu tiên, chúng ta thực hiện những hàm LEN cùng SUBSTITUTE để tìm thấy số lần xuất hiện thêm 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 rỗng (“”), và sau đó trừ đi chiều lâu năm của chuỗi mà lại không có số đó từ bỏ tổng số Chiều dài của chuỗi thuở đầu. Bởi bởi đó là 1 trong phương pháp mảng, làm việc này được triển khai 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 toàn bộ những lần xuất hiện thêm của toàn bộ các chữ số vào chuỗi nguồn.Cuối cùng, hàm RIGHT trả về nhiều cam kết từ trường đoản cú phía mặt đề nghị của chuỗi.Công thức nhằm trích xuất văn uống bạn dạng (hàm LEFT). quý khách tính toán thù từng nào ký kết trường đoản cú văn uống phiên bản chuỗi chứa bằng cách trừ số chữ số chiết xuất (C2) tự chiều dài của chuỗi gốc (A2). Sau kia, các bạn áp dụng hàm LEFT để trả về nhiều ký kết từ bỏ từ đầu chuỗi.
Một giải pháp khác (bí quyết không tồn tại mảng)
Giải pháp thay thế sửa chữa sẽ áp dụng phương pháp sau nhằm xác định vị 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 cho dù bí quyết cũng chứa một hằng số mảng, đó là 1 trong những phương pháp thông thường được dứt Theo phong cách thông thường bằng cách dìm phím Enter.
Khi vị trí của số đầu tiên được search thấy, bạn có thể bóc văn bản cùng số bằng cách sử dụng các bí quyết LEFT với RIGHT cực kỳ dễ dàng và đơn giản (hãy nhớ là một số luôn luôn mở ra sau văn bản):
Để trích xuất vnạp năng lượng bản:= LEFT (A2, B2-1)
Để trích xuất số:=RIGHT(B2, LEN(A1)-B2+1)
Trường hòa hợp A2 là chuỗi ban sơ, cùng B2 là địa điểm của số thứ nhất, 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 & “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 và “0123456789”)) + 1)
Công thức tính toán thù địa điểm của số sản phẩm nhất
Quý Khách cung ứng 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ố trong hằng số mảng phía bên trong bạn dạng nơi bắt đầu, và trả lại địa điểm của chúng. Bởi do hằng số mảng chứa 10 chữ số, mảng hiệu quả cũng đựng 10 mục.
Hàm MIN rước mảng tác dụng và trả về cực hiếm nhỏ duy nhất, tương xứng với địa điểm của số đầu tiên vào chuỗi ban đầu.
Ngoài ra, chúng tôi áp dụng một cấu tạo quan trọng (A2 và “0123456789”) nhằm ghnghiền mỗi số hoàn toàn có thể cùng với chuỗi lúc đầu. Cách này thực hiện vai trò của IFERROR với chất nhận được công ty chúng tôi tách lỗi khi một số trong những khăng khăng trong hằng số mảng không được tìm thấy vào chuỗi mối cung cấp. Trong ngôi trường phù hợp này, bí quyết trả về vị trí “giả mạo” bởi chuỗi chiều nhiều năm từ một ký kết tự trngơi nghỉ lên. Như vậy chất nhận được hàm LEFT trích xuất văn uống bản với hàm RIGHT trả về một chuỗi trống rỗng trường hợp chuỗi gốc ko cất bất kỳ số nào, nhỏng trong chiếc 7 hình ở 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à đó là biện pháp chúng tôi có:
5 là ký từ trang bị 8 trong chuỗi cội, với 0 là cam kết tự đồ vật 7, sẽ là nguyên do vì sao mục thứ nhất của mảng tác dụng là “7”, với thiết bị sáu là “8”.Không có mục nào khác của hằng số mảng được search thấy vào A2, và vì thế 8 phần không giống của mảng kết quả thay mặt đại diện cho những vị trí của các chữ số khớp ứng trong chuỗi nối (A2 & “0123456789”).
ví dụ như 2. Chia chuỗi của các loại ‘số + văn bản’
Nếu nhiều người đang bóc những ô vị trí vnạp năng lượng phiên bản lộ diện sau một số, bạn 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 phương pháp dấ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ỏng phương pháp mảng từ bỏ ví dụ trước, ngoài bạn áp dụng hàm LEFT nắm vì RIGHT, chính vì trong trường thích hợp này số luôn lộ diện làm việc phía bên trái của chuỗi. Một lúc bạn đã có những con số, trích xuất văn phiên bản bằng cách trừ số chữ số trường đoản cú tổng chiều nhiều năm của chuỗi gốc:
= RIGHT(A2, LEN (A2) -LEN (B2))
Trong những phương pháp bên trên, A2 là chuỗi thuở đầu và B2 là số trích xuất, nlỗi biểu đạt vào hình dưới đây:
Ví dụ 3. Trích xuất chỉ số từ bỏ chuỗi số ‘số văn bản’
Nếu các bước của người sử dụng đòi hỏi buộc phải trích xuất toàn bộ các số xuất phát điểm từ một chuỗi vào định dạng ‘number-text-number’, chúng ta có thể thực hiện phương pháp sau đây được gợi nhắc vì một trong số những chuyên gia của MrExcel:
= SUMPRODUCT (MID (0 và 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 hợp A2 là chuỗi văn uống phiên bản thuở đầu.
