CÁCH TÁCH CHỮ TRONG EXCEL

Trong nội dung bài viết này, my-huawei.com sẽ lý giải cách 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 bản dựa theo lốt phẩy, khoảng tầm trắng hoặc bất kỳ dấu ngăn cách nào khác, và làm cố nào để phân chia chuỗi thành văn bản và số.

Bạn đang xem: Cách tách chữ trong excel


Làm thế nào để chia văn bản trong Excel bằng cách sử dụng công thức:

Để bóc tách chuỗi vào Excel hay bóc chữ và số trong excel, chúng ta thường sử dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. Lúc đầu, một số công thức rất có thể phức tạp, nhưng thực tế logic là khá 1-1 giản, và các ví dụ sau đây sẽ cung cấp cho bạn một số đầu mối.

Tách chuỗi bằng dấu phẩy, vết hai chấm, vệt gạch chéo, dấu gạch ngang hoặc dấu ngăn cách khác

Khi phân chia các ô vào Excel, việc đó là xác định vị trí của dấu ngăn cách trong chuỗi văn bản. Tùy nằm trong vào các bước của bạn, điều này rất có thể được thực hiện bằng cách sử dụng hàm tìm kiếm không sáng tỏ chữ hoa chữ thường hoặc hàm Find bao gồm phân biệt chữ hoa chữ thường. Một khi bạn có địa chỉ của lốt phân cách, sử 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.

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

Giả sử bạn có một danh sách những SKU của mẫu mã Loại-Màu-Kích thước, và bạn muốn chia tách 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ý trường đoản cú trước vết nối đầu tiên), chèn phương pháp sau trong B2, cùng sau đó coppy nó xuống cột:

= LEFT (A2, tìm kiếm (“-“, A2,1) -1)

Trong phương pháp này, hàm tìm kiếm xác xác định trí của vệt nối đầu tiên (“-“) vào chuỗi và công dụng LEFT đang chiết tất cả các ký tự còn lại (bạn trừ 1 từ vị trí của lốt nối cũng chính vì bạn không thích có vết nối).

*

Để trích xuất color (tất cả các ký từ bỏ giữa những dấu gạch men nối thứ 2 và sản phẩm công nghệ 3), hãy nhập công thức sau vào C2, với sau đó 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ư chúng ta có thể biết, hàm MID bao gồm cú pháp sau:

MID (văn bản, start_num, num_chars)

Nơi:

Văn phiên bản – khu vực để trích xuất văn bạn dạng từ.Start_num – địa chỉ của kí tự thứ nhất để trích xuất.Num_chars – số ký kết tự để trích xuất.

Trong công thức trên, văn bản được trích ra từ ô A2, với 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ắt đầu (start_num) là địa chỉ của dấu nối thứ nhất +1:

SEARCH (“-“, A2) + 1

