Hàm tách chuỗi ký tự trong excel

Trong nội dung bài viết này, duongmonkyhiep.vn sẽ lý giải bí quyết tách bóc những ô trong Excel bằng bí quyết. Quý khách hàng sẽ học tập giải pháp tách bóc văn uống bạn dạng dựa vào vệt phẩy, khoảng white hoặc ngẫu nhiên vết ngăn cách nào khác, và làm cho vậy nào để phân loại chuỗi thành văn bạn dạng cùng số.

You watching: Hàm tách chuỗi ký tự trong excel


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

Để bóc tách chuỗi vào Excel hay bóc chữ cùng số vào excel, các bạn thường áp dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. thuở đầu, một số trong những phương pháp có thể phức tạp, nhưng mà thực tiễn lô ghích là khá đơn giản và dễ dàng, và những ví dụ sau đây đang hỗ trợ cho mình một số mai mối.

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

khi phân loại những ô trong Excel, việc chính là xác xác định trí của vết phân làn vào chuỗi văn bạn dạng. Tùy thuộc vào các bước của người tiêu dùng, điều này có thể được thực hiện bằng cách sử dụng hàm Search không sáng tỏ chữ hoa chữ hay hoặc hàm Find gồm rõ ràng chữ hoa chữ hay. Một khi bạn bao gồm vị trí của vệt ngăn cách, áp dụng hàm RIGHT, LEFT hoặc MID nhằm trích xuất phần khớp ứng của chuỗi vnạp năng lượng phiên bản.

Để nắm rõ rộng, hãy để mắt tới ví dụ sau đây:

Giả sử chúng ta bao gồm một list những SKU của chủng loại 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 biệt 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), ckém cách làm sau trong B2, cùng sau đó xào luộc nó xuống cột:

= LEFT (A2, search (“-“, A2,1) -1)

Trong công thức này, hàm Search xác định vị trí của lốt nối trước tiên (“-“) trong chuỗi và tác dụng LEFT đang phân tách toàn bộ những ký từ bỏ sót lại (các bạn trừ 1 từ địa chỉ của vết nối bởi vì bạn không thích tất cả lốt nối).

*

Để trích xuất Màu sắc (toàn bộ những ký từ thân những dấu gạch men nối thứ 2 cùng thứ 3), hãy nhập phương pháp sau vào C2, và 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ỏng bạn có thể biết, hàm MID tất cả cú pháp sau:

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

Nơi:

Văn uống bạn dạng – chỗ để trích xuất vnạp năng lượng bản từ.Start_num – địa chỉ của kí từ thứ nhất nhằm trích xuất.Num_chars – số ký kết từ nhằm trích xuất.

Trong phương pháp bên trên, văn uống phiên bản được trích ra trường đoản cú ô A2, và 2 đối số không giống được tính bằng phương pháp thực hiện 4 hàm SEARCH khác:

Số bắt đầu (start_num) là địa chỉ của vệt 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 giữa địa điểm của lốt nối thứ nhì cùng vệt nối đầu 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 máy 3), hãy nhập công thức sau trong D2:

