Cập nhật thông tin chi tiết về Cách Xóa Ký Tự Trong Chuỗi Từ Văn Bản Trên Excel mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
Trong một số trường hợp, bạn muốn loại bỏ đồng loạt ký tự ở vị trí bất kỳ trong chuỗi dữ liệu của bạn. Bài viết này,Gitiho sẽ hướng dẫn cách xóa ký tự trong chuỗi Excel.
Đăng ký ngay khóa học Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ
Ví dụ minh họa việc loại bỏ ký tự trong Excel
Dùng hàm Excel để xóa ký tự trong chuỗi Excel (ký tự đầu và cuối)
Trong Excel, có 2 hàm hỗ trợ bạn đọc xóa ký tự trong chuỗi Excel như ký tự đầu và cuối trong chuỗi, đó chính là hàm LEFT và hàm RIGHT. Để sử dụng những hàm này, bạn đọc làm theo các thao tác sau:
Loại bỏ ký tự đầu trong Excel
Các thao tác cần thực hiện như sau:
=RIGHT(A1,LEN(A1)-5)
Mẹo (Tips): Kết quả của công thức này là những ký tự bên phải trong chuỗi trừ đi 5 ký tự bên trái, trong ví dụ trên. Bạn hoàn toàn có thể linh động theo ý muốn, bằng cách thay đổi số 5 trong công thức bằng số ký tự mà bạn muốn loại bỏ.
Khoảng cách ( Space) cũng được coi là 1 ký tự. Cho nên, bạn cần lưu ý khi điền số lượng ký tự cần loại bỏ sao cho kết quả hiện ra đẹp mắt và gọn gàng hơn.
2. Nhấn phím Enter trên bàn phím máy tính để chạy công thức và Fill Down công thức xuống các ô phía dưới tương ứng. Vậy là, bạn đã có được chuỗi văn bản mới được loại bỏ 5 ký tự đầu tiên như hình:
Tương tự như hàm RIGHT giúp loại bỏ ký tự đầu trong Excel, hàm LEFT sẽ hỗ trợ bạn xóa các ký tự cuối trong chuỗi.
Sử dụng tính năng tìm và thay thế trong Excel để xóa ký tự trong chuỗi Excel
Tính năng Find and Replace trong Excel có thể giúp bạn loại bỏ ký tự trong Excel bất kỳ. Trong ví dụ này, giả sử bạn cần xóa ký từ trong chuỗi Excel như các ký tự ở đầu hoặc cuối dấu hai chấm (:). Cách làm như sau:
Giữ nút Ctrl và nhấn F để mở hộp thoại Find and Replace, và nhấn Replace.
3. Nhấp vào Replace All, và tất cả các ký tự ở cuối dấu hai chấm (bao gồm dấu hai chấm) đã bị xóa như hình dưới:
4. Nếu bạn muốn xóa tất cả các ký tự trước dấu hai chấm (:), vui lòng gõ *: vào ô Find what, và bỏ trống hộp thoại Replace with.
5. Nhấp vào Replace All, tất cả các ký tự từ dấu hai chấm trở về trước đã được xoá. Kết quả hiển thị như sau:
Lưu ý: Phương pháp này chỉ dùng cho những ký tự bao gồm dấu phân cách cụ thể, vì vậy bạn có thể thay đổi dấu hai chấm thành bất kì dấu phân cách nào khác.
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Kết luận
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
Cách Tách Chuỗi Văn Bản Trong Excel Theo Dấu Phẩy, Khoảng Trắng, Ký Tự Xác Định
Trong bài viết này, chúng tôi sẽ giải thích cách tách các ô trong Excel bằng công thức. Bạn sẽ học cách tách văn bản dựa theo dấu phẩy, khoảng trắng hoặc bất kỳ dấu phân cách nào khác, và làm thế nào để phân chia chuỗi thành văn bản và số.
Làm thế nào để chia văn bản trong Excel bằng cách sử dụng công thức:
Để tách chuỗi trong Excel, bạn thường 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ố công thức có thể phức tạp, nhưng thực tế logic là khá đơn giản, và các ví dụ sau đây sẽ cung cấp cho bạn một số đầu mối.
Tách chuỗi bằng dấu phẩy, dấu hai chấm, dấu gạch chéo, dấu gạch ngang hoặc dấu phân cách khác
Khi phân chia các ô trong Excel, việc chính là xác định vị trí của dấu phân cách trong chuỗi văn bản. Tùy thuộc vào công việc 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 biệt chữ hoa chữ thường hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi bạn có vị trí của dấu phân cách, sử dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi văn bản.
Để hiểu rõ hơn, hãy xem xét ví dụ sau đây:
Giả sử bạn có một danh sách các SKU của mẫu Loại-Màu-Kích thước, và bạn muốn chia tách cột thành 3 cột riêng biệt:
Chia 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ự trước dấu nối đầu tiên), chèn công thức sau trong B2, và sau đó sao chép 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 dấu nối đầu tiên (“-“) trong chuỗi và chức năng LEFT sẽ chiết tất cả các ký tự còn lại (bạn trừ 1 từ vị trí của dấu nối bởi vì bạn không muốn có dấu nối).
Chức năng LEFT sẽ chiết tất cả các ký tự còn lại
Để trích xuất màu sắc (tất cả các ký tự giữa các dấu gạch nối thứ 2 và thứ 3), hãy nhập công thức sau trong C2, và sau đó sao chép nó xuống các ô khác:
=MID(“-“, A2) – 1 (SEARCH(“-“, A2) +1) – SEARCH (“-“, A2) – 1)
Công thức hàm MID
Như bạn có thể biết, hàm MID có cú pháp sau:
MID (văn bản, start_num, num_chars)
Nơi:
Văn bản – nơi để trích xuất văn bản từ.
Start_num – vị trí của kí tự đầu tiên để trích xuất.
Num_chars – số ký tự để trích xuất.
Trong công thức trên, văn bản được trích ra từ ô A2, và 2 đối số khác được tính bằng cách sử dụng 4 hàm SEARCH khác:
Số bắt đầu (start_num) là vị trí của dấu nối đầu tiên +1:
SEARCH (“-“, A2) + 1
Số ký tự để trích xuất (num_chars): sự khác biệt giữa vị trí của dấu nối thứ hai và dấu nối đầu tiên, trừ đi 1:
SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1
Để trích xuất kích thước (tất cả các ký tự sau dấu nối thứ 3), hãy nhập công thức 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 dài của chuỗi, từ đó bạn trừ đi vị trí của dấu nối thứ hai. Sự khác biệt là số ký tự sau dấu nối thứ hai và hàm RIGHT chiết xuất chúng.
Trong một cách tương tự, bạn có thể phân chia cột bởi bất kỳ kí tự nào khác. Tất cả bạn phải làm là thay thế “-” bằng ký tự phân cách bắt buộc, ví dụ như dấu cách (“”), dấu gạch chéo (“/”), dấu hai chấm (“;”), dấu chấm phẩy (“;”) và vân vân.
Mẹo. Trong các công thức trên, +1 và -1 tương ứng với số ký tự trong dấu phâ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ý tự, ví dụ: Dấu phẩy và khoảng trắng, sau đó chỉ cung cấp dấu phẩy (“,”) cho hàm SEARCH, và sử dụng +2 và -2 thay vì +1 và -1.
Làm thế nào để phân chia chuỗi bằng cách ngắt dòng trong Excel:
Để chia văn bản bằng khoảng trắng, hãy sử dụng các công thức tương tự như công thức được minh họa trong ví dụ trước. Sự khác biệt duy nhất là bạn cần chức năng CHAR để cung cấp cho ký tự ngắt dòng vì bạn không thể gõ trực tiếp vào công thức. Giả sử, các ô mà bạn muốn chia nhỏ trông tương tự như sau:
Lấy công thức từ ví dụ trước và thay dấu gạch nối (“-“) bằng CHAR (10) trong đó 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) =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 thế nào để phân chia văn bản và số trong Excel:
Ví dụ 1. Chia chuỗi của loại ‘văn bản + số’
Giả sử bạn có một cột các chuỗi với văn bản và số kết hợp, trong đó một số luôn luôn theo sau văn bản. Bạn muốn phá vỡ các chuỗi ban đầu để văn bản và số xuất hiện trong các ô riêng biệt, như sau:
Để trích xuất các số, sử dụng công thức mảng sau đây, được hoàn thành bằng cách 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))
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, công thức tìm kiếm mọi số có thể từ 0 đến 9 trong chuỗi nguồn, tính số lượng và trả về nhiều ký tự từ ký tự cuối chuỗi ban đầu.
Và đây là công thức chi tiết phân rã:
Trước tiên, bạn sử dụng các hàm LEN và SUBSTITUTE để tìm ra số lần xuất hiện một số nào đó trong chuỗi gốc – thay thế số bằng một chuỗi rỗng (“”), và sau đó trừ đi chiều dài của chuỗi mà không có số đó từ tổng số Chiều dài của chuỗi ban đầu. Bởi vì đó là một công thức mảng, thao tác này được thực hiệ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ả các lần xuất hiện của tất cả các chữ số trong chuỗi nguồn.
Cuối cùng, hàm RIGHT trả về nhiều ký tự từ phía bên phải của chuỗi.
Công thức để trích xuất văn bản (hàm LEFT). Bạn tính toán bao nhiêu ký tự văn bản chuỗi chứa bằng cách trừ số chữ số chiết xuất (C2) từ chiều dài của chuỗi gốc (A2). Sau đó, bạn sử dụng hàm LEFT để trả về nhiều ký tự từ đầu chuỗi.
Một giải pháp khác (công thức không có mảng)
Giải pháp thay thế sẽ sử dụng 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ù công thức cũng chứa một hằng số mảng, đó là một công thức bình thường được hoàn thành theo cách thông thường bằng cách nhấn phím Enter.
=RIGHT(B2, LEN(A1)-B2+1)
Khi vị trí của số đầu tiên được tìm thấy, bạn có thể tách văn bản và số bằng cách sử dụng các công thức LEFT và RIGHT rất đơn giản (nhớ rằng một số luôn xuất hiện sau văn bản):
= LEFT (A2, B2-1)
Để loại bỏ cột helper giữ vị trí số bắt đầu, bạn có thể nhúng hàm MIN vào các hàm LEFT và 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 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 vị trí của số thứ nhất
Bạn 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, làm cho nó tìm từng số trong hằng số mảng bên trong bản gốc, và trả lại vị trí của chúng. Bởi vì hằng số mảng chứa 10 chữ số, mảng kết quả cũng chứa 10 mục.
Hàm MIN lấy mảng kết quả và trả về giá trị nhỏ nhất, tương ứng với vị trí của số đầu tiên trong chuỗi ban đầu.
Ngoài ra, chúng tôi sử dụng một cấu trúc đặc biệt (A2 & “0123456789”) để ghép mỗi số có thể với chuỗi ban đầu. Cách này thực hiện vai trò của IFERROR và cho phép chúng tôi tránh lỗi khi một số nhất định trong hằng số mảng không được tìm thấy trong chuỗi nguồn. Trong trường hợp này, công thức trả về vị trí “giả mạo” bằng chuỗi chiều dài từ 1 ký tự trở lên. Điều này cho phép hàm LEFT trích xuất văn bản và hàm RIGHT trả về một chuỗi rỗng nếu chuỗi gốc không chứa bất kỳ số nào, như trong dòng 7 hình ở 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à cách chúng tôi có:
5 là ký tự thứ 8 trong chuỗi gốc, và 0 là ký tự thứ 7, đó là lý do tại sao mục đầu tiên của mảng kết quả là “7”, và thứ sáu là “8”.
Không có mục nào khác của hằng số mảng được tìm thấy trong A2, và do đó 8 phần khác của mảng kết quả đại diện cho các vị trí của các chữ số tương ứng trong chuỗi nối (A2 & “0123456789”).
Ví dụ 2. Chia chuỗi của loại ‘số + văn bản’
Nếu bạn đang tách các ô nơi văn bản xuất hiện sau một số, bạn có thể trích xuất các số với công thức 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ư công thức mảng từ ví dụ trước, ngoại trừ bạn sử dụng hàm LEFT thay vì RIGHT, bởi vì trong trường hợp này số luôn xuất hiện ở phía bên trái của chuỗi. Một khi bạn đã có các con số, trích xuất vă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))
Ví dụ 3. Trích xuất chỉ số từ chuỗi số ‘số văn bản’
Nếu công việc của bạn đòi hỏi phải trích xuất tất cả các số từ một chuỗi trong định dạng ‘number-text-number’, bạn có thể sử dụng công thức sau đây được gợi ý bởi một trong những chuyên gia của MrExcel:
= SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)) * ROW (TRỰC TIẾP (“1:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)
Trường hợp A2 là chuỗi văn bản ban đầu.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của chúng tôi Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc…
Làm Thế Nào Để Tách Chuỗi Văn Bản Trong Excel Theo Dấu Phẩy, Khoảng Trắng, Ký Tự Xác Định
Trong bài viết này, blog.hocexcel.online sẽ giải thích cách tách các ô trong Excel bằng công thức. Bạn sẽ học cách tách văn bản dựa theo dấu phẩy, khoảng trắng hoặc bất kỳ dấu phân cách nào khác, và làm thế nào để phân chia chuỗi thành văn bản và số.
Làm thế nào để chia văn bản trong Excel bằng cách sử dụng công thức:
Để tách chuỗi trong Excel, bạn thường 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ố công thức có thể phức tạp, nhưng thực tế logic là khá đơn giản, và các ví dụ sau đây sẽ cung cấp cho bạn một số đầu mối.
Tách chuỗi bằng dấu phẩy, dấu hai chấm, dấu gạch chéo, dấu gạch ngang hoặc dấu phân cách khác
Khi phân chia các ô trong Excel, việc chính là xác định vị trí của dấu phân cách trong chuỗi văn bản. Tùy thuộc vào công việc 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 biệt chữ hoa chữ thường hoặc hàm Find có phân biệt chữ hoa chữ thường. Một khi bạn có vị trí của dấu phân cách, sử dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi văn bản.
Để hiểu rõ hơn, hãy xem xét ví dụ sau đây:
Giả sử bạn có một danh sách các SKU của mẫu Loại-Màu-Kích thước, và bạn muốn chia 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ự trước dấu nối đầu tiên), chèn công thức sau trong B2, và sau đó sao chép 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 dấu nối đầu tiên (“-“) trong chuỗi và chức năng LEFT sẽ chiết tất cả các ký tự còn lại (bạn trừ 1 từ vị trí của dấu nối bởi vì bạn không muốn có dấu nối).
Để trích xuất màu sắc (tất cả các ký tự giữa các dấu gạch nối thứ 2 và thứ 3), hãy nhập công thức sau trong C2, và sau đó sao chép nó xuống các ô khác:
=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1
MID (văn bản, start_num, num_chars)
Nơi:
Văn bản – nơi để trích xuất văn bản từ.
Start_num – vị trí của kí tự đầu tiên để trích xuất.
Num_chars – số ký tự để trích xuất.
Trong công thức trên, văn bản được trích ra từ ô A2, và 2 đối số khác được tính bằng cách sử dụng 4 hàm SEARCH khác:
Số bắt đầu (start_num) là vị trí của dấu nối đầu tiên +1:
SEARCH (“-“, A2) + 1
Số ký tự để trích xuất (num_chars): sự khác biệt giữa vị trí của dấu nối thứ hai và dấu nối đầu tiên, trừ đi 1:
SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1
Để trích xuất kích thước (tất cả các ký tự sau dấu nối thứ 3), hãy nhập công thức 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 dài của chuỗi, từ đó bạn trừ đi vị trí của dấu nối thứ hai. Sự khác biệt là số ký tự sau dấu nối thứ hai và hàm RIGHT chiết xuất chúng.
Mẹo. Trong các công thức trên, +1 và -1 tương ứng với số ký tự trong dấu phâ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ý tự, ví dụ: Dấu phẩy và khoảng trắng, sau đó chỉ cung cấp dấu phẩy (“,”) cho hàm SEARCH, và sử dụng +2 và -2 thay vì +1 và -1.
Làm thế nào để phân chia chuỗi bằng cách ngắt dòng trong Excel:
Để chia văn bản bằng khoảng trắng, hãy sử dụng các công thức tương tự như công thức được minh họa trong ví dụ trước. Sự khác biệt duy nhất là bạn cần chức năng CHAR để cung cấp cho ký tự ngắt dòng vì bạn không thể gõ trực tiếp vào công thức. Giả sử, các ô mà bạn muốn chia nhỏ trông tương tự như sau:
Để trích xuất tên mặt hàng:
=LEFT(A2, SEARCH(CHAR(10),A2,1)-1) =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ả:
Ví dụ 1. Chia chuỗi của loại ‘văn bản + số’
Giả sử bạn có một cột các chuỗi với văn bản và số kết hợp, trong đó một số luôn luôn theo sau văn bản. Bạn muốn phá vỡ các chuỗi ban đầu để văn bản và số xuất hiện trong các ô riêng biệt, như sau:
= 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))
Công thức để trích xuất số (hàm RIGHT). Về cơ bản, công thức tìm kiếm mọi số có thể từ 0 đến 9 trong chuỗi nguồn, tính số lượng và trả về nhiều ký tự từ ký tự cuối chuỗi ban đầu.
Và đây là công thức chi tiết phân rã:
Trước tiên, bạn sử dụng các hàm LEN và SUBSTITUTE để tìm ra số lần xuất hiện một số nào đó trong chuỗi gốc – thay thế số bằng một chuỗi rỗng (“”), và sau đó trừ đi chiều dài của chuỗi mà không có số đó từ tổng số Chiều dài của chuỗi ban đầu. Bởi vì đó là một công thức mảng, thao tác này được thực hiệ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ả các lần xuất hiện của tất cả các chữ số trong chuỗi nguồn.
Cuối cùng, hàm RIGHT trả về nhiều ký tự từ phía bên phải của chuỗi.
Công thức để trích xuất văn bản (hàm LEFT). Bạn tính toán bao nhiêu ký tự văn bản chuỗi chứa bằng cách trừ số chữ số chiết xuất (C2) từ chiều dài của chuỗi gốc (A2). Sau đó, bạn sử dụng hàm LEFT để trả về nhiều ký tự từ đầu chuỗi.
Một giải pháp khác (công thức không có mảng)
Giải pháp thay thế sẽ sử dụng 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ù công thức cũng chứa một hằng số mảng, đó là một công thức bình thường được hoàn thành theo cách thông thường bằng cách nhấn phím Enter.
Khi vị trí của số đầu tiên được tìm thấy, bạn có thể tách văn bản và số bằng cách sử dụng các công thức LEFT và RIGHT rất đơn giản (nhớ rằng một số luôn xuất hiện sau văn bản):
= LEFT (A2, B2-1)
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 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 vị trí của số thứ nhất
Bạn 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, làm cho nó tìm từng số trong hằng số mảng bên trong bản gốc, và trả lại vị trí của chúng. Bởi vì hằng số mảng chứa 10 chữ số, mảng kết quả cũng chứa 10 mục.
Hàm MIN lấy mảng kết quả và trả về giá trị nhỏ nhất, tương ứng với vị trí của số đầu tiên trong chuỗi ban đầu.
Ngoài ra, chúng tôi sử dụng một cấu trúc đặc biệt (A2 & “0123456789”) để ghép mỗi số có thể với chuỗi ban đầu. Cách này thực hiện vai trò của IFERROR và cho phép chúng tôi tránh lỗi khi một số nhất định trong hằng số mảng không được tìm thấy trong chuỗi nguồn. Trong trường hợp này, công thức trả về vị trí “giả mạo” bằng chuỗi chiều dài từ 1 ký tự trở lên. Điều này cho phép hàm LEFT trích xuất văn bản và hàm RIGHT trả về một chuỗi rỗng nếu chuỗi gốc không chứa bất kỳ số nào, như trong dòng 7 hình ở 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à cách chúng tôi có:
5 là ký tự thứ 8 trong chuỗi gốc, và 0 là ký tự thứ 7, đó là lý do tại sao mục đầu tiên của mảng kết quả là “7”, và thứ sáu là “8”.
Không có mục nào khác của hằng số mảng được tìm thấy trong A2, và do đó 8 phần khác của mảng kết quả đại diện cho các vị trí của các chữ số tương ứng trong chuỗi nối (A2 & “0123456789”).
Ví dụ 2. Chia chuỗi của loại ‘số + văn bản’
Nếu bạn đang tách các ô nơi văn bản xuất hiện sau một số, bạn có thể trích xuất các số với công thức 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ư công thức mảng từ ví dụ trước, ngoại trừ bạn sử dụng hàm LEFT thay vì RIGHT, bởi vì trong trường hợp này số luôn xuất hiện ở phía bên trái của chuỗi. Một khi bạn đã có các con số, trích xuất vă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))
Ví dụ 3. Trích xuất chỉ số từ chuỗi số ‘số văn bản’
Nếu công việc của bạn đòi hỏi phải trích xuất tất cả các số từ một chuỗi trong định dạng ‘number-text-number’, bạn có thể sử dụng công thức sau đây được gợi ý bởi một trong những chuyên gia của MrExcel:
= SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)) * ROW (TRỰC TIẾP (“1:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)
Trường hợp A2 là chuỗi văn bản ban đầu.
Cách Tìm Chuỗi Văn Bản Trong Một Chuỗi Khác Bằng Hàm Find, Findb Trong Excel
=FIND(find_text, within_text, [start_num])
=FINDB(find_text, within_text, [start_num])
Trong đó:
FIND là tên hàm dùng để định vị một chuỗi văn bản nằm trong chuỗi văn bản thứ hai và trả về số của vị trí bắt đầu của chuỗi văn bản thứ nhất tính từ ký tự thứ nhất của chuỗi văn bản thứ hai.
FINDB là hàm FIND dùng với những ngôn ngữ sử dụng bộ ký tự byte kép (DBCS)
Find_text: Là chuỗi văn bản cần tìm. Đây là giá trị bắt buộc.
Within_text: Là chuỗi văn bản chứa chuỗi văn bản cần tìm. Đây là giá trị bắt buộc.
Start_num: Là vị trí ký tự bắt đầu tìm trong chuỗi văn bản within_text. Ký tự thứ nhất trong chuỗi within_text là ký tự số 1. Nếu bỏ qua giá trị start_num thì mặc định nó sẽ lấy là 1.
Ví dụ và cách sử dụng hàm FIND và FINDB
Ví dụ 1
Ảnh dưới là ví dụ mô tả công thức FIND tìm ký tự M đầu tiên trong chuỗi và trả về thứ tự của ký tự đó trong chuỗi.
Ví dụ 2
Với chuỗi văn bản tham chiếu như trên, bạn cần tìm vị trí của ký tự “n” từ ký tự thứ 5 của chuỗi, công thức hàm sẽ như sau:
Ví dụ 3: Sử dụng kết hợp hàm FIND và hàm MID/LEFT/RIGHT
Bạn cần trích xuất n ký tự từ chuỗi văn bản gốc miễn sao trước dấu thăng # của chuỗi để ra chủng loại sản phẩm.
Trước tiên dùng hàm FIND để xác định thứ tự của giá trị # trong chuỗi dữ liệu gốc.
Tiếp theo dùng hàm LEFT để lấy các ký tự từ đầu cho đến trước dấu # bằng cách trừ thứ tự của # tìm đc ở trên cho 1.
Công thức để lấy thông tin cho cột Sản phẩm sẽ như sau: B4 =LEFT(A4,FIND(“#”,A4)-1). Sao chép công thức cho toàn bộ ô trong cột bạn thu được kết quả:
Bạn sao chép công thức cho các ô còn lại và được kết quả:
Lưu ý
Nếu như đối số Within_text và Find_text là dạng chữ, bạn phải đặt vào dấu nháy kép “”.
FIND và FINDB phân biệt chữ hoa chữ thường và không cho phép dùng ký tự đại diện.
Nếu find_text không xuất hiện trong within_text, thì hàm FIND và FINDB trả về giá trị lỗi #VALUE!.
Nếu start_num không lớn hơn không, thì hàm FIND và FINDB trả về giá trị lỗi #VALUE!.
Nếu start_num lớn hơn độ dài của within_text, thì hàm FIND và FINDB trả về giá trị lỗi #VALUE!.
Bạn đang xem bài viết Cách Xóa Ký Tự Trong Chuỗi Từ Văn Bản Trên Excel trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!