Trong bài viết này, ditinh.vn đang lý giải cách bóc các ô trong Excel bằng bí quyết. quý khách sẽ học bí quyết bóc tách vnạp năng lượng phiên bản dựa vào vết phẩy, khoảng chừng Trắng hoặc bất kỳ vệt phân làn nào không giống, với có tác dụng nạm làm sao để phân chia chuỗi thành văn uống bản và số.

Bạn đang xem: Tách chữ và số trong excel


Làm rứa như thế nào để chia văn bạn dạng trong Excel bằng phương pháp áp dụng công thức:

Để bóc tách chuỗi trong Excel tuyệt tách chữ với số trong excel, các bạn thường sử dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. trước khi, một số trong những cách làm có thể phức tạp, tuy nhiên thực tế xúc tích là tương đối dễ dàng, và các ví dụ dưới đây đã cung cấp cho mình một số trong những mối lái.

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

Lúc phân chia những ô vào Excel, vấn đề chính là xác xác định trí của vệt chia cách vào chuỗi văn uống bạn dạng. Tùy thuộc vào công việc của bạn, điều đó rất có thể được triển khai bằng phương pháp áp dụng hàm Search không riêng biệt chữ hoa chữ hay hoặc hàm Find có sáng tỏ chữ hoa chữ thường. Một khi chúng ta gồm vị trí của vết phân làn, á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 uống bạn dạng.

Để hiểu rõ hơn, hãy để ý ví dụ sau đây:

Giả sử chúng ta bao gồm một danh sách các SKU của mẫu Loại-Màu-Kích thước, cùng bạn muốn chia bóc cột thành 3 cột riêng biệt:

*


*

*

Để trích xuất tên mục (tất cả những ký từ trước vệt nối đầu tiên), ckém bí quyết sau vào B2, với tiếp nối sao chép nó xuống cột:

= LEFT (A2, search (“-“, A2,1) -1)

Trong công thức này, hàm Search xác xác định trí của dấu nối thứ nhất (“-“) trong chuỗi và công dụng LEFT đã phân tách tất cả những ký từ bỏ còn sót lại (bạn trừ 1 tự địa điểm của dấu nối chính vì các bạn không muốn có lốt nối).

*

Để trích xuất màu sắc (toàn bộ những ký kết trường đoản cú giữa các lốt gạch men nối thứ 2 cùng trang bị 3), hãy nhập phương pháp sau vào C2, với tiếp đến xào luộc nó xuống các ô khác:

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

*

Nhỏng bạn có thể biết, hàm MID tất cả cú pháp sau:

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

Nơi:

Văn bản – địa điểm để trích xuất văn bạn dạng tự.Start_num – địa chỉ của kí từ trước tiên nhằm trích xuất.Num_chars – số cam kết trường đoản cú nhằm trích xuất.

Trong cách làm bên trên, văn uống bạn dạng được trích ra từ ô A2, với 2 đối số khác được tính bằng phương pháp áp dụng 4 hàm SEARCH khác:

Số ban đầu (start_num) là địa chỉ của lốt nối đầu tiên +1:

SEARCH (“-“, A2) + 1

Số cam kết trường đoản cú để trích xuất (num_chars): sự khác hoàn toàn thân địa điểm của lốt nối trang bị nhì và lốt nối đầu tiên, trừ đi 1:

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

Để trích xuất 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 cách làm sau vào D2:

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

Trong công thức này, hàm LEN trả về tổng chiều nhiều năm của chuỗi, từ bỏ đó các bạn trừ đi địa chỉ của dấu nối đồ vật nhì. Sự khác hoàn toàn là số ký kết trường đoản cú sau lốt nối sản phẩm công nghệ nhị với hàm RIGHT triết xuất bọn chúng.

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

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

Làm chũm nào nhằm phân chia chuỗi bằng phương pháp ngắt loại vào Excel:

Để phân chia văn phiên bản bởi khoảng tầm Trắng, hãy sử dụng những công thức tương tự như bí quyết được minc họa vào ví dụ trước. Sự khác biệt duy nhất là bạn cần công dụng CHAR nhằm cung cấp cho cam kết từ ngắt mẫu vày bạn cần yếu gõ trực tiếp vào công thức. Giả sử, các ô nhưng bạn có nhu cầu phân tách nhỏ tuổi trông giống như như sau:

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

Để trích xuất tên mặt hàng:

=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 gắng làm sao để phân chia văn uống bản và số trong Excel:

Để ban đầu, không có giải pháp tổng quát mang lại tất cả các chuỗi chữ số. Công thức nào để sử dụng phụ thuộc vào mẫu chuỗi cụ thể. Dưới đây các bạn sẽ tìm thấy phương pháp mang đến 3 kịch phiên bản thường gặp mặt duy nhất.

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

Giả sử các bạn có một cột những chuỗi với văn uống bản và số phối kết hợp, trong những số đó một trong những luôn luôn luôn theo sau văn uống bạn dạng. Quý Khách mong mỏi phá vỡ lẽ những chuỗi thuở đầu để văn uống bạn dạng với số mở ra trong những ô riêng biệt, nhỏng sau:

*

Để trích xuất những số, áp dụng phương pháp mảng tiếp sau đây, được kết thúc bằng phương pháp 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 phiên bản, sử dụng:

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

