Công thức lấy ký tự trong excel

     

Trong bài viết này, sucmanhngoibut.com.vn đang lý giải biện pháp tách các ô vào Excel bởi công thức. quý khách hàng đã học tập phương pháp tách văn bạn dạng dựa trên dấu phẩy, khoảng chừng Trắng hoặc bất kỳ lốt phân làn như thế nào khác, với làm nỗ lực nào để phân loại chuỗi thành văn bạn dạng và số.

Bạn đang xem: Công thức lấy ký tự trong excel

Làm nuốm làm sao nhằm chia văn bản vào Excel bằng phương pháp thực hiện công thức:

Để bóc tách chuỗi vào Excel tuyệt bóc tách chữ cùng số trong excel, các bạn thường xuyên áp dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. ban đầu, một vài công thức có thể phức tạp, dẫu vậy thực tiễn xúc tích và ngắn gọn là khá dễ dàng và đơn giản, và những ví dụ sau đây đang cung ứng cho chính mình một số làm mai.

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


*

*

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

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

Giả sử bạn có một list những SKU của chủng loại Loại-Màu-Kích thước, và bạn có nhu cầu chia tách cột thành 3 cột riêng biệt biệt:

*

Để trích xuất thương hiệu mục (tất cả những ký kết tự trước vệt nối đầu tiên), cnhát phương pháp sau vào B2, với tiếp nối coppy nó xuống cột:

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

Trong công thức này, hàm Search xác xác định trí của lốt nối trước tiên (“-“) vào chuỗi với tác dụng LEFT sẽ chiết toàn bộ các cam kết từ bỏ sót lại (các bạn trừ 1 tự địa chỉ của vệt nối bởi vì các bạn không muốn có vệt nối).

*

Để trích xuất Color (toàn bộ những ký kết trường đoản cú thân những dấu gạch nối thứ hai với máy 3), hãy nhập bí quyết sau trong C2, cùng sau đó sao chép nó xuống các ô khác:

=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1

*

Nlỗi bạn cũng có thể biết, hàm MID gồm cú pháp sau:

MID (vnạp năng lượng bạn dạng, start_num, num_chars)

Nơi:

Văn bạn dạng – vị trí nhằm trích xuất văn bản tự.Start_num – địa điểm của kí từ thứ nhất nhằm trích xuất.Num_chars – số ký từ bỏ để trích xuất.

Trong phương pháp bên trên, văn bản được trích ra từ ô A2, và 2 đối số khác được tính bằng phương pháp sử dụng 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ý kết từ để trích xuất (num_chars): sự biệt lập giữa địa chỉ của vết nối thiết bị nhị cùng lốt nối thứ nhất, trừ đi 1:

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

Để trích xuất form size (toàn bộ những ký từ bỏ sau lốt nối sản phẩm công nghệ 3), hãy nhập phương pháp 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, tự đó các bạn trừ đi vị trí của vết nối thứ nhì. Sự khác hoàn toàn là số ký từ bỏ sau dấu nối sản phẩm công nghệ nhì và hàm RIGHT chiết xuất chúng.

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

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

Làm cố gắ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 uống phiên bản bằng khoảng chừng Trắng, hãy thực hiện các công thức tương tự như nlỗi bí quyết được minh họa trong ví dụ trước. Sự khác biệt độc nhất là bạn phải tính năng CHAR để hỗ trợ mang lại ký trường đoản cú ngắt chiếc vày các bạn cấp thiết gõ thẳng vào công thức. Giả sử, các ô cơ mà bạn muốn phân tách nhỏ trông tựa như nhỏng sau:

*
Lấy cách làm trường đoản cú ví dụ trước cùng cầm dấu gạch nối (“-“) bởi CHAR (10) trong những số đó 10 là mã ASCII mang lại chiếc cấp tài liệu.

Để trích xuất thương hiệu khía cạnh 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 rứa nào nhằm phân chia vnạp năng lượng bạn dạng với số vào Excel:

Để bắt đầu, không tồn tại phương án bao quát đến toàn bộ các chuỗi chữ số. Công thức như thế nào nhằm áp dụng phụ thuộc vào mẫu chuỗi rõ ràng. Dưới đây các bạn sẽ search thấy công thức cho 3 kịch phiên bản hay chạm chán độc nhất vô nhị.

Xem thêm: Câu Đội Thanh Niên Xung Kích Của 1 Trường Phổ Thông Có 12 Học Sinh,

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

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

*

Để trích xuất các số, thực hiện công thức mảng tiếp sau đây, được hoàn thành bằng cách dìm 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 sơ, và C2 là số trích xuất, nlỗi biểu hiện trong hình dưới đây:

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

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

