Lấy chuỗi ký tự trong excel

Trong bài viết này, giaidap.info đang lý giải phương pháp tách những ô vào Excel bằng cách làm. quý khách hàng sẽ học cách tách văn uống bạn dạng dựa trên vết phẩy, khoảng trắng hoặc bất kỳ dấu ngăn cách như thế nào không giống, cùng có tác dụng thay làm sao nhằm phân chia chuỗi thành văn phiên bản cùng số.

You watching: Lấy chuỗi ký tự trong excel


Làm nuốm làm sao để phân tách văn uống bạn dạng vào Excel bằng phương pháp áp dụng công thức:

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

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

Lúc phân loại các ô trong Excel, việc đó là xác xác định trí của vệt ngăn cách trong 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 có thể được thực hiện bằng cách thực hiện hàm Search ko phân minh chữ hoa chữ hay hoặc hàm Find có phân minh chữ hoa chữ hay. Một khi chúng ta bao gồm địa điểm của dấu chia 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 văn uống bạn dạng.

Để hiểu rõ rộng, hãy để ý 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, cùng bạn muốn chia bóc tách cột thành 3 cột riêng biệt:

*


*

*

Để trích xuất tên mục (tất cả các ký từ bỏ trước dấu nối đầu tiên), cyếu công thức sau trong B2, và kế tiếp xào luộc nó xuống cột:

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

Trong cách làm này, hàm Search xác xác định trí của vệt nối đầu tiên (“-“) trong chuỗi cùng tính năng LEFT sẽ tách toàn bộ những cam kết từ bỏ còn lại (bạn trừ 1 từ vị trí của vệt nối cũng chính vì chúng ta không muốn bao gồm vết nối).

*

Để trích xuất Màu sắc (tất cả những ký kết từ bỏ thân những lốt gạch nối thứ hai cùng đồ vật 3), hãy nhập bí quyết sau vào C2, với kế tiếp sao chép nó xuống những ô 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 có cú pháp sau:

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

Nơi:

Vnạp năng lượng bạn dạng – chỗ nhằm trích xuất văn uống bạn dạng trường đoản cú.Start_num – địa chỉ của kí tự trước tiên nhằm trích xuất.Num_chars – số cam kết từ để trích xuất.

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

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

SEARCH (“-“, A2) + 1

Số ký từ bỏ nhằm trích xuất (num_chars): sự khác biệt giữa địa điểm của lốt nối thứ nhì cùng vệ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ộ các ký kết từ bỏ sau lốt nối thứ 3), hãy nhập bí quyết sau trong D2:

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

Trong bí quyết 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 lốt nối sản phẩm công nghệ nhì. Sự khác biệt là số ký kết trường đoản cú sau lốt nối thiết bị hai cùng hàm RIGHT chiết xuất chúng.

*
Trong một bí quyết tương tự như, bạn có thể phân chia cột bởi vì ngẫu nhiên kí trường đoản cú như thế nào không giống. Tất cả chúng ta nên làm là thay thế “-” bởi ký kết tự phân cách nên, ví như dấu giải pháp (“”), lốt gạch men chéo (“/”), lốt nhị chnóng (“;”), lốt chnóng phẩy (“;”) cùng vân vân.

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

Làm cầm cố nào nhằm phân loại chuỗi bằng phương pháp ngắt loại trong Excel:

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

*
Lấy phương pháp từ ví dụ trước và thế lốt gạch nối (“-“) bởi CHAR (10) trong những số ấy 10 là mã ASCII mang lại dòng cung cấp tài liệu.

Để trích xuất thương hiệu 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 gắng nào nhằm phân chia văn bản cùng số vào Excel:

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

See more: Hướng Dẫn Cách Xem Số Điện Thoại Của Mình Viettel, Vinaphone, Mobifone

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

Giả sử chúng ta bao gồm một cột những chuỗi với vnạp năng lượng bạn dạng với số phối hợp, trong đó một vài luôn luôn luôn theo sau văn uống bản. Bạn ao ước phá tan vỡ những chuỗi ban sơ nhằm vnạp năng lượng phiên bản và số mở ra trong số ô đơn nhất, nlỗi sau:

*

