Trong nội dung bài viết này, dulongky.com sẽ giải thích cách bóc tách các ô vào Excel bằng công thức. Các bạn sẽ học cách bóc văn phiên bản dựa theo vệt phẩy, khoảng tầm trắng hoặc bất kỳ dấu ngăn cách nào khác, cùng làm rứa nào để phân loại chuỗi thành văn bản và số.

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


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

Để bóc chuỗi vào Excel hay bóc tách 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. Thời điểm đầ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á đối chọi giản, và các ví dụ tiếp sau đây sẽ cung cấp cho mình một số đầu mối.

Tách chuỗi bởi 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 ô trong Excel, việc đó là xác xác định trí của dấu phân làn trong chuỗi văn bản. Tùy trực thuộc 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 cách sử dụng hàm tìm kiếm không rành mạch chữ hoa chữ hay 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 cẩn thận ví dụ sau đây:

Giả sử các bạn có một danh sách những SKU của mẫu Loại-Màu-Kích thước, và bạn muố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ý từ bỏ trước dấu nối đầu tiên), chèn phương pháp sau vào B2, với sau đó 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 định vị 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 tất cả các cam 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 thích có lốt nối).

*

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

Trong công thức trên, văn bản được trích ra từ bỏ ô A2, cùng 2 đối số khác được tính bằng phương pháp sử dụng 4 hàm search khác:

Số ban đầu (start_num) là địa điểm của vết nối đầu tiên +1:

SEARCH (“-“, A2) + 1

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

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

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

= RIGHT (A2, LEN (A2) – search (“-“, A2, tìm kiếm (“-“, 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 địa điểm của dấu nối lắp thêm hai. Sự khác hoàn toàn là số ký tự sau lốt nối thứ hai cùng hàm RIGHT tinh chiết chúng.

*
Trong một bí quyết tương tự, chúng ta cũng có thể phân chia cột bởi bất kỳ kí tự nào khác. Toàn bộ bạn phải làm là sửa chữa “-” bằng ký tự phân cách bắt buộc, ví như dấu bí quyết (“”), lốt gạch chéo (“/”), dấu hai chấm (“;”), dấu chấm phẩy (“;”) cùng vân vân.

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

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

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

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

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

Xem thêm: Mua Card Điện Thoại Giá Sỉ Ở Đâu Để Nhận Chiết Khấu Cao Nhất?

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

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

*

Để trích xuất những số, áp 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 vừa lòng A2 là chuỗi ban đầu, với C2 là số trích xuất, như bộc lộ trong hình dưới đây:

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

Công thức để trích xuất số (hàm RIGHT). Về cơ bản, bí quyết tìm kiếm phần đa số có thể từ 0 cho 9 vào chuỗi nguồn, tính con số 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 cụ thể phân rã:

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

Công thức để trích xuất văn phiên bản (hàm LEFT). Bạn đo lường bao nhiêu ký 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ừ bỏ chiều nhiều năm của chuỗi nơi bắt đầu (A2). Sau đó, bạn sử dụng hàm LEFT nhằm trả về các ký tự từ trên đầu chuỗi.

Một phương án 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ẽ sử dụng công thức 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 & “0123456789”))

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

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

*
Để đào thải cột helper giữ địa chỉ số bắt đầu, bạn 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 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 đo lường và thống kê vị trí của số sản phẩm công nghệ nhất

Bạn hỗ trợ 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 bạn dạng gốc, cùng trả lại địa điểm của chúng. Bởi 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 rước mảng kết quả và trả về giá trị nhỏ nhất, tương ứng với vị trí của số thứ nhất trong chuỗi ban đầu.

Ngoài ra, chúng tôi sử dụng một cấu trúc đặc biệt (A2 & “0123456789”) nhằm ghép từng số có thể với chuỗi ban đầu. Giải pháp này tiến hành vai trò của IFERROR và cho phép chúng tôi kị lỗi khi một vài nhất định trong hằng số mảng không được kiếm tìm thấy trong chuỗi nguồn. Vào trường hòa hợp này, công thức trả về địa điểm “giả mạo” bằng chuỗi chiều dài từ 1 ký từ bỏ trở lên. Điều này được cho phép hàm LEFT trích xuất văn phiên bản và hàm RIGHT trả về một chuỗi rỗng giả dụ chuỗi cội không chứa ngẫu nhiên số nào, như trong dòng 7 hình ngơi nghỉ trên.

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

5 là ký tự thiết bị 8 vào chuỗi gốc, với 0 là ký tự máy 7, đó là nguyên nhân tại sao mục đầu tiên của mảng hiệu quả là “7”, và thứ sáu là “8”.Không tất cả mục nào khác của hằng số mảng được search 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 các chữ số tương ứng trong chuỗi nối (A2 và “0123456789”).

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

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

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

*

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

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

*