Cách tách chữ và số trong excel

Trong nội dung bài viết này, deptraiphaitheclub.com đang phân tích và lý giải biện pháp bóc các ô vào Excel bởi bí quyết. Quý Khách sẽ học tập biện pháp tách văn phiên bản dựa trên dấu phẩy, khoảng tầm trắng hoặc ngẫu nhiên vệt phân cách nào không giống, và có tác dụng rứa như thế nào để phân chia chuỗi thành văn uống phiên bản và số.

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

Làm gắng làm sao để phân chia vnạp năng lượng bản vào Excel bằng phương pháp thực hiện công thức:

Để bóc chuỗi vào Excel giỏi tách bóc chữ và số trong excel, chúng ta thường xuyên áp dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. ban sơ, một trong những công thức có thể tinh vi, nhưng thực tế logic là hơi dễ dàng, cùng những ví dụ sau đây đang cung cấp cho mình một số mối manh.

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

Lúc phân chia các ô trong Excel, vấn đề đó là xác xác định trí của vệt phân cách trong chuỗi văn uống phiên bản. Tùy trực thuộc vào công việc của bạn, vấn đề này có thể được thực hiện bằng cách thực hiện hàm Search không sáng tỏ chữ hoa chữ hay hoặc hàm Find tất cả phân minh chữ hoa chữ hay. Một khi chúng ta có địa chỉ của lốt chia cách, thực hiện hàm RIGHT, LEFT hoặc MID để trích xuất phần khớp ứng của chuỗi văn uống phiên bản.

Để làm rõ rộng, hãy chu đáo ví dụ sau đây:

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

Để trích xuất tên mục (tất cả các cam kết tự trước vệt nối đầu tiên), cnhát phương pháp 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 phương pháp này, hàm Search xác định vị trí của dấu nối trước tiên (“-“) vào chuỗi và tác dụng LEFT đã chiết tất cả các cam kết tự còn lại (các bạn trừ 1 trường đoản cú địa chỉ của lốt nối chính vì chúng ta không muốn bao gồm dấu nối).

Để trích xuất Color (toàn bộ những ký kết từ thân những dấu gạch ốp nối thứ hai với sản phẩm công nghệ 3), hãy nhập công thức sau trong C2, cùng kế tiếp xào luộc nó xuống những ô khác:

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

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

MID (vnạp năng lượng phiên bản, start_num, num_chars)

Nơi:

Văn uống bản – vị trí nhằm trích xuất vnạp năng lượng bạn dạng từ.Start_num – vị trí của kí từ bỏ trước tiên nhằm trích xuất.Num_chars – số ký trường đoản cú để trích xuất.

Trong công thức bên trên, văn uống phiên bản được trích ra từ bỏ ô A2, cùng 2 đối số không giống được tính bằng phương pháp sử dụng 4 hàm SEARCH khác:

Số bước đầu (start_num) là vị trí của dấu nối đầu tiên +1:

SEARCH (“-“, A2) + 1

Số ký từ nhằm trích xuất (num_chars): sự khác biệt giữa địa điểm của vết nối thiết bị nhì với vệt nối trước tiên, trừ đi 1:

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

Để trích xuất kích thước (tất cả những cam kết tự sau vệt nối trang bị 3), hãy nhập bí quyết sau trong 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, từ đó các bạn trừ đi địa điểm của vệt nối vật dụng hai. Sự biệt lập là số cam kết từ sau lốt nối đồ vật hai và hàm RIGHT triết xuất chúng.

Trong một biện pháp tương tự như, chúng ta có thể phân loại cột bởi bất kỳ kí từ nào không giống. Tất cả bạn cần có tác dụng là thay thế sửa chữa “-” bằng ký từ chia cách đề nghị, ví dụ như vệt biện pháp (“”), vệt gạch men chéo cánh (“/”), vết nhì chấm (“;”), dấu chnóng phẩy (“;”) cùng vân vân.

Mẹo. Trong các phương pháp trên, +1 cùng -1 tương xứng cùng với số cam kết từ trong vết phân cách. Trong ví dụ này, nó là một trong lốt nối (1 ký tự). Nếu lốt phân làn của khách hàng bao hàm 2 ký từ, ví dụ: Dấu phẩy và khoảng chừng trắng, tiếp đến chỉ hỗ trợ vệt phẩy (“,”) đến hàm SEARCH, với sử dụng +2 cùng -2 vậy vị +1 và -1.

Làm cố làm sao nhằm phân loại chuỗi bằng cách ngắt chiếc trong Excel:

Để phân chia văn bản bởi khoảng chừng white, hãy sử dụng các cách làm tựa như như phương pháp được minch họa vào ví dụ trước. Sự khác biệt duy nhất là bạn cần tác dụng CHAR để cung cấp mang đến ký kết từ bỏ ngắt chiếc vì bạn cần thiết gõ trực tiếp vào công thức. Giả sử, những ô nhưng mà bạn muốn phân tách nhỏ trông tựa như như sau:

Lấy bí quyết trường đoản cú ví dụ trước với vắt vết gạch nối (“-“) bằng CHAR (10) trong số đó 10 là mã ASCII cho dòng cấp cho tài liệu.

Để trích xuất thương hiệu 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 cố gắng làm sao nhằm phân loại văn uống bạn dạng và số vào Excel:

Để ban đầu, không tồn tại giải pháp tổng quát mang đến toàn bộ những chuỗi chữ số. Công thức như thế nào nhằm thực hiện phụ thuộc vào mẫu mã chuỗi cụ thể. Dưới phía trên bạn sẽ tra cứu thấy cách làm mang lại 3 kịch bản thường xuyên chạm mặt độc nhất vô nhị.

Xem thêm:

lấy ví dụ như 1. Chia chuỗi của một số loại ‘văn bạn dạng + số’

Giả sử chúng ta bao gồm một cột các chuỗi cùng với văn bản với số phối hợp, trong số ấy một vài luôn luôn luôn theo sau văn bạn dạng. quý khách mong phá đổ vỡ các chuỗi lúc đầu nhằm văn phiên bản cùng số xuất hiện thêm trong số ô lẻ tẻ, như sau:

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

Công thức hoạt động như vậy nào:

Công thức để trích xuất số (hàm RIGHT). Về cơ bản, phương pháp tìm kiếm đa số số rất có thể từ 0 cho 9 vào chuỗi nguồn, tính con số và trả về nhiều ký từ từ bỏ ký tự cuối chuỗi lúc đầu.

Và đấy là phương pháp cụ thể phân rã:

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

Công thức để trích xuất vnạp năng lượng phiên bản (hàm LEFT). Quý khách hàng tính tân oán từng nào cam kết tự văn uống phiên bản chuỗi đựng bằng cách trừ số chữ số triết xuất (C2) từ bỏ chiều dài của chuỗi cội (A2). Sau kia, bạn áp dụng hàm LEFT để trả về các ký từ từ trên đầu chuỗi.

Một chiến thuật không giống (công thức không tồn tại mảng)

Giải pháp sửa chữa đã áp dụng cách làm sau nhằm xác xác định trí của số đầu tiên vào 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 cất một hằng số mảng, đó là 1 trong phương pháp thông thường được chấm dứt theo cách thường thì bằng cách dìm phím Enter.

khi địa chỉ của số trước tiên được tìm kiếm thấy, chúng ta cũng có thể bóc văn bản với số bằng phương pháp áp dụng các cách làm LEFT và RIGHT cực kỳ đơn giản dễ dàng (nhớ là một số trong những luôn luôn xuất hiện thêm 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 đúng theo A2 là chuỗi ban sơ, và B2 là địa chỉ của số thứ nhất, như biểu hiện trong hình dưới đây:

Để loại bỏ cột helper giữ lại vị trí số bắt đầu, bạn có thể nhúng hàm MIN vào những hàm LEFT cùng 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 & “0123456789”)) + 1)

Công thức tính toán vị trí của số sản phẩm nhất

quý khách hàng hỗ trợ 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, tạo nên nó tìm kiếm từng số vào hằng số mảng bên phía trong bạn dạng nơi bắt đầu, với trả lại địa chỉ của chúng. Bởi bởi hằng số mảng đựng 10 chữ số, mảng công dụng cũng cất 10 mục.

Hàm MIN lấy mảng kết quả cùng trả về cực hiếm nhỏ tuổi tuyệt nhất, khớp ứng với địa điểm của số thứ nhất vào chuỗi ban sơ.

Trong khi, Cửa Hàng chúng tôi sử dụng một cấu trúc đặc biệt quan trọng (A2 và “0123456789”) để ghxay mỗi số có thể cùng với chuỗi ban sơ. Cách này thực hiện vai trò của IFERROR cùng được cho phép công ty chúng tôi tách lỗi Khi một trong những cố định trong hằng số mảng ko được tìm thấy trong chuỗi mối cung cấp. Trong trường hòa hợp này, bí quyết trả về địa điểm “mang mạo” bằng chuỗi chiều lâu năm từ 1 cam kết từ bỏ trsinh hoạt lên. Vấn đề này có thể chấp nhận được hàm LEFT trích xuất văn uống bạn dạng với hàm RIGHT trả về một chuỗi rỗng nếu như chuỗi cội không cất ngẫu nhiên số như thế nào, như vào cái 7 hình sinh sống bên trên.

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

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

Và bởi vì 7 là quý hiếm nhỏ dại tốt nhất trong mảng công dụng, vày hàm MIN trả về, với chúng ta nhận ra địa điểm của số thứ nhất (0) trong chuỗi vnạp năng lượng bạn dạng ban đầu.

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

Nếu nhiều người đang tách các ô địa điểm vnạp năng lượng phiên bản xuất hiện sau một số, chúng ta cũng có thể trích xuất những số cùng với công thức mảng này (xong 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 công thức mảng tự ví dụ trước, ngoại trừ chúng ta thực hiện hàm LEFT nắm do RIGHT, bởi vì vào ngôi trường thích hợp này số luôn luôn xuất hiện sinh sống phía phía trái của chuỗi. Một Khi bạn đã sở hữu các số lượng, trích xuất vnạp năng lượng bản bằng phương pháp 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 các phương pháp bên trên, A2 là chuỗi ban sơ và B2 là số trích xuất, nlỗi biểu thị trong hình bên dưới đây:

Ví dụ 3. Trích xuất chỉ số tự chuỗi số ‘số vnạp năng lượng bản’

Nếu công việc của bạn đòi hỏi buộc phải trích xuất toàn bộ những số xuất phát từ 1 chuỗi vào format ‘number-text-number’, bạn cũng có thể thực hiện cách làm sau đây được gợi nhắc 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ẾP (“1:” & LEN (A2) (1: “và 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 thuở đầu.