Để trích xuất các số, sử dụng công thức mảng tiếp sau đây, được ngừng 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 phiên bản, sử dụng:

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

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

*
Công thức hoạt động như thế nào:

Công thức để trích xuất số (hàm RIGHT). Về cơ bạn dạng, cách làm kiếm tìm tìm các số hoàn toàn có thể trường đoản cú 0 đến 9 trong chuỗi mối cung cấp, tính con số cùng trả về các cam kết từ bỏ tự ký từ bỏ cuối chuỗi ban đầu.

Và đây là cách làm cụ thể phân rã:

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

Công thức nhằm trích xuất văn bản (hàm LEFT). Quý Khách tính toán thù bao nhiêu cam kết tự văn uống bạn dạng chuỗi đựng bằng cách trừ số chữ số chiết xuất (C2) tự chiều lâu năm của chuỗi gốc (A2). Sau kia, các bạn áp dụng hàm LEFT nhằm trả về nhiều ký tự từ đầu chuỗi.

Một giải pháp khác (bí quyết không tồn tại mảng)

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

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

Lúc địa chỉ của số thứ nhất được search thấy, bạn có thể tách vnạp năng lượng phiên bản với số bằng cách sử dụng những bí quyết LEFT cùng RIGHT khôn xiết dễ dàng (hãy nhớ là một vài luôn mở ra 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 vừa lòng A2 là chuỗi lúc đầu, cùng B2 là địa điểm của số thứ nhất, nlỗi biểu lộ trong hình bên dưới đây:

*
Để thải trừ cột helper duy trì địa chỉ số ban đầu, bạn cũng 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 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 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 tân oán địa chỉ của số đồ vật nhất

Quý khách hàng 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, tạo nên nó tìm từng số trong hằng số mảng phía bên trong bạn dạng nơi bắt đầu, và trả lại địa chỉ của bọn chúng. Bởi vày 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 kết quả với 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 thuở đầu.

Bên cạnh đó, công ty chúng tôi áp dụng một cấu tạo quan trọng đặc biệt (A2 & “0123456789”) để ghép từng số có thể với chuỗi thuở đầu. Cách này tiến hành mục đích của IFERROR cùng cho phép công ty chúng tôi tách lỗi Khi một số nhất mực vào hằng số mảng không được kiếm tìm thấy trong chuỗi mối cung cấp. Trong ngôi trường hợp này, phương pháp trả về vị trí “mang mạo” bằng chuỗi chiều dài từ là một ký kết tự trnghỉ ngơi lên. Vấn đề này cho phép hàm LEFT trích xuất vnạp năng lượng bản cùng hàm RIGHT trả về một chuỗi rỗng giả dụ chuỗi nơi bắt đầu ko chứa ngẫu nhiên số nào, nlỗi trong cái 7 hình làm việc trên.

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

5 là cam kết từ máy 8 vào chuỗi cội, cùng 0 là ký trường đoản cú lắp thêm 7, sẽ là nguyên do vì sao mục thứ nhất của mảng kết quả là “7”, cùng lắp thêm sáu là “8”.Không có mục làm sao không giống của hằng số mảng được tra cứu thấy vào A2, cùng cho nên vì thế 8 phần khác của mảng tác dụng đại diện cho những địa chỉ của các chữ số tương ứng vào chuỗi nối (A2 và “0123456789”).

*
Và cũng chính vì 7 là quý giá bé dại tuyệt nhất trong mảng công dụng, vị hàm MIN trả về, cùng họ nhận được địa chỉ của số đầu tiên (0) trong chuỗi vnạp năng lượng phiên bản ban sơ.

See more: Vận Đơn Gốc ( Obl Là Gì ? Chức Năng, Tác Dụng & Phân Loại B/L

ví dụ như 2. Chia chuỗi của một số loại ‘số + văn uống bản’

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

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

Trong các công thức bên trên, A2 là chuỗi thuở đầu cùng B2 là số trích xuất, như biểu thị vào hình bên dưới đây:

*

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

Nếu công việc của bạn đòi hỏi buộc phải trích xuất tất cả 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ể áp dụng cách làm sau đây được gợi nhắc do 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:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)

Trường thích hợp A2 là chuỗi vnạp năng lượng phiên bản lúc đầu.

*


Chuyên mục: Chia sẻ