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

Trong nội dung bài viết này, geektechreviews.com vẫn lý giải biện pháp tách bóc những ô trong Excel bởi phương pháp. Bạn đã học biện pháp bóc tách vnạp năng lượng bạn dạng dựa trên vết phẩy, khoảng chừng White hoặc ngẫu nhiên lốt phân cách làm sao không giống, cùng có tác dụng thế như thế nào nhằm phân loại chuỗi thành văn bản với số.

Bạn đang xem: Hàm lấy ký tự bất kỳ trong excel


Làm cụ như thế nào nhằm phân tách văn uống bản vào Excel bằng cách áp dụng công thức:

Để tách chuỗi trong Excel tuyệt tách bóc chữ với số trong excel, các bạn hay sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. lúc đầu, một số phương pháp có thể tinh vi, tuy vậy thực tế súc tích là khá dễ dàng và đơn giản, cùng các ví dụ tiếp sau đây đã cung cấp cho bạn một số mối manh.

Tách chuỗi bởi lốt phẩy, lốt nhì chnóng, lốt gạch men chéo, vệt gạch men ngang hoặc dấu chia cách khác

Khi phân loại các ô vào Excel, bài toán đó là xác xác định trí của dấu chia cách vào chuỗi vnạp năng lượng phiên bản. Tùy nằm trong vào quá trình của khách hàng, đ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 ko tách biệt chữ hoa chữ hay hoặc hàm Find bao gồm khác nhau chữ hoa chữ thường xuyên. Một khi bạn bao gồm địa điểm của vệt phân cách, sử dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương xứng của chuỗi vnạp năng lượng phiên bản.

Để làm rõ hơn, hãy chăm chú ví dụ sau đây:

Giả sử các bạn tất cả một list 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 biệt biệt:

*


*

*

Để trích xuất tên mục (toàn bộ những ký kết trường đoản cú trước vệt nối đầu tiên), cnhát bí quyết sau vào B2, và tiếp đến xào nấu nó xuống cột:

= LEFT (A2, tìm kiếm (“-“, A2,1) -1)

Trong phương pháp 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à tính năng LEFT sẽ chiết toàn bộ những ký từ bỏ còn lại (chúng ta trừ 1 tự địa điểm của vết nối chính vì chúng ta không muốn bao gồm dấu nối).

*

Để trích xuất Màu sắc (tất cả các cam kết tự giữa những vệt gạch nối thứ 2 và máy 3), hãy nhập cách làm sau vào C2, với kế tiếp xào luộc nó xuống những ô 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 có cú pháp sau:

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

Nơi:

Vnạp năng lượng phiên bản – vị trí nhằm trích xuất văn bản từ bỏ.Start_num – địa chỉ của kí tự trước tiên nhằm trích xuất.Num_chars – số ký từ bỏ để trích xuất.

Trong cách làm bên trên, vnạp năng lượng phiên bản được trích ra từ bỏ ô A2, với 2 đối số khác được xem bằng cách sử dụng 4 hàm SEARCH khác:

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

SEARCH (“-“, A2) + 1

Số cam kết từ nhằm trích xuất (num_chars): sự biệt lập thân địa điểm của dấu nối sản phẩm hai cùng vệt nối đầu tiên, trừ đi 1:

SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1

Để trích xuất form size (tất cả các ký kết tự sau lốt nối lắp thêm 3), hãy nhập phương pháp sau trong D2:

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

Trong cách làm 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 vệt nối trang bị nhị. Sự khác biệt là số cam kết từ bỏ sau dấu nối máy nhị với hàm RIGHT chiết xuất bọn chúng.

*
Trong một phương pháp tương tự như, chúng ta cũng có thể phân loại cột do ngẫu nhiên kí trường đoản cú nào khác. Tất cả bạn phải có tác dụng là sửa chữa “-” bằng cam kết trường đoản cú phân làn phải, ví dụ như dấu giải pháp (“”), dấu gạch men chéo (“/”), vết hai chấm (“;”), vệt chnóng phẩy (“;”) và vân vân.

Mẹo. Trong những cách làm trên, +1 cùng -1 khớp ứng với số ký từ vào lốt chia cách. Trong ví dụ này, nó là 1 trong những vết nối (1 ký tự). Nếu vết phân làn của chúng ta bao gồm 2 ký trường đoản cú, ví dụ: Dấu phẩy với khoảng chừng White, tiếp nối chỉ cung ứng vết phẩy (“,”) mang đến hàm SEARCH, cùng thực hiện +2 cùng -2 cố bởi vì +1 cùng -1.

Làm vậy như thế nào để phân loại chuỗi bằng cách ngắt dòng vào Excel:

Để chia văn bạn dạng bằng khoảng tầm white, hãy thực hiện các công thức tương tự nhỏng cách làm được minch họa trong ví dụ trước. Sự biệt lập độc nhất là bạn phải chức năng CHAR để cung ứng cho ký kết tự ngắt mẫu vày chúng ta quan yếu gõ trực tiếp vào cách làm. Giả sử, những ô mà lại bạn muốn phân chia nhỏ tuổi trông tựa như nlỗi sau:

