Hàm tách chữ ra khỏi chuỗi trong excel

     

Trong nội dung bài viết này, colonyinvest.net sẽ giải thích cách bóc tách các ô vào Excel bởi công thức. Bạn sẽ học cách bóc tách văn bản dựa theo dấu phẩy, khoảng chừng trắng hoặc ngẫu nhiên dấu ngăn cách nào khác, cùng làm nuốm nào để phân chia chuỗi thành văn bản và số.

Bạn đang xem: Hàm tách chữ ra khỏi chuỗi trong excel


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

Để tách bóc chuỗi trong Excel hay tách bóc chữ cùng 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. Dịp đầu, một vài 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ụ dưới đây sẽ cung cấp cho mình một số đầu mối.

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

Khi phân chia các ô trong Excel, việc chính là xác định vị trí của dấu chia cách trong chuỗi văn bản. Tùy ở trong vào công việc của bạn, điều này rất có thể được thực hiện bằng phương pháp sử dụng hàm search không tách biệt chữ hoa chữ thường hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi chúng ta có vị trí của vệt phân cách, thực hiện hàm RIGHT, LEFT hoặc MID nhằm trích xuất phần khớp ứng của chuỗi văn bản.

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

Giả sử chúng ta có một danh sách các SKU của chủng loại Loại-Màu-Kích thước, và bạn có nhu cầu chia tách bóc 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ý từ bỏ trước vết nối đầu tiên), chèn công thức sau vào B2, và sau đó coppy nó xuống cột:

= LEFT (A2, tìm kiếm (“-“, 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 (“-“) vào chuỗi và chức năng LEFT vẫn chiết toàn bộ các ký kết tự còn lại (bạn trừ 1 từ vị trí của vệt nối chính vì bạn không muốn có vết nối).

*

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

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

Nơi:

Văn bạn dạng – chỗ để trích xuất văn phiên bản 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ách làm trên, văn bản được trích ra từ bỏ ô A2, và 2 đối số khác được tính bằng cách sử dụng 4 hàm tìm kiếm khác:

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

SEARCH (“-“, A2) + 1

Số ký kết tự để trích xuất (num_chars): sự khác hoàn toàn giữa địa chỉ của lốt nối đồ vật hai cùng dấu nối đầu tiên, trừ đi 1:

SEARCH (“-“, A2, search (“-“, A2) +1) – tìm kiếm (“-“, A2) -1

Để trích xuất form size (tất cả các ký tự sau vết nối thứ 3), hãy nhập bí quyết sau vào D2:

= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, A2, tìm kiếm (“-“, A2) + 1))

Trong công thức này, hàm LEN trả về tổng chiều lâu năm của chuỗi, từ bỏ đó bạn trừ đi địa chỉ của lốt nối đồ vật hai. Sự khác biệt là số ký tự sau dấu nối sản phẩm hai và hàm RIGHT chiết xuất chúng.

*
Trong một bí quyết 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ự như thế nào khác. Toàn bộ bạn phải làm là thay thế “-” bởi ký tự ngăn cách bắt buộc, ví như dấu biện pháp (“”), vệt gạch chéo (“/”), vết hai chấm (“;”), vệt chấm phẩy (“;”) với vân vân.

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

Làm cầm cố nào để phân chia chuỗi bằng phương pháp ngắt mẫu trong Excel:

Để chia văn phiên bản bằng khoảng trắng, hãy sử dụng các công thức tương tự như cách làm được minh họa trong ví dụ như trước. Sự khác hoàn toàn duy tốt nhất là các bạn cần công dụng CHAR để cung cấp cho ký kết tự ngắt cái vì các bạn không thể gõ thẳng vào công thức. Giả sử, những ô mà bạn muốn chia nhỏ tuổi trông tương tự như sau:

*
Lấy cách làm từ lấy ví dụ như trước và cố kỉnh dấu gạch nối (“-“) bởi CHAR (10) trong số đó 10 là mã ASCII cho cái cấp dữ 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 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 nuốm nào để phân loại văn bạn dạng và số trong Excel:

Để bắt đầu, ko có phương án tổng quát lác cho toàn bộ các chuỗi chữ số. Công thức nào nhằm sử dụng phụ thuộc vào mẫu mã chuỗi thay thể. Dưới đây các bạn sẽ tìm thấy phương pháp cho 3 kịch bạn dạng thường chạm chán nhất.

Xem thêm: Cách Copy Từ Cad Sang Word Không Bị Lỗi Font Hiệu Quả Nhất 2022

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

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

*

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

*
Công thức chuyển động như gắng nào:

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

Và đây là công thức cụ thể phân rã:

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

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

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

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

Khi vị trí của số thứ nhất được search thấy, bạn có thể tách văn bản và số bằng cách sử dụng các công thức LEFT với RIGHT rất đơn giản (nhớ rằng một số 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à B2 là địa điểm của số đầu tiên, như biểu đạt trong hình bên dưới đây:

*
Để đào thải cột helper giữ địa điểm số bắt đầu, chúng ta có thể nhúng hàm MIN vào các hàm LEFT và 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 & “0123456789”)) + 1)

Công thức tính toán vị trí của số vật dụng nhất

Bạn 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ó search từng số trong hằng số mảng mặt trong bạn dạng gốc, với trả lại địa chỉ của chúng. Bởi vì hằng số mảng cất 10 chữ số, mảng hiệu quả cũng cất 10 mục.

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

Ngoài ra, shop chúng tôi sử dụng một cấu tạo đặc biệt (A2 & “0123456789”) nhằm ghép mỗi số hoàn toàn có thể với chuỗi ban đầu. Cách này triển khai vai trò của IFERROR và cho phép chúng tôi né lỗi khi một vài nhất định vào hằng số mảng ko được kiếm tìm thấy trong chuỗi nguồn. Vào trường hợp này, cách làm trả về địa điểm “giả mạo” bằng chuỗi chiều dài từ là 1 ký từ trở lên. Điều này có thể chấp nhận được 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 sống 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à đây là cách chúng tôi có:

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

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

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

Nếu bạn đang tách các ô địa điểm văn bạn dạng xuất hiện nay sau một số, chúng ta 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ương tự như phương pháp mảng từ lấy ví dụ như trước, bên cạnh bạn sử dụng hàm LEFT thay vị RIGHT, chính vì trong trường hòa hợp này số luôn xuất hiện ở phía phía trái của chuỗi. Một khi bạn đã sở hữu các 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 trên, A2 là chuỗi ban sơ và B2 là số trích xuất, như bộc lộ 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ố văn bản’

Nếu quá trình của bạn yên cầu phải trích xuất tất cả các số xuất phát từ 1 chuỗi trong format ‘number-text-number’, bạn cũng có thể sử dụng công thức sau đây được gợi ý bởi 1 trong những những chuyên viên 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 hợp A2 là chuỗi văn bản ban đầu.

*