Trong bài viết này, thuychien.vn đang giải thích cách bóc những ô vào Excel bằng công thức. Bạn đang học tập phương pháp bóc văn uống bạn dạng dựa vào vệt phẩy, khoảng trắng hoặc bất kỳ vệt ngăn cách nào không giống, cùng có tác dụng thế như thế nào nhằm phân loại chuỗi thành vnạp năng lượng bản với số.

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


Làm cố làm sao để phân chia văn bản vào Excel bằng cách sử dụng công thức:

Để bóc chuỗi trong Excel xuất xắc bóc chữ và số vào excel, chúng ta thường xuyên thực hiện hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. ban sơ, một vài phương pháp có thể phức tạp, dẫu vậy thực tế lô ghích là tương đối dễ dàng, cùng các ví dụ tiếp sau đây sẽ hỗ trợ cho bạn một số mai dong.

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

lúc phân loại những ô vào Excel, vấn đề đó là xác định vị trí của lốt chia cách vào chuỗi vnạp năng lượng bản. Tùy ở trong vào công việc của công ty, điều này rất có thể được thực hiện bằng cách thực hiện hàm Search ko khác nhau chữ hoa chữ hay hoặc hàm Find tất cả minh bạch chữ hoa chữ thường xuyên. Một khi bạn gồm vị trí của dấu phân cách, thực hiện hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi văn bạn dạng.

Để làm rõ hơn, hãy để ý ví dụ sau đây:

Giả sử chúng ta gồm một list những SKU của mẫu Loại-Màu-Kích thước, cùng bạn muốn phân 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ý kết từ trước lốt nối đầu tiên), cnhát cách làm sau trong B2, cùng kế tiếp 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 trước tiên (“-“) trong chuỗi cùng tính năng LEFT vẫn phân tách tất cả những cam kết tự sót lại (bạn trừ 1 từ địa chỉ của vệt nối bởi vì chúng ta không thích bao gồm lốt nối).

*

Để trích xuất màu sắc (tất cả những cam kết từ bỏ giữa các lốt gạch ốp nối thứ 2 và vật dụng 3), hãy nhập phương pháp sau vào C2, với sau đó sao chép nó xuống các ô khác:

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

*

Nhỏng chúng ta 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:

Vnạp năng lượng phiên bản – nơi nhằm trích xuất vnạp năng lượng phiên bản tự.Start_num – địa chỉ của kí từ bỏ đầu tiên để trích xuất.Num_chars – số ký 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ông giống được xem bằng cách thực hiện 4 hàm SEARCH khác:

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

SEARCH (“-“, A2) + 1

Số ký tự nhằm trích xuất (num_chars): sự khác hoàn toàn thân địa chỉ của vết nối thiết bị nhì cùng 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 (toàn bộ các ký kết tự sau vết nối máy 3), hãy nhập cách làm sau vào D2:

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

Trong bí quyết này, hàm LEN trả về tổng chiều dài của chuỗi, trường đoản cú đó bạn trừ đi vị trí của dấu nối sản phẩm nhì. Sự khác biệt là số ký từ bỏ sau lốt nối lắp thêm hai cùng hàm RIGHT chiết xuất bọn chúng.

*
Trong một bí quyết tựa như, bạn có thể phân chia cột vì chưng ngẫu nhiên kí từ bỏ nào khác. Tất cả các bạn yêu cầu làm cho là sửa chữa “-” bởi cam kết từ bỏ phân làn bắt buộc, ví như lốt biện pháp (“”), lốt gạch men chéo (“/”), vệt nhị chấm (“;”), vệt chấm phẩy (“;”) với vân vân.

Mẹo. Trong các cách làm bên trên, +1 và -1 khớp ứng với số cam kết trường đoản cú vào dấu phân làn. Trong ví dụ này, nó là 1 trong vết nối (1 ký kết tự). Nếu vệt phân cách của người tiêu dùng bao gồm 2 ký kết tự, ví dụ: Dấu phẩy và khoảng tầm white, kế tiếp chỉ cung cấp vết phẩy (“,”) cho hàm SEARCH, cùng áp dụng +2 và -2 thế vì +1 với -1.

Làm vậy nào nhằm phân loại chuỗi bằng cách ngắt loại vào Excel:

Để chia vnạp năng lượng bạn dạng bằng khoảng trắng, hãy sử dụng các công thức tương tự nhỏng công thức được minc họa trong ví dụ trước. Sự khác hoàn toàn độc nhất là bạn cần chức năng CHAR để cung cấp mang đến ký từ bỏ ngắt chiếc vì chúng ta tất yêu gõ thẳng vào cách làm. Giả sử, các ô mà lại bạn có nhu cầu phân chia nhỏ tuổi trông tương tự như nhỏng sau:

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

Để trích xuất tên khía cạnh hàng:

=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)