*
Lấy công thức từ bỏ ví dụ trước cùng cầm cố lốt gạch ốp nối (“-“) bởi CHAR (10) trong những số đó 10 là mã ASCII mang lại mẫu cấp cho tài 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à đấy là kết quả:

*

Làm nắm làm sao để phân loại văn phiên bản và số trong Excel:

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

Xem thêm: Uống Nước Lá Tía To Có Tác Dụng Gì Cho Bà Bầu, Tác Dụng Không Ngờ Của Lá Tía Tô Với Bà Bầu

lấy ví dụ 1. Chia chuỗi của các loại ‘văn uống phiên bản + số’

Giả sử các bạn bao gồm một cột những chuỗi với văn uống bản với số phối kết hợp, trong những số ấy một số trong những luôn luôn luôn theo sau văn bạn dạng. Quý khách hàng mong mỏi phá vỡ lẽ những chuỗi thuở đầu nhằm văn uống phiên bản với số xuất hiện trong những ô đơn lẻ, như sau:

*

Để trích xuất những số, thực hiện phương pháp mảng dưới đây, được chấm dứt bằng phương pháp dấ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 vnạp năng lượng bản, sử dụng:

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

Trường đúng theo A2 là chuỗi thuở đầu, và C2 là số trích xuất, nhỏng thể hiện trong hình bên dưới đây:

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

Công thức để trích xuất số (hàm RIGHT). Về cơ bản, công thức search kiếm gần như số rất 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ý kết từ từ cam kết từ cuối chuỗi ban sơ.

Và đấy là phương pháp cụ thể phân rã:

Thứ nhất, chúng ta sử dụng các hàm LEN và SUBSTITUTE nhằm tìm thấy mốc giới hạn xuất hiện thêm một số làm sao kia trong chuỗi nơi bắt đầu – sửa chữa thay thế số bởi một chuỗi rỗng (“”), với sau đó trừ đi chiều dài của chuỗi mà không có số đó trường đoản cú tổng cộng Chiều nhiều năm của chuỗi ban sơ. Bởi vì chưng đó là một trong bí quyết mảng, thao tác 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ả những lần mở ra của tất cả những chữ số vào chuỗi mối cung cấp.Cuối thuộc, hàm RIGHT trả về những ký kết từ bỏ tự phía mặt cần của chuỗi.

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

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

Giải pháp thay thế sửa chữa vẫn sử dụng cách làm sau để xác xác định trí của số thứ nhất trong chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”))

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

Khi địa điểm của số trước tiên được tìm kiếm thấy, bạn có thể bóc tách văn bạn dạng và số bằng phương pháp thực hiện những công thức LEFT và RIGHT vô cùng đơn giản và dễ dàng (đừng quên một vài luôn xuất hiện sau văn uống bản):

Để trích xuất văn uố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ơ, cùng B2 là địa điểm của số thứ nhất, nlỗi thể hiện trong hình bên 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 các hàm LEFT cùng RIGHT:

Công thức trích xuất văn uống 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 tính toán thù địa chỉ của số đồ vật nhất

quý khách hàng 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, khiến cho nó tìm từng số trong hằng số mảng phía bên trong bản cội, và trả lại vị trí của chúng. Bởi vì hằng số mảng cất 10 chữ số, mảng kết quả cũng chứa 10 mục.

Hàm MIN mang mảng công dụng và trả về cực hiếm nhỏ nhất, tương xứng cùng với địa điểm của số thứ nhất trong chuỗi lúc đầu.

Trong khi, Cửa Hàng chúng tôi thực hiện một cấu trúc quan trọng đặc biệt (A2 và “0123456789”) nhằm ghép mỗi số rất có thể với chuỗi thuở đầu. Cách này thực hiện mục đích của IFERROR và được cho phép chúng tôi tránh lỗi Khi một số trong những nhất mực vào hằng số mảng không được tra cứu thấy trong chuỗi mối cung cấp. Trong ngôi trường đúng theo này, bí quyết trả về địa chỉ “đưa mạo” bằng chuỗi chiều dài từ 1 ký kết tự trnghỉ ngơi lên. Điều này được cho phép hàm LEFT trích xuất văn bạn dạng cùng hàm RIGHT trả về một chuỗi trống rỗng ví như chuỗi cội ko chứa bất kỳ số nào, nhỏng trong chiếc 7 hình sống bên trên.

Ví dụ: so 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à giải pháp công ty chúng tôi có:

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

*
Và cũng chính vì 7 là cực hiếm bé dại độc nhất vào mảng kết quả, vị hàm MIN trả về, và chúng ta nhận thấy địa điểm của số thứ nhất (0) vào chuỗi văn bản ban sơ.

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

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

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

Trong những bí quyết trên, A2 là chuỗi lúc đầu cùng B2 là số trích xuất, nhỏng bộc lộ trong hình dưới đây:

*

lấy ví dụ 3. Trích xuất chỉ số tự chuỗi số ‘số vnạp năng lượng bản’

Nếu công việc của bạn yên cầu bắt buộc trích xuất tất cả những số từ một chuỗi trong định dạng ‘number-text-number’, chúng ta có thể thực hiện công thức tiếp sau đây được lưu ý vị một giữa những Chuyên Viên của MrExcel:

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

Trường hợp A2 là chuỗi văn uống phiên bản lúc đầu.

*