Hàm Lấy Chuỗi Ký Tự Trong Excel

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


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

Để tách chuỗi vào Excel hay bóc chữ và số trong excel, chúng ta thường áp dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. Thời gian đầu, một trong những công thức có thể phức tạp, nhưng thực tế logic là khá 1-1 giản, và những ví dụ sau đây sẽ cung cấp cho mình một số đầu mối.

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

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

Để nắm rõ hơn, hãy để mắt tới ví dụ sau đây:

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

*

Để trích xuất màu sắc (tất cả các ký trường đoản cú giữa các dấu gạch ốp nối thứ hai và thiết bị 3), hãy nhập cách làm sau trong C2, với 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 – khu vực để trích xuất văn bản từ.Start_num – vị trí của kí tự trước tiên để trích xuất.Num_chars – số cam kết tự để trích xuất.

Trong công thức trên, văn phiên bản được trích ra từ ô A2, với 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 dấu nối trước tiên +1:

SEARCH (“-“, A2) + 1

Số cam kết tự để trích xuất (num_chars): sự biệt lập giữa địa chỉ của vết nối thứ hai cùng dấu nối đầu tiên, trừ đi 1:

SEARCH (“-“, A2, search (“-“, 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 bí quyết sau trong D2:

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

Trong phương pháp 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 vật dụng hai. Sự biệt lập là số ký kết tự sau vệt nối lắp thêm hai với hàm RIGHT triết xuất chúng.

*
Trong một biện pháp tương tự, bạn cũng có thể phân phân tách cột bởi bất kỳ kí tự như thế nào khác. Tất cả bạn buộc phải làm là sửa chữa thay thế “-” bằng ký tự phân cách bắt buộc, ví dụ như dấu bí quyết (“”), lốt gạch chéo (“/”), lốt hai chấm (“;”), dấu chấm phẩy (“;”) cùng vân vân.

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

Làm gắng nào để phân loại chuỗi bằng phương pháp ngắt dòng trong Excel:

Để chia văn bạn dạng bằng khoảng chừng trắng, hãy sử dụng những công thức giống như như công thức được minh họa trong lấy một ví dụ trước. Sự khác hoàn toàn duy tuyệt nhất là bạn cần tác dụng CHAR để hỗ trợ cho ký tự ngắt loại vì các bạn không thể gõ thẳng vào công thức. Trả sử, các ô mà bạn có nhu cầu chia nhỏ trông tương tự như như sau:

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

Để bắt đầu, ko có chiến thuật tổng quát lác cho tất cả các chuỗi chữ số. Cách làm nào để sử dụng nhờ vào vào chủng loại chuỗi rứa thể. Dưới đây các bạn sẽ tìm thấy phương pháp cho 3 kịch phiên bản thường gặp mặt nhất.

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

Giả sử chúng ta có một cột những chuỗi cùng 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 ban sơ để văn bạn dạng và số xuất hiện thêm trong các ô riêng biệt, như sau:

*

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

*
Công thức vận động như rứa 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 gần như số có thể từ 0 mang đến 9 vào chuỗi nguồn, tính số lượng và trả về nhiều ký từ bỏ từ cam 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, chúng ta sử dụng các hàm LEN và SUBSTITUTE nhằm tìm ra số lần mở ra một số nào kia trong chuỗi gốc – sửa chữa số bằng một chuỗi trống rỗng (“”), và kế tiếp trừ đi chiều lâu năm của chuỗi mà không có số đó từ tổng số Chiều nhiều năm của chuỗi ban đầu. Bởi vì đó là một trong công thức mảng, thao tác này được triển khai trên mỗi 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 mở ra của tất cả các chữ số vào chuỗi nguồn.Cuối cùng, hàm RIGHT trả về các ký tự tự phía bên phải 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 và thống kê bao nhiêu ký tự văn phiên bản chuỗi chứa bằng cách trừ số chữ số tinh chiết (C2) từ bỏ chiều nhiều năm của chuỗi gốc (A2). Sau đó, bạn áp dụng hàm LEFT nhằm trả về những ký tự từ đầu chuỗi.

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

Giải pháp sửa chữa thay thế sẽ thực hiện 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ù bí quyết cũng chứa một hằng số mảng, đó là 1 trong những công thức bình thường được kết thúc 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 kiếm 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 các công thức LEFT và RIGHT rất đơn giản và dễ dàng (nhớ rằng một số luôn lộ diệ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 thích hợp A2 là chuỗi ban đầu, với B2 là địa điểm của số đầu tiên, như bộc lộ trong hình bên dưới đây:

*
Để sa thải cột helper giữ vị trí số bắt đầu, bạn cũng có thể nhúng hàm MIN vào những 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 & “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 & “0123456789”)) + 1)

Công thức đo lường vị trí của số máy nhất

Bạn cung ứng 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 cho nó kiếm tìm từng số vào hằng số mảng mặt trong phiên 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 tác dụng cũng cất 10 mục.

Hàm MIN lấy mảng kết quả và trả về giá chỉ trị bé dại nhất, tương ứng với vị trí của số trước tiên trong chuỗi ban đầu.

Ngoài ra, chúng tôi sử dụng một kết cấu đặc biệt (A2 và “0123456789”) để ghép mỗi số rất có thể với chuỗi ban đầu. Bí quyết này thực hiện vai trò của IFERROR và chất nhận được chúng tôi kiêng lỗi khi một trong những nhất định vào hằng số mảng ko được tra cứu thấy trong chuỗi nguồn. Trong trường 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 bất kỳ số nào, như trong loại 7 hình nghỉ ngơi trên.

Ví dụ: so với chuỗi “Dress 05” trong A2, mảng tác dụng là 7,10,11,12,13,8,15,16,17,18. Và đây là cách công ty chúng tôi có:

5 là ký kết tự sản phẩm công nghệ 8 vào chuỗi gốc, với 0 là cam kết tự sản phẩm 7, đó là vì sao tại sao mục đầu tiên của mảng tác dụng là “7”, và thứ sáu là “8”.Không gồm mục nào khác của hằng số mảng được tìm kiếm thấy trong A2, và cho nên 8 phần khác của mảng hiệu quả đại diện cho các vị trí của các chữ số khớp ứng trong chuỗi nối (A2 và “0123456789”).

*
Và bởi vì 7 là giá trị nhỏ nhất vào mảng kết quả, bởi vì hàm MIN trả về, và họ nhận được địa điểm của số trước tiên (0) trong chuỗi văn bạn dạng ban đầu.

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

Nếu bạn đang tách bóc các ô khu vực văn phiên bản xuất hiện nay sau một số, chúng ta có thể trích xuất các số với cách làm 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ư bí quyết mảng từ ví dụ như trước, ngoại trừ bạn áp dụng hàm LEFT thay do RIGHT, cũng chính vì trong trường vừa lòng này số luôn lộ diện ở phía phía bên trái của chuỗi. Một khi bạn đã sở hữu các con số, trích xuất văn phiên bản bằng cách trừ số chữ số tự tổng chiều dài 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 ban sơ và B2 là số trích xuất, như biểu đạt 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ác bước của bạn đòi hỏi phải trích xuất toàn bộ các số xuất phát từ 1 chuỗi trong định dạng ‘number-text-number’, bạn có thể sử dụng công thức sau đây được lưu ý bởi 1 trong những chuyên gia của MrExcel:

= SUMPRODUCT (MID (0 và 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 hòa hợp A2 là chuỗi văn phiên bản ban đầu.

*

Leave a Reply

Your email address will not be published. Required fields are marked *

x

Welcome Back!

Login to your account below

Retrieve your password

Please enter your username or email address to reset your password.