Trường phù hợp A2 là chuỗi ban sơ, với C2 là số trích xuất, nlỗi trình bày vào hình dưới đây:

*
Công thức hoạt động như thế nào:

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ phiên bản, phương pháp kiếm tìm kiếm đầy đủ số hoàn toàn có thể từ bỏ 0 cho 9 trong chuỗi nguồn, tính số lượng và trả về các cam kết trường đoản cú từ bỏ ký kết từ bỏ cuối chuỗi thuở đầu.

Và đấy là phương pháp chi tiết phân rã:

Trước tiên, bạn áp dụng các hàm LEN cùng SUBSTITUTE để tìm ra số lần lộ diện một số như thế nào kia vào chuỗi gốc – sửa chữa thay thế số bởi một chuỗi rỗng (“”), với kế tiếp trừ đi chiều lâu năm của chuỗi mà không tồn tại số kia tự tổng số Chiều dài của chuỗi ban sơ. Bởi do kia là 1 trong những phương pháp mảng, làm việc này được triển khai bên trên từng 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 tất cả các lần xuất hiện thêm của tất cả những chữ số trong chuỗi mối cung cấp.Cuối thuộc, hàm RIGHT trả về nhiều ký kết trường đoản cú từ bỏ phía mặt yêu cầu của chuỗi.

Công thức nhằm trích xuất văn uống phiên bản (hàm LEFT). Bạn tính toán từng nào cam kết trường đoản cú văn uống phiên bản chuỗi cất bằng cách trừ số chữ số tinh chiết (C2) tự chiều nhiều năm của chuỗi gốc (A2). Sau đó, chúng ta sử dụng hàm LEFT nhằm trả về những ký từ 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 sửa chữa thay thế đang thực hiện công thức sau nhằm 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 mặc dù cách làm cũng đựng một hằng số mảng, đó là 1 trong công thức 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 kiếm tìm thấy, chúng ta cũng có thể tách bóc văn bạn dạng với số bằng cách sử dụng những công thức LEFT cùng RIGHT vô cùng đơn giản và dễ dàng (hãy nhờ rằng một số trong những luôn luôn xuất hiện thêm sau vnạp năng lượng 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 chỉ của số thứ nhất, nhỏng trình bày vào hình bên dưới đây:

*
Để thải trừ cột helper duy trì vị trí số ban đầu, chúng ta cũng có thể nhúng hàm MIN vào những hàm LEFT với RIGHT:

Công thức trích xuất vnạp năng lượ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 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ù vị trí 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 trong đối số find_text của hàm SEARCH, làm cho nó search từng số trong hằng số mảng bên trong bạn dạng cội, và trả lại địa chỉ của bọn chúng. Bởi bởi vì hằng số mảng đựng 10 chữ số, mảng công dụng cũng chứa 10 mục.

Hàm MIN đem mảng hiệu quả với trả về quý hiếm nhỏ dại duy nhất, khớp ứng cùng với địa điểm của số đầu tiên trong chuỗi ban sơ.

Hình như, chúng tôi áp dụng một cấu trúc đặc biệt (A2 & “0123456789”) nhằm ghxay mỗi số hoàn toàn có thể với chuỗi ban đầu. Cách này tiến hành mục đích của IFERROR và được cho phép Shop chúng tôi tránh lỗi Lúc một trong những nhất quyết trong hằng số mảng ko được tìm thấy vào chuỗi nguồn. Trong trường hợp này, công thức trả về địa chỉ “giả mạo” bằng chuỗi chiều lâu năm từ là 1 cam kết từ trlàm việc lên. Vấn đề này chất nhận được hàm LEFT trích xuất văn bản và hàm RIGHT trả về một chuỗi trống rỗng ví như chuỗi cội ko cất ngẫu nhiên số nào, như trong dòng 7 hình ngơi nghỉ trên.

Ví dụ: so với chuỗi “Dress 05” trong A2, mảng công dụng là 7,10,11,12,13,8,15,16,17,18. Và đây là giải pháp Cửa Hàng chúng tôi có:

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

*
Và cũng chính vì 7 là cực hiếm nhỏ dại duy nhất trong mảng tác dụng, vì hàm MIN trả về, và chúng ta nhận thấy vị trí của số thứ nhất (0) trong chuỗi văn phiên bản ban sơ.

Xem thêm: Cách Sử Dụng Teamviewer Mà Đối Phương Không Biết!, Cách Sử Dụng Teamviewer Mà Đối Phương Không Biết

ví dụ như 2. Chia chuỗi của loại ‘số + văn bản’

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

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

Trong những phương pháp trên, A2 là chuỗi thuở đầu và B2 là số trích xuất, nlỗi diễn tả trong hình bên dưới đây:

*

lấy ví dụ như 3. Trích xuất chỉ số trường đoản cú chuỗi số ‘số văn uống bản’

Nếu các bước của khách hàng yên cầu phải trích xuất toàn bộ những số xuất phát điểm từ 1 chuỗi vào định dạng ‘number-text-number’, chúng ta có thể thực hiện phương pháp tiếp sau đây được lưu ý bởi một trong những chuyên gia của MrExcel:

= SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)) * ROW (TRỰC TIẾPhường (“1:” và LEN (A2) (1: “và LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “và LEN (A2))) / 10)

Trường thích hợp A2 là chuỗi văn uống bạn dạng lúc đầu.

*