Đầu tiên, bạn sử dụng những hàm LEN và SUBSTITUTE để tìm thấy mốc giới hạn xuất hiện một số trong những làm sao kia trong chuỗi cội – thay thế số bởi một chuỗi trống rỗng (“”), với kế tiếp trừ đi chiều nhiều năm của chuỗi cơ mà không tồn tại số kia trường đoản cú tổng thể Chiều dài của chuỗi lúc đầu. Bởi do đó là 1 trong phương pháp mảng, thao tác này được triển khai 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 toàn bộ những lần xuất hiện thêm của tất cả những chữ số trong chuỗi mối cung cấp.Cuối cùng, hàm RIGHT trả về những ký từ bỏ trường đoản cú phía bên đề nghị của chuỗi.

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

Một chiến thuật khác (công thức không có mảng)

Giải pháp sửa chữa đang sử dụng bí quyết sau để xác định vị trí của số đầu tiên vào 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 phương pháp thông thường được kết thúc Theo phong cách thông thường bằng cách nhận phím Enter.

Khi vị trí của số thứ nhất được kiếm tìm thấy, chúng ta cũng có thể tách bóc văn bản cùng số bằng phương pháp áp dụng những công thức LEFT và RIGHT vô cùng dễ dàng và đơn giản (hãy nhờ rằng một số 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 thích hợp A2 là chuỗi thuở đầu, và B2 là địa điểm của số trước tiên, nhỏng diễn đạt vào hình dưới đây:

*
Để loại trừ cột helper giữ lại địa điểm số ban đầu, chúng ta 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 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 tính toán thù địa điểm của số sản phẩm nhất

Quý Khách hỗ trợ 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, tạo nên nó kiếm tìm từng số vào hằng số mảng bên phía trong bạn dạng cội, với trả lại vị trí của chúng. Bởi vì hằng số mảng đựng 10 chữ số, mảng công dụng cũng đựng 10 mục.

Hàm MIN mang mảng hiệu quả và trả về cực hiếm bé dại tuyệt nhất, tương ứng với địa điểm của số đầu tiên trong chuỗi ban đầu.

Hình như, công ty chúng tôi áp dụng một cấu tạo quan trọng (A2 & “0123456789”) nhằm ghép mỗi số hoàn toàn có thể cùng với chuỗi ban sơ. Cách này triển khai mục đích của IFERROR với được cho phép Cửa Hàng chúng tôi tránh lỗi Khi một trong những cố định vào hằng số mảng không được tìm thấy vào chuỗi mối cung cấp. Trong trường hợp này, công thức trả về vị trí “đưa mạo” bởi chuỗi chiều nhiều năm từ là một ký từ bỏ trlàm việc lên. Như vậy chất nhận được hàm LEFT trích xuất văn bạn dạng với hàm RIGHT trả về một chuỗi rỗng trường hợp chuỗi nơi bắt đầu không cất ngẫu nhiên số làm sao, nhỏng trong dòng 7 hình ở bên trên.

Ví dụ: đối với chuỗi “Dress 05” trong 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ửa Hàng chúng tôi có:

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

*
Và chính vì 7 là cực hiếm nhỏ dại độc nhất vào mảng công dụng, bởi hàm MIN trả về, và bọn họ nhận được vị trí của số trước tiên (0) vào chuỗi văn uống bạn dạng ban đầu.

Xem thêm: Chuyên Đề Logarit Ôn Thi Đại Học Chuyên Đề Mũ Logarit, Chuyên Đề Mũ

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

Nếu bạn đang tách bóc các ô địa điểm vnạp năng lượng bản lộ diện 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 (xong xuôi bằng cách dìm 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 công thức mảng từ bỏ ví dụ trước, bên cạnh các bạn thực hiện hàm LEFT vậy bởi RIGHT, cũng chính vì vào trường hòa hợp này số luôn luôn xuất hiện thêm sống phía phía bên trái của chuỗi. Một khi bạn đã sở hữu những số lượng, trích xuất vnạp năng lượng phiên bản bằng phương 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 các phương pháp trên, A2 là chuỗi ban đầu với B2 là số trích xuất, nhỏng thể hiện trong hình bên dưới đây:

*

ví dụ như 3. Trích xuất chỉ số trường đoản cú chuỗi số ‘số văn uống bản’

Nếu các bước của người tiêu dùng đòi hỏi yêu cầu trích xuất tất cả những số từ 1 chuỗi vào định dạng ‘number-text-number’, bạn cũng có thể thực hiện công thức sau đây được nhắc nhở vì một trong những chuyên gia của MrExcel:

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

Trường đúng theo A2 là chuỗi văn bạn dạng thuở đầu.

*


Chuyên mục: