Trong bài viết này, blog.hocexcel.online sẽ lý giải cách tách bóc các ô trong Excel bằng công thức. Các bạn sẽ học cách bóc văn bạn dạng dựa theo vết phẩy, khoảng trắng hoặc bất kỳ dấu ngăn cách nào khác, và làm cố kỉnh nào để phân loại chuỗi thành văn bản và số.

Bạn đang xem: Hàm lấy chuỗi ký tự trong excel


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

Để tách chuỗi vào Excel hay tách bóc chữ với số trong excel, chúng ta thường áp 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ố trong những công thức có thể phức tạp, nhưng thực tiễn logic là khá đơn giản, và những ví dụ tiếp sau đây sẽ cung cấp cho mình một số đầu mối.

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

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

Để hiểu rõ hơn, hãy xem xét 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 muốn chia tách bóc cột thành 3 cột riêng biệt:

*


*

*

Để trích xuất tên mục (tất cả các ký trường đoản cú trước vết nối đầu tiên), chèn công thức sau vào B2, với 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 tìm kiếm xác định vị trí của vệt nối trước tiên (“-“) vào chuỗi và tính năng LEFT đã chiết toàn bộ các ký tự sót lại (bạn trừ 1 từ vị trí của dấu nối chính vì bạn không muốn có dấu nối).

*

Để trích xuất màu sắc (tất cả những ký từ bỏ giữa các dấu gạch ốp nối thứ hai và lắp thêm 3), hãy nhập bí quyết sau trong 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 – nơi để trích xuất văn bạn dạng từ.Start_num – vị trí của kí tự thứ nhất để trích xuất.Num_chars – số cam kết tự để trích xuất.

Trong công thức trên, văn phiên bản được trích ra trường đoản cú ô A2, với 2 đối số khác đượ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 lốt nối thứ nhất +1:

SEARCH (“-“, A2) + 1

Số ký kết tự để trích xuất (num_chars): sự biệt lập giữa vị trí của dấu nối trang bị hai với dấu nối đầu tiên, trừ đi 1:

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

Để trích xuất size (tất cả các ký trường đoản cú sau dấu nối sản phẩm 3), hãy nhập cách làm sau vào D2:

= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, 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ạn trừ đi địa điểm của lốt nối trang bị hai. Sự khác biệt là số cam kết tự sau lốt nối đồ vật hai cùng hàm RIGHT triế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 chia cột bởi ngẫu nhiên kí tự làm sao khác. Tất cả bạn phải làm là sửa chữa thay thế “-” bằng ký tự chia cách bắt buộc, ví như dấu cách (“”), lốt gạch chéo (“/”), vệt hai chấm (“;”), vết chấm phẩy (“;”) và vân vân.

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

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

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

*
Lấy phương pháp từ ví dụ trước và cầm cố dấu gạch nối (“-“) bởi CHAR (10) trong đó 10 là mã ASCII cho cái cấp dữ 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:

=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à đó là kết quả:

*

Làm gắng nào để phân loại văn bạn dạng và số vào Excel:

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

Ví dụ 1. Phân tách chuỗi của một số loại ‘văn phiên bản + số’

Giả sử các bạn có một cột các chuỗi cùng với văn bạn dạng và số kết hợp, trong đó một số luôn 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ố lộ diện trong những ô riêng rẽ biệt, như sau:

*

Để trích xuất các số, thực hiện công thức mảng sau đây, được hoàn thành bằng cách 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, với C2 là số trích xuất, như diễn tả trong hình bên dưới đây:

*
Công thức vận động như cầm nào:

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bản, phương pháp tìm kiếm phần lớn số hoàn toàn có thể từ 0 mang lại 9 vào chuỗi nguồn, tính số lượng và trả về nhiều ký tự từ ký 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à SUBSTITUTE để tìm ra số lần mở ra một số nào kia trong chuỗi nơi bắt đầu – thay thế sửa chữa số bằng một chuỗi trống rỗng (“”), và sau đó trừ đi chiều nhiều năm của chuỗi mà không tồn tại số đó từ toàn bô Chiều nhiều năm của chuỗi ban đầu. Cũng chính vì đó là một công thức mảng, làm việc này được thực hiện 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 xuất hiện của toàn bộ các chữ số vào chuỗi nguồn.Cuối cùng, hàm RIGHT trả về nhiều ký tự từ phía bên đề nghị của chuỗi.

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

Một chiến thuật khác (công thức không có mảng)

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

Khi địa điểm của số trước tiên được kiếm 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 với RIGHT rất dễ dàng và đơn giản (nhớ rằng một vài luôn xuất hiện thêm 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 hòa hợp A2 là chuỗi ban đầu, với B2 là vị trí của số đầu tiên, như biểu đạt trong hình bên dưới đây:

*
Để loại bỏ cột helper giữ địa chỉ số bắt đầu, bạn 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 & “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 & “0123456789”)) + 1)