= RIGHT (A2, LEN (A2) – SEARCH (“-“, 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, trường đoản cú kia bạn trừ đi địa điểm của dấu nối thiết bị hai. Sự khác biệt là số ký kết từ bỏ sau dấu nối trang bị hai với hàm RIGHT chiết xuất chúng.

*
Trong một bí quyết tương tự như, chúng ta cũng có thể phân loại cột vì chưng ngẫu nhiên kí trường đoản cú nào không giống. Tất cả chúng ta đề xuất làm là thay thế “-” bởi ký trường đoản cú chia cách yêu cầu, ví như vết phương pháp (“”), dấu gạch ốp chéo cánh (“/”), vệt hai chấm (“;”), vệt chấm phẩy (“;”) và vân vân.

Mẹo. Trong những công thức bên trên, +1 với -1 tương xứng cùng với số ký trường đoản cú vào vệt chia cách. Trong ví dụ này, nó là một trong những dấu nối (1 ký kết tự). Nếu lốt chia cách của người sử dụng bao hàm 2 ký kết trường đoản cú, ví dụ: Dấu phẩy cùng khoảng white, sau đó chỉ hỗ trợ vết phẩy (“,”) cho hàm SEARCH, với áp dụng +2 và -2 cố gắng bởi +1 và -1.

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

Để phân tách văn uống bạn dạng bằng khoảng tầm White, hãy áp dụng những bí quyết giống như nhỏng bí quyết được minc họa trong ví dụ trước. Sự biệt lập tốt nhất là bạn cần tác dụng CHAR để cung cấp mang lại ký từ ngắt chiếc vì các bạn tất yêu gõ trực tiếp vào cách làm. Giả sử, những ô mà lại bạn có nhu cầu phân tách nhỏ dại trông tựa như như sau:

*
Lấy bí quyết trường đoản cú ví dụ trước cùng cầm vết gạch men nối (“-“) bằng CHAR (10) trong những số đó 10 là mã ASCII đến cái cung 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 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 cầm làm sao để phân loại vnạp năng lượng bạn dạng cùng số vào Excel:

Để bước đầu, không có giải pháp tổng thể mang đến toàn bộ những chuỗi chữ số. Công thức nào nhằm thực hiện dựa vào vào chủng loại chuỗi cụ thể. Dưới đây bạn sẽ kiếm tìm thấy công thức đến 3 kịch bản hay gặp mặt độc nhất vô nhị.

See more: Điện Thoại Tự Hiện Quảng Cáo, Điện Thoại Bị Nhiễm Quảng Cáo Phải Xử Lý Như Nào

Ví dụ 1. Chia chuỗi của các loại ‘vnạp năng lượng phiên bản + số’

Giả sử bạn có một cột các chuỗi với văn bản cùng số phối hợp, trong số đó một số trong những luôn luôn luôn theo sau văn phiên bản. Bạn ý muốn phá đổ vỡ những chuỗi ban sơ để vnạp năng lượng bạn dạng và số xuất hiện trong những ô riêng lẻ, nhỏng sau:

*

Để trích xuất các số, thực hiện phương pháp mảng sau đây, được xong 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 uống phiên bản, sử dụng:

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

Trường đúng theo A2 là chuỗi ban đầu, với C2 là số trích xuất, nhỏng diễn tả trong hình bên dưới đây:

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

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ phiên bản, công thức tìm kiếm tìm những số có thể trường đoản cú 0 đến 9 vào chuỗi mối cung cấp, tính số lượng cùng trả về những ký từ từ cam kết từ cuối chuỗi ban đầu.

Và đấy là cách làm cụ thể phân rã:

Trước hết, chúng ta sử dụng các hàm LEN với SUBSTITUTE để tìm thấy mốc giới hạn xuất hiện thêm một trong những như thế nào kia vào 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ới tiếp đến trừ đi chiều lâu năm của chuỗi cơ mà không tồn tại số kia từ bỏ toàn bô Chiều dài của chuỗi ban sơ. Bởi vày kia là 1 trong những công thức mảng, làm việc này được thực hiện bê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ả những lần xuất hiện thêm của tất cả các chữ số vào chuỗi mối cung cấp.Cuối cùng, hàm RIGHT trả về nhiều ký kết tự tự phía mặt yêu cầu của chuỗi.

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

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

Giải pháp thay thế đang thực hiện phương pháp sau nhằm xác xác định trí của số trước tiên vào chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”))

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

khi vị trí của số trước tiên được tra cứu thấy, bạn cũng có thể tách vnạp năng lượng phiên bản và số bằng cách sử dụng các bí quyết LEFT với RIGHT hết sức dễ dàng và đơn giản (hãy nhờ rằng một vài luôn xuất hiện thêm 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 vừa lòng A2 là chuỗi ban sơ, với B2 là địa điểm của số thứ nhất, như trình bày vào hình bên dưới đây:

*
Để sa thải cột helper duy trì vị trí số ban đầ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 vnạp năng lượng 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 và “0123456789”)) + 1)

Công thức tính tân oán địa điểm của số vật dụng nhất

Quý Khách 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, khiến cho nó tìm từng số vào hằng số mảng bên phía trong phiên bản gốc, với trả lại địa điểm của bọn chúng. Bởi do hằng số mảng cất 10 chữ số, mảng công dụng cũng chứa 10 mục.

Hàm MIN lấy mảng kết quả và trả về quý hiếm bé dại tuyệt nhất, tương ứng với địa điểm của số thứ nhất trong chuỗi ban sơ.

Trong khi, Shop chúng tôi thực hiện một cấu trúc quan trọng đặc biệt (A2 & “0123456789”) nhằm ghxay mỗi số rất có thể cùng với chuỗi ban sơ. Cách này thực hiện mục đích của IFERROR với cho phép chúng tôi tránh lỗi Khi một số cố định trong hằng số mảng không được kiếm tìm thấy trong chuỗi nguồn. Trong trường đúng theo này, phương pháp trả về vị trí “giả mạo” bởi chuỗi chiều nhiều năm từ một cam kết từ bỏ trsinh sống lên. Vấn đề này có thể chấp nhận được hàm LEFT trích xuất vnạp năng lượng bạn dạng và hàm RIGHT trả về một chuỗi trống rỗng nếu như chuỗi gốc không chứa ngẫu nhiên số nào, như vào dòng 7 hình sống trên.

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

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

*
Và cũng chính vì 7 là quý giá nhỏ độc nhất vào mảng tác dụng, bởi hàm MIN trả về, và họ nhận thấy vị trí của số đầu tiên (0) trong chuỗi văn uống bạn dạng ban sơ.

See more: Yến Vy Giờ Ra Sao Sau Khi Trải Qua Loạt Biến Cố Trong Cuộc Đời?

lấy ví dụ 2. Chia chuỗi của các loại ‘số + vnạp năng lượng bản’

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

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

Trong những bí quyết bên trên, A2 là chuỗi ban đầu và B2 là số trích xuất, như biểu thị vào hình dưới đây:

*

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

Nếu các bước của công ty yên cầu buộc phải trích xuất tất cả những số từ 1 chuỗi trong định hình ‘number-text-number’, chúng ta có thể thực hiện bí quyết tiếp sau đây được nhắc nhở vì chưng một Một trong những Chuyên Viên của MrExcel:

= 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: “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 lúc đầu.

*