Thủ thuật excel lấy chữ cuối

      6

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

Bạn đang xem: Thủ thuật excel lấy chữ cuối


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

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

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

Khi phân chia các ô trong Excel, việc chính là xác xác định trí của dấu ngăn cách trong chuỗi văn bản. Tùy ở trong vào quá trình của bạn, điều này có thể được thực hiện bằng phương pháp sử dụng hàm tìm kiếm không phân minh chữ hoa chữ thường hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi bạn có vị trí của dấu phân cách, áp 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.

Để hiểu rõ hơn, hãy lưu ý ví dụ sau đây:

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

*

Để trích xuất color (tất cả những ký từ giữa các dấu gạch nối thứ 2 và sản phẩm công nghệ 3), hãy nhập phương pháp sau trong C2, cùng 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ư bạn cũng 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 – địa điểm để trích xuất văn phiên bản từ.Start_num – vị trí của kí tự thứ nhất để trích xuất.Num_chars – số ký tự nhằm trích xuất.

Trong phương pháp trên, văn bạn dạng được trích ra tự ô A2, cùng 2 đối số không giống được tính bằng cách sử dụng 4 hàm tìm kiếm khác:

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

SEARCH (“-“, A2) + 1

Số ký tự để trích xuất (num_chars): sự biệt lập giữa địa điểm của lốt nối thiết bị 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 size (tất cả những ký tự sau vệt nối sản phẩm 3), hãy nhập cách làm sau trong D2:

= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, A2, search (“-“, 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, trường đoản cú đó chúng ta trừ đi địa điểm của dấu nối lắp thêm hai. Sự khác biệt là số cam kết tự 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ự, chúng ta có thể phân chia cột bởi bất kỳ kí tự làm sao khác. Toàn bộ bạn yêu cầu làm là thay thế “-” bởi ký tự chia cách bắt buộc, ví dụ như dấu giải pháp (“”), vệt gạch chéo cánh (“/”), dấu hai chấm (“;”), dấu chấm phẩy (“;”) với vân vân.

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

Làm chũm nào để phân loại chuỗi bằng phương pháp ngắt chiếc 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ư cách làm được minh họa trong 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 chiếc vì chúng ta 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 bé dại trông giống như như sau:

*
Lấy công thức từ ví dụ như trước và vắt dấu gạch nối (“-“) bằng CHAR (10) trong đó 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à đó là kết quả:

*

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

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

Xem thêm: Hướng Dẫn Cài Đặt Win 7 Bằng Usb, Cách Cài Win 7 Bang Usb Cho Laptop

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

Giả sử chúng ta 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 các 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 ban sơ để văn bản và số xuất hiện thêm 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 trả 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 đúng theo A2 là chuỗi ban đầu, với C2 là số trích xuất, như biểu lộ trong hình bên dưới đây:

*
Công thức hoạt động như thế 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 phần đa số có thể từ 0 mang đến 9 trong 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à đó là công thức chi tiết phân rã:

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

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

Giải pháp sửa chữa thay thế sẽ áp dụng công thức sau để 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 & “0123456789”))

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

Khi địa chỉ của số thứ nhất được search thấy, chúng ta có thể tách văn phiê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 dễ dàng (nhớ rằng một vài luôn xuất hiệ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ới B2 là địa điểm của số đầu tiên, như thể hiện trong hình dưới đây:

*
Để thải trừ cột helper giữ địa điểm số bắt đầu, bạn có thể nhúng hàm MIN vào những 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 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 đo lường vị trí của số đồ vật nhất

Bạn cung cấp hằng số mảng 0,1,2,3,4,5,6,7,8,9 trong đố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 mặt trong bạn dạng gốc, cùng trả lại vị trí của chúng. Bởi vì hằng số mảng cất 10 chữ số, mảng tác dụng cũng cất 10 mục.

Hàm MIN đem mảng kết quả và trả về giá trị nhỏ nhất, tương ứng với địa chỉ của số thứ nhất trong chuỗi ban đầu.

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

Ví dụ: đối 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à cách cửa hàng chúng tôi có:

5 là ký tự thiết bị 8 trong chuỗi gốc, với 0 là cam kết tự máy 7, kia là lý do tại sao mục trước tiên của mảng tác dụng là “7”, cùng thứ sáu là “8”.Không có mục nào khác của hằng số mảng được tra cứu thấy vào A2, và cho nên vì vậy 8 phần không giống 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à cũng chính vì 7 là giá trị nhỏ dại nhất trong mảng kết quả, bởi hàm MIN trả về, và chúng ta nhận được địa điểm của số trước tiên (0) trong chuỗi văn bản ban đầu.

Ví dụ 2. Phân chia chuỗi của nhiều 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 sau một số, bạn 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ừ ví dụ như trước, xung quanh bạn thực hiện hàm LEFT thay vị RIGHT, bởi vì trong trường thích 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 đã có các bé số, trích xuất văn bạn dạng bằng giải pháp trừ số chữ số trường đoản cú tổng chiều nhiều năm 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ư mô tả 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 tất cả các số xuất phát từ 1 chuỗi trong định dạng ‘number-text-number’, chúng ta cũng có thể sử dụng công thức sau đây được gợi nhắc bởi một trong những những chuyên gia của MrExcel:

= SUMPRODUCT (MID (0 & 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 dạng ban đầu.

*