Để trích xuất color 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ỗ lực làm sao để phân chia văn uống phiên bản và số trong Excel:

Để bước đầu, không tồn tại giải pháp tổng quát đến toàn bộ các chuỗi chữ số. Công thức nào nhằm sử dụng nhờ vào vào chủng loại chuỗi cụ thể. Dưới trên đây các bạn sẽ tìm thấy công thức đến 3 kịch bạn dạng thường gặp gỡ nhất.

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

Giả sử bạn gồm một cột các chuỗi với văn bạn dạng và số kết hợp, trong đó một trong những luôn luôn theo sau văn phiên bản. Bạn mong muốn phá đổ vỡ các chuỗi lúc đầu nhằm văn bản và số mở ra trong những ô đơn nhất, như sau:

*

Để trích xuất những số, sử dụng phương pháp mảng dưới đây, được ngừng bằng phương pháp dìm 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 lúc đầu, với C2 là số trích xuất, như diễn tả vào hình bên dưới đây:

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

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

Và đấy là cách làm chi tiết phân rã:

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

Một chiến thuật khác (phương pháp không có mảng)

Giải pháp thay thế sẽ sử dụng phương pháp 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 và “0123456789”))

Mặc mặc dù bí quyết cũng cất một hằng số mảng, kia là 1 phương pháp thông thường được hoàn thành theo cách thường thì bằng phương pháp dấn phím Enter.

Lúc địa chỉ của số đầu tiên được kiếm tìm thấy, chúng ta có thể tách bóc văn uống bản và số bằng phương pháp sử dụng những bí quyết LEFT cùng RIGHT cực kỳ dễ dàng và đơn giản (nhớ rằng một số luôn mở ra sau vnạp năng lượng 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ợp A2 là chuỗi thuở đầu, với B2 là địa chỉ của số đầu tiên, như bộc lộ vào hình bên dưới đây:

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

Công thức trích xuất văn uống bản:

= 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 các 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 tân oán vị trí của số đồ vật nhất

Quý khách hàng 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, tạo nên nó tìm kiếm từng số trong hằng số mảng bên trong bạn dạng cội, cùng trả lại vị trí của bọn chúng. Bởi vì chưng hằng số mảng cất 10 chữ số, mảng công dụng cũng cất 10 mục.

Hàm MIN mang mảng hiệu quả cùng trả về giá trị nhỏ duy nhất, tương xứng cùng với vị trí của số thứ nhất trong chuỗi thuở đầu.

Bên cạnh đó, công ty chúng tôi thực hiện một cấu trúc quan trọng đặc biệt (A2 & “0123456789”) nhằm ghnghiền mỗi số rất có thể cùng với chuỗi ban đầu. Cách này tiến hành phương châm của IFERROR cùng cho phép chúng tôi tách lỗi Khi một số một mực trong hằng số mảng ko được tra cứu thấy trong chuỗi mối cung cấp. Trong trường thích hợp này, công thức trả về địa điểm “trả mạo” bởi chuỗi chiều lâu năm từ là 1 ký kết trường đoản cú trngơi nghỉ lên. Như vậy có thể chấp nhận được hàm LEFT trích xuất văn uống bạn dạng cùng 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ố như thế nào, nlỗi trong chiếc 7 hình làm việc bên trên.

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

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

*
Và cũng chính vì 7 là quý giá nhỏ độc nhất vô nhị vào mảng công dụng, vị hàm MIN trả về, với chúng ta nhận thấy địa điểm của số thứ nhất (0) vào chuỗi văn uống phiên bản thuở đầu.

Xem thêm: Học Cách Làm Giàu Từ Người Giàu Có Nhất Thành Babylon" %, Học Cách Làm Giàu Từ Kinh Doanh & Đầu Tư

lấy ví dụ như 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 bóc những ô địa điểm văn phiên bản mở ra sau một trong những, chúng ta có thể trích xuất các số với phương pháp mảng này (xong xuôi bằng phương pháp 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ư nhỏng bí quyết mảng trường đoản cú ví dụ trước, bên cạnh chúng ta sử dụng hàm LEFT cố gắng bởi vì RIGHT, chính vì vào trường vừa lòng này số luôn lộ diện nghỉ ngơi phía phía trái của chuỗi. Một lúc bạn đã sở hữu 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 dài của chuỗi gốc:

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

Trong các công thức bên trên, A2 là chuỗi lúc đầu cùng 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ố trường đoản cú chuỗi số ‘số vnạp năng lượng bản’

Nếu quá trình của bạn đòi hỏi yêu cầu trích xuất toàn bộ các số xuất phát từ 1 chuỗi trong định hình ‘number-text-number’, bạn cũng có thể sử dụng bí quyết dưới đây được lưu ý bởi vì một trong số 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: “và LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “và LEN (A2))) / 10)

Trường đúng theo A2 là chuỗi văn uống bản thuở đầu.

*