Hàm lấy ký tự trong excel

     

Trong bài viết này, letspro.edu.vn sẽ phân tích và lý giải cách bóc các ô vào Excel bởi công thức. Các bạn sẽ học cách tách văn bạn dạng dựa theo vết phẩy, khoảng chừng trắng hoặc bất kỳ dấu ngăn cách nào khác, và 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 ký tự trong excel


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

Tách chuỗi bởi dấu phẩy, dấu hai chấm, vết gạch chéo, lốt gạch ngang hoặc dấu phân làn khác

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

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

Giả sử bạn có một danh sách các SKU của mẫu Loại-Màu-Kích thước, và bạn muốn chia tách 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ý từ trước lốt nối đầu tiên), chèn bí quyết sau vào B2, cùng 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 thứ nhất (“-“) trong chuỗi và công dụng LEFT đã chiết toàn bộ các ký kết tự còn sót lại (bạn trừ 1 từ địa điểm của vết nối bởi vì bạn không thích có vệt nối).

*

Để trích xuất màu sắc (tất cả các ký tự giữa các dấu gạch ốp nối thứ hai và sản phẩm 3), hãy nhập phương pháp sau vào C2, cùng sau đó xào nấu 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 tất cả 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 điểm của kí tự trước tiên để 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 dạng được trích ra tự ô A2, với 2 đối số khác được tính bằng cách sử dụng 4 hàm search khác:

Số bước đầu (start_num) là địa chỉ của lốt nối thứ nhất +1:

SEARCH (“-“, A2) + 1

Số cam kết tự nhằm trích xuất (num_chars): sự khác biệt giữa địa chỉ của vệt nối thứ hai và 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 kích thước (tất cả các ký từ sau lốt nối đồ vật 3), hãy nhập công thức sau vào D2:

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

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

*
Trong một bí quyết tương tự, chúng ta có thể phân phân tách cột bởi bất kỳ kí tự nào khác. Toàn bộ bạn bắt buộc làm là thay thế “-” bằng ký tự ngăn cách bắt buộc, ví như dấu phương pháp (“”), lốt gạch chéo cánh (“/”), vết hai chấm (“;”), dấu chấm phẩy (“;”) cùng 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 lốt phân cách. Trong ví dụ như này, nó là 1 dấu nối (1 ký tự). Giả dụ dấu phân cách của bạn bao gồm 2 cam kết tự, ví dụ: dấu phẩy và khoảng chừng trắng, sau đó chỉ cung ứng dấu phẩy (“,”) mang lại hàm SEARCH, và sử dụng +2 cùng -2 thay bởi +1 cùng -1.

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

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

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

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

Xem thêm: Danh Sách Những Video Được Xem Nhiều Nhất Youtube, 5 Mv Youtube Được Xem Nhiều Nhất Mọi Thời Đại

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

Giả sử chúng ta 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ố đó một số luôn luôn luôn theo sau văn bản. Bạn có nhu cầu phá vỡ những chuỗi thuở đầu để văn bạn dạng và số xuất hiện thêm trong những ô riêng biệt biệt, như sau:

*

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

*
Công thức hoạt động như nạ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 đông số hoàn toàn có thể từ 0 mang đến 9 vào chuỗi nguồn, tính số lượng và trả về các ký từ bỏ từ cam kết tự cuối chuỗi ban đầu.

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

Trước tiên, bạn sử dụng các hàm LEN cùng SUBSTITUTE nhằm tìm ra số lần xuất hiện một số nào đó trong chuỗi cội – 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 có số kia từ tổng thể Chiều nhiều năm của chuỗi ban đầu. Bởi vì đó là một trong những công thức mảng, thao tác này được thực hiện trên mỗi 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 của tất cả các chữ số trong chuỗi nguồn.Cuối cùng, hàm RIGHT trả về các ký tự trường đoản cú 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 cam kết tự văn bản chuỗi chứa bằng cách trừ số chữ số chiết xuất (C2) từ chiều dài của chuỗi nơi bắt đầu (A2). Sau đó, bạn sử dụng hàm LEFT nhằm trả về những ký tự từ đầu chuỗi.

Một phương án khác (công thức không có mảng)

Giải pháp thay thế sẽ áp 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 công thức thông thường được hoàn thành theo cách 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 dạng và số bằng phương pháp 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 số trong những 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à địa chỉ của số đầu tiên, như biểu thị trong hình bên dưới đây:

*
Để loại trừ cột helper giữ địa chỉ số bắt đầu, chúng ta cũng có thể nhúng hàm MIN vào các hàm LEFT cùng 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 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 giám sát vị trí của số đồ vật 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ó tìm kiếm từng số trong hằng số mảng mặt trong bản gốc, và trả lại địa điểm của chúng. Cũng chính vì hằng số mảng chứa 10 chữ số, mảng hiệu quả cũng đựng 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 chỉ 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 tạo đặc biệt (A2 & “0123456789”) để ghép mỗi số có thể với chuỗi ban đầu. Bí quyết này thực hiện vai trò của IFERROR và có thể chấp nhận được chúng tôi tránh lỗi khi một số trong những nhất định vào 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ách làm trả về địa chỉ “giả mạo” bởi chuỗi chiều dài từ một ký từ bỏ trở lên. Điều này có thể chấp nhận được 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 trường hợp chuỗi cội không chứa bất kỳ số nào, như trong cái 7 hình ngơi nghỉ trên.

Ví dụ: đối với chuỗi “Dress 05” vào A2, mảng tác dụng 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ự thiết bị 8 vào chuỗi gốc, và 0 là ký tự lắp thêm 7, kia là nguyên nhân tại sao mục đầu tiên của mảng công dụng là “7”, cùng thứ sáu là “8”.Không gồm mục nào không giống của hằng số mảng được tìm thấy vào A2, và 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ố tương ứng trong chuỗi nối (A2 & “0123456789”).

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

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

Nếu bạn đang bóc tách các ô nơi 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 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ương tự như công thức mảng từ lấy ví dụ trước, ko kể bạn áp dụng hàm LEFT thay do RIGHT, bởi vì trong trường phù hợp này số luôn lộ diện ở phía bên trái của chuỗi. Một khi bạn đã có các nhỏ số, trích xuất văn bạn dạng bằng biện pháp trừ số chữ số trường đoản cú 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 lúc đầu 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ố từ chuỗi số ‘số văn bản’

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

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

*