Công thức đo lường và thống kê vị trí của số trang bị 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, làm cho nó tìm kiếm từng số trong hằng số mảng bên trong phiên bản gốc, cùng trả lại địa chỉ của chúng. Cũng chính vì hằng số mảng đựng 10 chữ số, mảng tác dụng cũng đựng 10 mục.

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

Ngoài ra, công ty chúng tôi sử dụng một kết cấu đặc biệt (A2 và “0123456789”) để ghép từng số có thể với chuỗi ban đầu. Biện pháp này thực hiện vai trò của IFERROR và cho phép chúng tôi tránh lỗi khi một trong những nhất định trong hằng số mảng ko được search thấy vào chuỗi nguồn. Vào trường đúng theo này, bí quyết trả về địa chỉ “giả mạo” bằng chuỗi chiều dài từ một ký từ bỏ trở lên. Điều này được cho phép hàm LEFT trích xuất văn bản và 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ố nào, như trong dòng 7 hình nghỉ ngơi 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à đấy là cách cửa hàng chúng tôi có:

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

Xem thêm: Sách One Punch Man Tập 10 - Phàn2 Phim Thánh Phồng Tôm

*
Và bởi vì 7 là giá chỉ trị nhỏ tuổi nhất trong mảng kết quả, vì hàm MIN trả về, và chúng ta nhận được địa chỉ 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 loại ‘số + văn bản’

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

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

Trong những công thức trên, A2 là chuỗi thuở đầu và B2 là số trích xuất, như thể 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 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ừ một chuỗi trong format ‘number-text-number’, chúng ta cũng có thể sử dụng công thức sau đây được lưu ý bởi một trong những những chuyên viên của Mr
Excel:

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

Trường hòa hợp A2 là chuỗi văn bản ban đầu.

*

Khi bạn làm việc với những dữ liệu bao gồm dấu phẩy hay khoảng tầm trắng thì rất nhiều lúc bọn họ cần tách bóc những tài liệu đó ra để hoàn toàn có thể phân loại hay thống kê. Nếu như dữ liệu không quá nhiều thì bạn có thể tách dữ liệu bằng phương pháp thủ công, nhưng trái lại nếu dữ liệu rất nhiều thì các bạn sẽ làm nhự vắt nào? nội dung bài viết này sẽ hướng dẫn các bạn cách tách chuỗi văn bản vào Excel theo vệt phẩy và khoảng trắng.

*

Dùng hàm LEFT

Nếu bạn muốn lấy được chuỗi ký kết tự tính tự đầu cho tới vị trí của vết phẩy trước tiên của dữ liệu trong ô tham số có nghĩa là số ký kết tự nằm tại vị trí phía mặt trái, bởi vì vậy bạn cũng có thể sử dụng hàm LEFT kết hợp với hàm search để tách bóc dữ liệu.

Công thức của nhị hàm đó như sau:

=LEFT(ô tham số gốc;số ký kết tự đề xuất lấy chuỗi)

Trong đó:

Ô tham số gốc là ô ký kết tự gốc các bạn sẽ lấy ký kết tự ra từ nó.

Số ký kết tự bắt buộc lấy chuỗi là số ký tự tính từ ký kết tự thứ nhất của chuỗi ký kết tự trong ô tham số gốc.

=SEARCH(“ký tự hoặc nhiều ký tự đề nghị tìm kiếm”;”ô tham số gốc để tra cứu kiếm”;”bắt đầu kiếm tìm kiếm từ ký kết tự số bao nhiêu”)

Trong đó:

Ký tự hoặc cụm ký tự đề xuất tìm kiếm ở trường vừa lòng này sẽ là dấu phẩy hoặc che dấu cách dẫu vậy khi nhập cam kết tự vào chúng ta lưu ý bắt buộc cho ký tự hoặc nhiều ký tự kia vào trong lốt ngoặc kép. 

Ô tham số gốc để tìm kiếm là ô ký kết tự gốc bạn sẽ lấy cam kết tự ra từ nó.

Bắt đầu tìm kiếm kiếm từ ký kết tự số bao nhiêu bạn cũng có thể nhập là 1 trong những để nó sẽ ban đầu đếm từ ký kết tự đầu tiên khi bắt đầu tìm ký tự hoặc nhiều ký tự cần tìm kiếm.

