Trong nội dung bài viết hôm nay, bọn họ sẽ thuộc nhau tò mò về cách sử dụng hàm cắt chuỗi có điều kiện trong Excel, thuộc đón gọi nhé!
Cách để chia văn bản trong Excel bằng phương pháp dùng công thức
Để gồm thể tách bóc chuỗi vào Excel hoặc là bóc tách chữ cùng số trong Excel, bạn hay sử dụng hàm LEFT, RIGHT hay là hàm MID kết phù hợp với hàm FIND hoặc là hàm SEARCH. Cơ hội đầu, một vài ba công thức rất có thể sẽ khá phức tạp, nhưng thực tiễn theo logic là khá đối kháng giản, và đầy đủ ví dụ dưới đây sẽ cung cấp cho bạn một vài ba đầu mối.
Bạn đang xem: Hàm cắt chuỗi có điều kiện trong excel
Tách chuỗi bằng cách dùng vệt phẩy, vệt gạch chéo, vết hai chấm, vết gạch ngang hay là dấu ngăn cách khác
Khi triển khai phân chia đều ô vào Excel, câu hỏi chính sẽ là xác định vị trí dấu ngăn cách ở chuỗi văn bản. Tùy vào công việc người dùng, điều này có thể được thực hiện bằng việc dùng hàm search không minh bạch chữ hoa, chữ thường hay những hàm Find gồm phân biệt chữ hoa cùng chữ thường. Khi mà lại bạnn đã gồm vị trí của lốt phân cách, thì dùng hàm RIGHT, LEFT hoặc là hàm MID nhằm trích xuất phần khớp ứng trong chuỗi văn bản.
Để có thể hiểu rõ hơn, bạn hãy xem xét ví dụ bên dưới:
Ví dụ bạn đang có một list những SKU của chủng loại Loại-Màu-Kích thước, và bạn muốn chia bóc tách chúng thành 3 cột riêng rẽ biệt:
= LEFT (A2, tìm kiếm (“-“, A2,1) -1)
Ở phương pháp này, hàm tìm kiếm sẽ có tác dụng xác định vị trí của dấu nối đầu tiên (“-“) trên chuỗi và tác dụng hàm LEFT sẽ chiết tổng thể những ký tự sót lại (bạn thực hiện trừ 1 từ địa điểm của lốt nối vì chưng bạn không muốn có dấu nối).
=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1
Như bạn thấy, hàm MID gồm cú pháp sau:
MID (văn bản, start_num, num_chars)
Trong đó:
Văn bản: là nơi để trích xuất văn phiên bản từ.Start_num: là địa chỉ của kí tự thứ nhất để trích xuất.Num_chars: là số cam kết tự để trích xuất.Ở công thức trên, văn phiên bản đã được trích ra trường đoản cú ô A2, với 2 đối số khác được xem bằng việc dùng 4 hàm tìm kiếm khác:
Số ban đầu (start_num): địa điểm của vệt nối thứ nhất +1:SEARCH (“-“, A2) + 1
Số cam kết tự để trích xuất (num_chars): sự biệt lập giữa vị trí của vết nối lắp thêm hai so với vệt nối đầu tiên, trừ đi 1:SEARCH (“-“, A2, search (“-“, A2) +1) – tìm kiếm (“-“, A2) -1
Thực hiện nay trích xuất size (toàn bộ những ký kết tự sau lốt nối đồ vật 3), hãy triển khai nhập công thức sau vào D2:= RIGHT (A2, LEN (A2) – tìm kiếm (“-“, A2, search (“-“, A2) + 1))
Ở cách làm này, hàm LEN sẽ trả về tổng chiều dài của chuỗi, lúc này bạn triển khai trừ đi vị trí của vết nối lắp thêm hai. Sự biệt lập ở đó là số ký tự sau lốt nối sản phẩm công nghệ hai cùng hàm RIGHT chiết xuất chúng.
Ở một cách tương tự, chúng ta cũng có thể tiến hành phân chia cột bởi bất kể kí tự nào khác. Cục bộ mọi thứ bạn phải làm là sửa chữa “-” bằng ký tự phân làn bắt buộc, chẳng hạn như dấu biện pháp (“”), dấu gạch chéo cánh (“/”), vết chấm phẩy (“;”), vệt hai chấm (“;”) và vân vân.
Mẹo. Tại những công thức trên, +1 cùng -1 sẽ khớp ứng với số cam kết tự trong lốt phân cách. Ở lấy một ví dụ này, nó là một trong những dấu nối (1 ký tự). Trường hợp như lốt phân cách của bạn gồm 2 ký tự, lấy ví dụ như như: vệt phẩy và khoảng tầm trắng, tiếp đó chỉ cung cấp dấu phẩy (“,”) cho hàm SEARCH, và dùng +2 với -2 thay vày +1 với -1.
Cách để phân chia chuỗi bằng phương pháp ngắt mẫu trong Excel
Để tiến hành chia văn phiên bản bằng khoảng trắng, hãy dùng các công thức tựa như như phương pháp đã được minh họa sống ví dụ trước. Sự biệt lập duy tốt nhất ở đây là bạn cần tác dụng CHAR để rất có thể cung cấp cho cam kết tự ngắt dòng cũng chính vì bạn không thể triển khai gõ trực tiếp vào công thức. Ví dụ, đông đảo ô mà bạn có nhu cầu chia nhỏ dại trông giống như như bên dưới:
Thực hiện nay lấy công thức từ lấy một ví dụ trước và tiếp nối thay lốt gạch nối (“-“) bởi CHAR (10), với 10 là mã ASCII cho dòng cấp dữ liệu.
Thực hiện tại trích xuất tên phương diện hàng:=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)
Thực hiện 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)
Thực hiện nay trích xuất kích thước:=RIGHT(A2,LEN(A2) – SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))
Và dưới đây là hiệu quả thu được:
Cách để phân chia văn bạn dạng và số vào Excel:
Để bắt đầu, sẽ không còn có chiến thuật tổng quát lác cho toàn bộ chuỗi chữ số. Công thức nào để sử dụng sẽ nhờ vào vào chủng loại chuỗi nắm thể. Các ví dụ dưới đây, các bạn sẽ tìm thấy bí quyết cho 3 kịch bản hay gặp nhất.
Ví dụ 1. Phân tách chuỗi của một số loại ‘văn bản + số’
Ví dụ bạn có một cột đa số chuỗi với văn phiên bản và số kết hợp. Trong đó, một số luôn luôn luôn theo phía sau văn bản. Ai đang muốn phá vỡ phần đa chuỗi thuở đầu để văn bạn dạng và số được xuất hiện trong những ô riêng biệt, như bảng bên dưới:
Thực hiện nay trích xuất những số, bạn dùng cách làm mảng sau đây, được xong xuôi bằng bài toán nhấn tổng hợp phím Ctrl + Shift + Enter:
= RIGHT (A2, SUM (LEN (A2) – LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7” , “8”, “9”}, “”))))
Thực hiện trích xuất văn bản, các bạn dùng:
= LEFT (A2, LEN (A2) -LEN (C2))
Trong trường vừa lòng ô A2 là chuỗi ban đầu, với ô C2 là số trích xuất, hãy đọc hình minh họa hình mặt dưới:
Công thức vận động như nạm nào:
Công thức nhằm trích xuất số (hàm RIGHT). Xem về cơ bản, cách làm để kiếm tìm kiếm mọi số có thể từ 0 mang lại 9 ở chuỗi nguồn, tính con số rồi trả về nhiều ký từ bỏ từ ký kết tự cuối chuỗi ban đầu.
Và bên dưới là công thức chi tiết phân rã:
Đầu tiên, bạn dùng các hàm LEN và SUBSTITUTE để tiến hành tìm ra số lần mở ra một số nào kia ở chuỗi cội – thay thế sửa chữa số vì một chuỗi trống rỗng (“”), với tiếp kia trừ đi chiều nhiều năm của chuỗi mà không có số kia từ tổng cộng Chiều lâu năm của chuỗi đầu tiên. Vị đó là một trong những công thức mảng, nên thao tác làm việc này được tiến hành trên từng số phía trong hằng mảng:LEN (A2) -LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9 “},” “)
Tiếp đến, hàm SUM thêm toàn thể các lần xuất hiện thêm của tất cả những chữ số trong chuỗi nguồn.Cuối cùng là hàm RIGHT giúp trả về nhiều ký tự từ bỏ phía bên đề xuất của chuỗi.Công thức nhằm trích xuất văn phiên bản (hàm LEFT). Bạn thực hiện tính toán bao nhiêu cam kết tự văn bạn dạng chuỗi chứa bằng việc trừ số chữ số triết xuất (C2) từ chiều dài của chuỗi nơi bắt đầu (A2). Tiếp đó, các bạn dùng hàm LEFT để thực hiện trả về những ký tự từ đầu chuỗi.
Một bí quyết làm 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ẽ cần sử dụng công thức sau đây để thực hiện xác xác định trí của số trước tiên nằm vào chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 & “0123456789”))
Mặc dù công thức bao gồm chứa một hằng số mảng, đó là 1 công thức bình thường và được kết thúc theo cách thông thường bằng bài toán nhấn phím Enter.
Khi vị trí của số đầu tiên đã được tra cứu thấy, chúng ta cũng có thể tiến hành tách bóc văn bạn dạng và số bằng câu hỏi dùng cách làm hàm LEFT với RIGHT rất đơn giản (lưu ý rằng một vài luôn xuất hiện sau văn bản):
Thực hiện nay trích xuất văn bản:= LEFT (A2, B2-1)
Thực hiện trích xuất số:=RIGHT(B2, LEN(A1)-B2+1)
Trường hợp ô A2 là chuỗi ban đầu, cùng ô B2 là vị trí của số đầu tiên, tìm hiểu thêm hình minh họa bên dưới:
Để thực hiện loại trừ cột helper giữ vị trí số bắt đầu, chúng ta có thể tiến hành nhúng hàm MIN vào đầy đủ hàm LEFT và RIGHT:
Công thức trích xuất văn phiên bản như sau:= 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ố như sau:= RIGHT (A2, LEN (A2) -MIN (SEARCH(0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”)) + 1)
Công thức giám sát vị trí của số sản phẩm nhất
Bạn thực hiệ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, kế tiếp làm cho nó tìm từng số vào hằng số mảng ở mặt trong bản gốc, và tiếp đó trả lại vị trí của chúng. Chính vì hằng số mảng chứa 10 chữ số, mảng tác dụng cũng sẽ cất 10 mục.
Hàm MIN vẫn lấy mảng kết quả và tiếp đến trả về giá trị nhỏ dại nhất, sẽ khớp ứng với địa chỉ của số trước tiên trong chuỗi ban đầu.
Ngoài ra, các bạn dùng một cấu tạo đặc biệt (A2 & “0123456789”) để ghép từng số rất có thể cùng cùng với chuỗi ban đầu. Cách này có sử dụng hàm IFERROR và được cho phép bạn kiêng lỗi trong trường hợp một số trong những nhất định sinh sống hằng số mảng không được tra cứu thấy sinh hoạt chuỗi nguồn. Cùng với trường phù hợp này, bí quyết sẽ trả về địa điểm “giả mạo” bởi chuỗi chiều dài từ 1 ký tự trở lên. Điều này sẽ chất nhận được hàm LEFT trích xuất văn bản, hàm RIGHT thì trả về một chuỗi rỗng giả dụ như chuỗi gốc không có chứa bất kỳ số nào, như thể hiện trong cái 7 hình sinh sống trên.
Ví dụ: cùng với chuỗi “Dress 05” tại A2, thì mảng kết quả là 7,10,11,12,13,8,15,16,17,18. Và đấy là cách các bạn có:
5 là ký tự máy 8 nằm trong chuỗi gốc, cùng 0 là ký tự thứ 7, đấy là lý bởi vì vì sao mục đầu tiên của mảng công dụng đó là “7”, cùng thứ sáu là “8”.Không có mục khác của hằng số mảng tìm thấy trong A2, và chính vì vậy 8 phần không giống của mảng kết quả đại diện cho những vị trí của không ít chữ số tương xứng tại chuỗi nối (A2 & “0123456789”).
Và bởi 7 là giá trị nhỏ nhất bên trong mảng kết quả, chính vì hàm MIN trả về, và chúng ta nhận được địa điểm số trước tiên (0) sống chuỗi văn bản ban đầu.
Ví dụ 2. Thực hành thực tế chia chuỗi của một số loại ‘số + văn bản’
Nếu như nhiều người đang thực hiện tách bóc những ô khu vực văn phiên bản xuất hiện nay sau một số, thì chúng ta có thể trích xuất các số với phương pháp mảng này (hoàn thành bằng bài toán nhấn tổ hợp phí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 này tương tự như phương pháp mảng sống ví dụ phía trước, ngoại câu hỏi bạn sử dụng hàm LEFT chũm cho hàm RIGHT, chính vì ở trường hòa hợp này số luôn mở ra ở phía bên trái của chuỗi. Khi bạn đã có những con số, trích xuất văn bản bằng cách trừ số chữ số từ bỏ tổng chiều lâu năm chuỗi gốc, cách làm như sau:
= RIGHT(A2, LEN (A2) -LEN (B2))
Ở những công thức trên, ô A2 là chuỗi ban sơ và ô B2 là số trích xuất, được mô tả ở bảng bên dưới:
Nếu như công việc của bạn yên cầu phải thực hiện trích xuất toàn thể số xuất phát từ một chuỗi ở định dạng ‘number-text-number’, bạn có thể dùng phương pháp sau đây, nó được gợi ý bởi 1 trong các chuyên viên của Mr
Excel:
= SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” và LEN (A2))), 1)) * ROW (TRỰC TIẾP (“1:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)
Trong trường phù hợp A2 là chuỗi văn bạn dạng ban đầu.
Vậy là chúng ta đã bên nhau tìm hiểu cụ thể về phương pháp dùng những hàm giảm chuỗi có điều kiện trong Excel, chúc bạn triển khai thành công!
Excel ngày nay đang trở thành một công cụ phổ cập cho số đông người, tốt nhất là dân văn phòng. Trong quá trình sử dụng excel thì ít nhiều họ cũng chạm mặt trường hợp đề xuất lấy cam kết tự vào một chuỗi. Vậy buộc phải dùng hàm nào cho phù hợp? nội dung bài viết này sẽ mách bạn một trong những hàm để cắt chuỗi kí tự trong excel. Cùng tìm hiểu thêm nhé.
Cắt chuỗi trong excel làm như thế nào?
Muốn cắt chuỗi hàm lấy ký tự ta phải sử dụng các hàm giảm chuỗi. Bọn họ sẽ tất cả 3 hàm lấy ký kết tự như sau:
Hàm Right : Hàm này để đưa ký phía bên cần của phần chuỗi ký tự gốc.
Hàm Hàm Left: Hàm này dùng để lấy phần ký tự bên trái phần chuỗi gốc.
Hàm Mid: cần sử dụng để bóc tách lấy phần ký kết tự nằm trong lòng của chuỗi ký tự gốc.
1. Hàm Right cắt ký tự trong Excel
Cú pháp của hàm Right=RIGHT(text, n) ….. Vào đó:
Text: là chuỗi kí từ hoặc ô đựng chuỗi kí trường đoản cú mà bạn muốn cắt.
n: Số cam kết tự cần cắt ngoài chuỗi.
** lưu lại ý: so với ” n ” nếu khách hàng không nhập thông số này thì Excel vẫn tự hiểu là 1
Ví dụ : Ta có bảng list sinh viên và nên dùng hàm Right để mang 3 kí từ bỏ cuối của cột mã số sv (MSSV) mang đến cột tên Lớp. Tiến hành như sau:Tại D2 ta gõ cách làm như sau: = RIGHT (C2,3)
Ô C2 là nơi đựng hàm tách bóc chuỗi ký tự vào excel để cắt chuỗi.
Sau khi nhập cách làm thì thừa nhận Enter để hiện kết quả. Xào nấu công thức xuống bên dưới và bạn sẽ được kết quả hàm cắt chuỗi vào excel như hình.
2. Hàm Left cắt chuỗi cam kết tự trong Excel
Cú pháp của hàm Left=LEFT(text, n) … vào đó:
Text : Là chuỗi kí tự hay ô cất chuỗi kí tự ước ao cắt.
n: Số kí từ mà bạn cần cắt.
Ví dụ: áp dụng hàm Left để đưa 4 ký tự trong cột Mã nhập học . Biện pháp làm như sau:– tại ô E2 gõ công thức như sau: = LEFT (C2,4)
– Ô C2 là ô chứa dữ liệu bạn yêu cầu cắt chuỗi.
– Nhập phương pháp của hàm Left. Sau đó sao chép công thức xuống dưới với được hiệu quả như hình.
3. Cách bóc ký tự vào Excel bằng hàm Mid
Cú pháp của hàm Mid=MID(text,m,n) ….. Vào đó:
Text: Chuỗi ký kết tự.
m: Vị trí bước đầu cần cắt chuỗi ký tự.
n: Số cam kết tự yêu cầu cắt ra trường đoản cú chuỗi cam kết tự
Ví dụ : Ta tất cả bảng danh sách sinh viên và phải dùng hàm Mid để mang 4 kí trường đoản cú cuối của cột mã số sv (MSSV) cho cột Ngành học. Tiến hành như sau:Ta có công thức trên ô F2 như sau: = MID (C2,5,4) . Ta cắt chuỗi tự kí tự thứ 5 và giảm 4 ký tự.Kéo xuống các ô sót lại để sao chép công thức cùng sẽ ra được hiệu quả như hình dưới.
ICT dùng Gòn cung những cấp cung cần thiết bị công nghệ thông tin như: cho thuê máy tính xách tay hcm, thuê máy bàn giá rẻ, dịch vụ cho thuê macbook…phục vụ mọi nhu yếu tổ chức hội nghị, hội thảo, đào tạo, sự kiện cùng quảng cáo của khách hàng Hàng.
4. Cắt chuỗi trong excel bởi hàm left phối kết hợp hàm Len
Hàm LEN là hàm hay được dùng để kết hợp với những hàm lấy chuỗi kí tự. Khi phối hợp hàm Len với hàm Left ta hoàn toàn có thể dùng để loại bỏ một số cam kết tự nhất quyết từ phần cuối của chuỗi.
Ta bao gồm cú pháp khi kết hợp như sau: =LEFT(text,LEN(text)-n) … vào đó:
Text : là chuỗi ký kết tự mong cắt.
n : Là số kí tự bạn có nhu cầu cắt bên trên chuỗi.
Ví dụ: trong bảng có danh sách họ cùng tên, muốn thải trừ 5 ký tự của chuỗi ký tự trong ô B2 sẽ có cách tiến hành như sau:Ta bao gồm công thức =LEFT(B2, LEN(B2)-5) .
Sau khi nhập bí quyết -> nhấn Enter giúp xem kết quả.
Tách cam kết tự vào excel bởi hàm phối hợp left với lenHàm LEN vào cú pháp này là để mang tổng số ký kết tự vào một chuỗi rồi trừ đi lượng kí từ muốn đào thải từ tổng độ nhiều năm của dãy. Hàm LEFT thì vẫn trả về số ký kết tự còn lại.
Chúng ta vẫn có tác dụng là đang loai 5 ký kết tự ở đầu cuối trong dãy ký tự và bao hàm dấu cách.
Xem thêm: Hướng dẫn cách chèn logo chìm trong word 2010, cách chèn chữ, logo chìm vào trong word
Video hướng dẫn giải pháp cắt chuỗi trong Excel bởi hàm Left, Right, Mid
Vậy là ICT sài gòn đã chỉ dẫn về cách cắt chuỗi trong excel để bổ sung thêm một vài ba thủ thuật quan trọng và hữu mang đến mình. Hi vọng qua nội dung bài viết này các các bạn sẽ thao tác dễ dãi và thành công xuất sắc hơn cùng với excel nhé.