Số ký tự nhằm trích xuất (num_chars): sự biệt lập giữa vị trí của vệt nối vật dụng hai cùng dấu 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ừ sau lố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) – tìm kiếm (“-“, A2, tìm kiếm (“-“, 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ỏ đó chúng ta trừ đi địa chỉ của lốt nối đồ vật hai. Sự biệt lập là số ký tự sau lốt nối trang bị hai và hàm RIGHT triết xuất chúng.

*
Trong một biện pháp tương tự, chúng ta cũng có thể phân phân tách cột bởi ngẫu nhiên kí tự nào khác. Toàn bộ bạn đề nghị làm là sửa chữa thay thế “-” bởi ký tự phân làn bắt buộc, ví như dấu bí quyết (“”), vệt gạch chéo cánh (“/”), vệt hai chấm (“;”), vết chấm phẩy (“;”) với vân vân.

Mẹo. Trong số công thức trên, +1 với -1 khớp ứng với số cam kết tự trong lốt phân cách. Trong lấy một ví dụ này, nó là 1 trong những dấu nối (1 ký tự). Nếu như dấu chia cách của bạn bao hàm 2 ký kết tự, ví dụ: lốt phẩy và khoảng tầm trắng, tiếp đến chỉ hỗ trợ dấu phẩy (“,”) cho hàm SEARCH, và thực hiện +2 và -2 thay vì +1 và -1.

Làm thế nào để phân loại chuỗi bằng phương pháp ngắt chiếc trong Excel:

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

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

Để trích xuất tên phương diện hàng:

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

Để trích xuất màu 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 để phân loại văn phiên bản và số trong Excel:

Để bắt đầu, ko có phương án tổng quát cho tất cả các chuỗi chữ số. Bí quyết nào nhằm sử dụng nhờ vào vào mẫu mã chuỗi cầm thể. Dưới đây bạn sẽ tìm thấy phương pháp cho 3 kịch bản thường gặp mặt nhất.

Ví dụ 1. Chia chuỗi của các loại ‘văn phiên bản + số’

Giả sử bạn có một cột các chuỗi với văn phiên bản và số kết hợp, trong những số ấy một số luôn luôn theo sau văn bản. Bạn muốn phá vỡ những chuỗi ban đầu để văn bạn dạng và số xuất hiện thêm trong những ô 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 hoàn thành 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 bản, sử dụng:

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

Trường vừa lòng A2 là chuỗi ban đầu, cùng C2 là số trích xuất, như mô tả trong hình bên dưới đây:

*
Công thức chuyển động như núm 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 hồ hết số rất có thể từ 0 mang lại 9 trong chuỗi nguồn, tính số lượng và trả về những ký tự từ cam kết tự cuối chuỗi ban đầu.

Và đấy là công thức chi tiết phân rã:

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

Công thức nhằm trích xuất văn bạn dạng (hàm LEFT). Bạn đo lường và tính toán bao nhiêu cam kết tự văn bạn dạng chuỗi chứa bằng phương pháp trừ số chữ số chiết xuất (C2) tự chiều dài của chuỗi gốc (A2). Sau đó, bạn thực hiện hàm LEFT nhằm trả về nhiều ký tự từ đầu chuỗi.

Một giải pháp khác (công thức không tồn tại mảng)

Giải pháp thay thế sửa chữa sẽ thực hiện công thức sau để xác định vị 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 dù cách làm cũng chứa một hằng số mảng, đó là một công thức bình thường được chấm dứt theo biện pháp thông thường bằng cách nhấn phím Enter.

Khi địa điểm của số thứ nhất được tìm thấy, bạn cũng có thể tách văn bản và số bằng cách sử dụng những công thức LEFT cùng RIGHT rất dễ dàng và đơn giản (nhớ rằng một trong những luôn lộ diện 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 phù hợp A2 là chuỗi ban đầu, với B2 là địa điểm của số đầu tiên, như mô tả trong hình bên dưới đây:

*
Để loại bỏ cột helper giữ địa điểm số bắt đầ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 văn 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 thống kê giám sát vị trí của số máy nhất

Bạn cung cấp 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 cho nó tìm từng số vào 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. Bởi vì hằng số mảng cất 10 chữ số, mảng tác dụng cũng cất 10 mục.

Hàm MIN đem mảng tác dụng và trả về giá bán trị nhỏ tuổi nhất, tương ứng với địa điểm của số trước tiên trong chuỗi ban đầu.

Ngoài ra, cửa hàng 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. Biện pháp này thực hiện vai trò của IFERROR và chất nhận được chúng tôi né lỗi khi một số nhất định vào hằng số mảng không được tìm kiếm thấy vào chuỗi nguồn. Vào trường vừa lòng này, bí quyết trả về vị trí “giả mạo” bởi chuỗi chiều dài từ là một ký tự trở lên. Điều này được cho phép hàm LEFT trích xuất văn bạn dạng và hàm RIGHT trả về một chuỗi rỗng ví như chuỗi gốc không chứa bất kỳ số nào, như trong cái 7 hình ở 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à đó là cách shop chúng tôi có:

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

*
Và bởi vì 7 là giá trị bé dại nhất trong mảng kết quả, bởi hàm MIN trả về, và họ nhận được vị trí của số đầu tiên (0) trong chuỗi văn bạn dạng ban đầu.

Xem thêm: 1500 Hình Xăm Cá Chép Nên Xăm Ở Đâu, 1500 Hình Xăm Cá Chép Đẹp, Ý Nghĩa Nhất

Ví dụ 2. Phân chia chuỗi của các loại ‘số + văn bản’

Nếu chúng ta đang bóc tách các ô vị trí văn bạn dạng xuất hiện tại 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 (hoàn thành 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ựa như như công thức mảng từ lấy ví dụ như trước, xung quanh bạn thực hiện hàm LEFT thay vị RIGHT, chính vì trong trường vừa lòng này số luôn mở ra ở phía phía trái của chuỗi. Một khi bạn đã có các con số, trích xuất văn phiên bản bằng bí quyết trừ số chữ số từ bỏ 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 lúc đầu và B2 là số trích xuất, như biểu hiện trong hình dưới đây:

*

Ví dụ 3. Trích xuất chỉ số tự chuỗi số ‘số văn bản’

Nếu các bước của bạn đòi hỏi phải trích xuất tất cả các số xuất phát từ một chuỗi trong định dạng ‘number-text-number’, chúng ta có thể sử dụng công thức tiếp sau đây được gợi nhắc bởi một trong các 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: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)

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

*

table('setting')->where("{$db->web}")->select('code_footer'); if($oh->code_footer){ # nếu có code header tùy chỉnh $code_footer = htmlspecialchars_decode($oh->code_footer); $code_footer = str_replace('[home_link]', $home, $code_footer); $code_footer = str_replace('[home_name]', $h, $code_footer); $code_footer = str_replace('[link]', $link, $code_footer); $code_footer = str_replace('[title]', $head->tit, $code_footer); $code_footer = str_replace('[des]', $head->des, $code_footer); $code_footer = str_replace('[key]', $head->key, $code_footer); $code_footer = str_replace('[image]', $head->img, $code_footer); $code_footer = str_replace('[link]', $link, $code_footer); $code_footer = str_replace('[date_Y]', date('Y'), $code_footer); echo $code_footer; } ?>