Và 2 hàm trên sau khi kết họp với nhau thì chúng ta cũng có thể tách được chuỗi văn bản có tác dụng là các ký tự trường đoản cú đầu tính đến vị trí vết phẩy. Nếu như khách hàng không mong kết quả bao gồm cả luôn dấu cách hoặc có thể dấu phẩy kia bọn họ cần cần thêm -1 ở phía sau công thức SEARCH.

Ví dụ rõ ràng như sau:

Công thức ví dụ của hàm:

=LEFT(A2,SEARCH(“,”,A2,1)-1)

*

Nếu như bạn có nhu cầu tách chuỗi văn bản theo khoảng tầm trắng thì bạn chỉ cần thay thế dấu “,” trong phương pháp thành khoảng tầm trắng “ “. Công thức rõ ràng như sau:

=LEFT(A2,SEARCH(” “,A2,1)-1)

*

Dùng hàm RIGHT

Để tất cả thể bóc những ký tự sinh sống bên đề xuất của chuỗi cam kết tự có thể xem là cam kết tự ở cuối chuỗi văn bản. Và để có thể bóc ký tự họ sẽ phải kết hợp cả 3 hàm RIGHT, LEN, SEARCH. Công thức của các hàm trên cụ thể như sau:

=RIGHT(ô thông số gốc;số ký kết tự nên lấy chuỗi)

Trong đó:

Ô tham số cội là ô cam kết tự gốc bạn sẽ lấy cam kết tự ra trường đoản cú nó.

Số ký tự buộc phải lấy chuỗi là số ký tự tính từ cam kết tự trước tiên của chuỗi ký tự vào ô thông số gốc.

=LEN(text)

Trong đó: Text là chuỗi văn bạn dạng mà các bạn muốn tìm độ dài, hàm sẽ đếm cả không gian và là thông số bắt buộc.

Hàm tìm kiếm đã được reviews ở trên. Ở trường hòa hợp này chúng ta sẽ thấy sự xuất hiện thêm của hàm LEN là một trong những hàm khá khác biệt so với nhị hàm RIGHT và SEARCH. Hàm này đang là hàm đếm số ký kết tự của ô tham số. 

Và 3 hàm bên trên sau khi phối hợp sẽ chuyển động như sau tổng số ký kết tự của ô tham số có nghĩa là ta vẫn đếm hết toàn cục các ký tự gồm trong ô tham số bằng hàm LEN, số địa điểm của vết phẩy máy hai được xác minh bởi hàm SEARCH. Và tác dụng là bọn họ đã tách chuỗi văn bản được theo lốt phẩy, các chúng ta cũng có thể linh hoạt ví như muốn bóc tách chuỗi theo những ký trường đoản cú khác hoặc có thể dấu cách bằng phương pháp thay ký tự kia vào vị trí của vệt phẩy trong công thức.

Ví dụ cụ thể như sau:

Công thức: 

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

*

Dùng hàm MID

Với những cách tách chuỗi văn bản làm việc đầu và cuối chuỗi văn phiên bản thì họ còn tất cả cách để tách bóc văn bản nằm ở giữa chuỗi văn bản. Khi muốn tách văn bạn dạng ở thân chuỗi văn phiên bản thì tất cả phần tinh vi hơn và chúng ta sẽ thực hiện hàm MID với SEARCH. Bí quyết của hàm MID rõ ràng như sau:

=MID(ô tham chiếu gốc;vị trí ban đầu lấy ký tự, số ký tự cần lấy)

Trong những phần của cách làm thì khi đọc chúng ta đã nắm rõ và hiểu những phần đó sẽ nhập được nhập ra sao. Ở phần đầu thì chúng ta chỉ đề xuất nhập phần dữ liệu cần bóc vào, hai phần còn sót lại thì chúng ta cần kết hợp với hàm search để có thể để tính ra con số phù hợp.

Ví dụ rõ ràng như sau:

Công thức:

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

*

Một mẹo khi tách văn bản bằng những cách trên

Trong những công thức trên gồm sự kết hợp với hàm SEARCH, từng công thức đều phải có +1 với -1 tương ứng với số cam kết tự trong vệt phân cách. Ví như dấu phân làn của bạn bao gồm 2 ký kết tự, thì chúng ta sẽ nhập là +2 hay -2.

Ví dụ: Dấu phân cách gồm dấu phẩy cùng khoảng trắng, tiếp nối chỉ cung ứng dấu phẩy (“,”) cho hàm SEARCH, và áp dụng +2 với -2 thay vì chưng +1 và -1.

Như vậy, bọn họ đã biết cách tách chuỗi văn bản trong Excel theo vệt phẩy và khoảng cách qua nội dung bài viết này. Hy vọng, bài viết sẽ mang về hữu ích cho chúng ta và đủ kỹ năng và kiến thức để chúng ta cũng có thể áp dụng vào công việc của bạn.