Hướng dẫn excel remove characters from right until space - excel loại bỏ các ký tự từ bên phải cho đến khoảng trắng

• Lời khuyên Excel
• Chức năng Excel
• Công thức Excel
• Biểu đồ Excel
• Lời khuyên từ
• Lời khuyên Outlook

Trong một vài bài viết gần đây, chúng tôi đã xem xét các cách khác nhau để loại bỏ các ký tự khỏi các chuỗi trong Excel. Hôm nay, chúng tôi sẽ điều tra thêm một trường hợp sử dụng - cách xóa mọi thứ trước hoặc sau một nhân vật cụ thể.

Xóa văn bản trước, sau hoặc giữa 2 ký tự bằng Find & thay thế

Đối với các thao tác dữ liệu trong nhiều ô, hãy tìm và thay thế là công cụ phù hợp. Để xóa một phần của chuỗi trước hoặc theo một ký tự cụ thể, đây là các bước để thực hiện:

1. Chọn tất cả các ô nơi bạn muốn xóa văn bản.
2. Nhấn Ctrl + H để mở hộp thoại Tìm và thay thế.Ctrl + H to open the Find and Replace dialog.
3. Trong hộp tìm kiếm, nhập một trong các kết hợp sau:
   • Để loại bỏ văn bản trước một ký tự đã cho, hãy nhập ký tự trước dấu hoa thị [*char].before a given character, type the character preceded by an asterisk [*char].
   • Để xóa văn bản sau một ký tự nhất định, hãy nhập ký tự theo sau là dấu hoa thị [char*].after a certain character, type the character followed by an asterisk [char*].
   • Để xóa một chuỗi con giữa hai ký tự, nhập dấu hoa thị được bao quanh bởi 2 ký tự [char*char].between two characters, type an asterisk surrounded by 2 characters [char*char].
4. Để trống thay thế.
5. Nhấp vào Thay thế tất cả.

Ví dụ: để xóa mọi thứ sau khi làm dấu phẩy bao gồm cả dấu phẩy, hãy đặt dấu phẩy và dấu hoa thị [,*] vào hộp tìm kiếm nào và bạn sẽ nhận được kết quả sau:everything after a comma including the comma itself, put a comma and an asterisk sign [,*] in the Find what box, and you'll get the following result:

Để xóa một chuỗi con trước dấu phẩy, hãy nhập dấu hoa thị, dấu phẩy và không gian [*,] vào hộp tìm kiếm.before a comma, type an asterisk, a comma, and a space [*, ] in the Find what box.

Xin lưu ý rằng chúng tôi đang thay thế không chỉ là dấu phẩy mà là dấu phẩy và không gian để ngăn không gian hàng đầu trong kết quả. Nếu dữ liệu của bạn được phân tách bằng dấu phẩy mà không có khoảng trắng, thì hãy sử dụng dấu hoa thị theo sau là dấu phẩy [*,].a comma and a space to prevent leading spaces in the results. If your data is separated by commas without spaces, then use an asterisk followed by a comma [*,].

Để xóa văn bản giữa hai dấu phẩy, sử dụng dấu hoa thị được bao quanh bởi dấu phẩy [,*,].between two commas, use an asterisk surrounded by commas [,*,].

Mẹo. Nếu bạn muốn có tên và số điện thoại được phân tách bằng dấu phẩy, thì hãy nhập dấu phẩy [,] vào trường thay thế. If you'd rather have the names and phone numbers separated by a comma, then type a comma [,] in the Replace with field.

Xóa một phần của văn bản bằng cách sử dụng flash fill

Trong các phiên bản hiện đại của Excel [2013 trở lên], có một cách dễ dàng hơn để xóa văn bản trước hoặc theo một ký tự cụ thể - tính năng điền flash. Đây là cách nó hoạt động:

1. Trong một ô bên cạnh ô đầu tiên với dữ liệu của bạn, hãy nhập kết quả dự kiến ​​và nhấn Enter.Enter.
2. Bắt đầu gõ một giá trị thích hợp trong ô tiếp theo. Khi Excel cảm thấy mẫu trong các giá trị bạn đang nhập, nó sẽ hiển thị bản xem trước cho các ô còn lại theo cùng một mẫu.
3. Nhấn phím Enter để chấp nhận đề xuất.Enter key to accept the suggestion.

Done!

Xóa văn bản bằng các công thức

Trong Microsoft Excel, nhiều thao tác dữ liệu được thực hiện bằng cách sử dụng các tính năng sẵn có cũng có thể được thực hiện bằng một công thức. Không giống như các phương thức trước đó, các công thức không thực hiện bất kỳ thay đổi nào đối với dữ liệu gốc và cho bạn quyền kiểm soát nhiều hơn đối với kết quả.

Cách xóa mọi thứ sau một nhân vật cụ thể

Để xóa văn bản sau một ký tự cụ thể, công thức chung là:

Trái [di động, tìm kiếm ["char", cell] -1]

Ở đây, chúng tôi sử dụng chức năng tìm kiếm để có được vị trí của ký tự và chuyển nó sang hàm bên trái, vì vậy nó trích xuất số lượng ký tự tương ứng từ đầu chuỗi. Một ký tự bị trừ khỏi số được trả về bằng cách tìm kiếm để loại trừ dấu phân cách khỏi kết quả.

Ví dụ: để xóa một phần của chuỗi sau dấu phẩy, bạn nhập công thức bên dưới trong B2 và kéo nó xuống qua B7:

=LEFT[A2, SEARCH[",", A2] -1]

Cách xóa mọi thứ trước một ký tự cụ thể

Để xóa một phần của chuỗi văn bản trước một ký tự nhất định, công thức chung là:

Phải [ô, LEN [ô] - Tìm kiếm ["Char", Cell]]

Ở đây, chúng tôi một lần nữa tính toán vị trí của ký tự mục tiêu với sự trợ giúp của tìm kiếm, trừ nó từ tổng chiều dài chuỗi được trả về bởi Len và chuyển sự khác biệt sang chức năng phù hợp, do đó, nó kéo được nhiều ký tự từ cuối chuỗi.

Ví dụ: để xóa văn bản trước dấu phẩy, công thức là:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]

Trong trường hợp của chúng tôi, dấu phẩy được theo sau bởi một nhân vật không gian. Để tránh các không gian hàng đầu trong kết quả, chúng tôi bọc công thức cốt lõi trong hàm trang trí:

=TRIM[RIGHT[A2, LEN[A2] - SEARCH[",", A2]]]

Notes:

• Cả hai ví dụ trên đều cho rằng chỉ có một phiên bản của dấu phân cách trong chuỗi gốc. Nếu có nhiều lần xuất hiện, văn bản sẽ bị xóa trước/sau trường hợp đầu tiên.one instance of the delimiter in the original string. If there are multiple occurrences, text will be removed before/after the first instance.
• Hàm tìm kiếm không nhạy cảm trường hợp, có nghĩa là nó không có sự khác biệt giữa các ký tự chữ thường và chữ hoa. Nếu nhân vật cụ thể của bạn là một chữ cái và bạn muốn phân biệt trường hợp chữ cái, thì hãy sử dụng chức năng tìm thấy trường hợp nhạy cảm thay vì tìm kiếm.not case-sensitive, meaning it makes no difference between lowercase and uppercase characters. If your specific character is a letter and you want to distinguish the letter case, then use the case-sensitive FIND function instead of SEARCH.

Cách xóa văn bản sau lần xuất hiện thứ n của một ký tự

Trong tình huống khi một chuỗi nguồn chứa nhiều phiên bản của dấu phân cách, bạn có thể có nhu cầu xóa văn bản sau một trường hợp cụ thể. Đối với điều này, hãy sử dụng công thức sau:

Trái [di động, tìm ["#", thay thế [ô, "char", "#", n]] -1]

Trong đó n là sự xuất hiện của nhân vật sau đó để xóa văn bản.

Logic nội bộ của công thức này yêu cầu sử dụng một số ký tự không xuất hiện ở bất cứ đâu trong dữ liệu nguồn, ký hiệu băm [#] trong trường hợp của chúng tôi. Nếu ký tự này xảy ra trong tập dữ liệu của bạn, thì hãy sử dụng thứ khác thay vì "#".

Ví dụ: để xóa mọi thứ sau dấu phẩy thứ 2 trong A2 [và chính dấu phẩy], công thức là:

=LEFT[A2, FIND["#", SUBSTITUTE[A2, ",", "#", 2]] -1]

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

Phần chính của công thức là hàm tìm tính toán vị trí của dấu phân cách thứ n [dấu phẩy trong trường hợp của chúng tôi]. Đây là cách:

Chúng tôi thay thế dấu phẩy thứ 2 trong A2 bằng biểu tượng băm [hoặc bất kỳ ký tự nào khác không tồn tại trong dữ liệu của bạn] với sự trợ giúp của sự thay thế:

SUBSTITUTE[A2, ",", "#", 2]

Chuỗi kết quả đi đến đối số thứ 2 của tìm, vì vậy nó tìm thấy vị trí của "#" trong chuỗi đó:

FIND["#", "Emma, Design# [102] 123-4568"]

Tìm cho chúng tôi biết rằng "#" là ký tự thứ 13 trong chuỗi. Để biết số lượng ký tự trước nó, chỉ cần trừ 1 và bạn sẽ nhận được 12 là kết quả:

FIND["#", SUBSTITUTE[A2, ",", "#", 2]] - 1

Số này chuyển trực tiếp đến đối số Num_chars của trái yêu cầu nó kéo 12 ký tự đầu tiên từ A2:

=LEFT[A2, 12]

Đó là nó!

Cách xóa văn bản trước khi xuất hiện thứ n của một ký tự

Công thức chung để loại bỏ một chuỗi con trước một ký tự nhất định là:

Phải [thay thế [ô, "char", "#", n], len [ô] - tìm ["#", thay thế [ô, "char", "#", n]] -1]

Ví dụ: để loại bỏ văn bản trước dấu phẩy thứ 2 trong A2, công thức là:

=RIGHT[SUBSTITUTE[A2, ",", "#", 2], LEN[A2] - FIND["#", SUBSTITUTE[A2, ",", "#", 2]] -1]

Để loại bỏ một không gian hàng đầu, chúng tôi một lần nữa sử dụng chức năng trang trí như một trình bao bọc:

=TRIM[RIGHT[SUBSTITUTE[A2, ",", "#", 2], LEN[A2] - FIND["#", SUBSTITUTE[A2, ",", "#", 2]]]]

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

Phần chính của công thức là hàm tìm tính toán vị trí của dấu phân cách thứ n [dấu phẩy trong trường hợp của chúng tôi]. Đây là cách:

Chúng tôi thay thế dấu phẩy thứ 2 trong A2 bằng biểu tượng băm [hoặc bất kỳ ký tự nào khác không tồn tại trong dữ liệu của bạn] với sự trợ giúp của sự thay thế:

SUBSTITUTE[A2, ",", "#", 2]

Chuỗi kết quả đi đến đối số thứ 2 của tìm, vì vậy nó tìm thấy vị trí của "#" trong chuỗi đó:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]1

Tìm cho chúng tôi biết rằng "#" là ký tự thứ 13 trong chuỗi. Để biết số lượng ký tự trước nó, chỉ cần trừ 1 và bạn sẽ nhận được 12 là kết quả:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]2

Số này chuyển trực tiếp đến đối số Num_chars của trái yêu cầu nó kéo 12 ký tự đầu tiên từ A2:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]3

Đó là nó!

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]4

Cách xóa văn bản trước khi xuất hiện thứ n của một ký tự

Công thức chung để loại bỏ một chuỗi con trước một ký tự nhất định là:

Phải [thay thế [ô, "char", "#", n], len [ô] - tìm ["#", thay thế [ô, "char", "#", n]] -1]

Ví dụ: để loại bỏ văn bản trước dấu phẩy thứ 2 trong A2, công thức là:

Để loại bỏ một không gian hàng đầu, chúng tôi một lần nữa sử dụng chức năng trang trí như một trình bao bọc:

Tóm lại, chúng tôi tìm ra có bao nhiêu ký tự sau dấu phân cách thứ n và trích xuất một chuỗi con có độ dài tương ứng từ bên phải. Dưới đây là công thức bị phá vỡ:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]5

Đầu tiên, chúng tôi thay thế dấu phẩy thứ 2 trong A2 bằng biểu tượng băm:

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

Phần chính của công thức là hàm tìm tính toán vị trí của dấu phân cách thứ n [dấu phẩy trong trường hợp của chúng tôi]. Đây là cách:

Chúng tôi thay thế dấu phẩy thứ 2 trong A2 bằng biểu tượng băm [hoặc bất kỳ ký tự nào khác không tồn tại trong dữ liệu của bạn] với sự trợ giúp của sự thay thế:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]6

Chuỗi kết quả đi đến đối số thứ 2 của tìm, vì vậy nó tìm thấy vị trí của "#" trong chuỗi đó:

Trừ số trên khỏi tổng chiều dài chuỗi [38 ký tự]:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]7

Bạn và bạn sẽ nhận được 3, đó là tổng số dấu phẩy trong A2 [và cũng là số thứ tự của dấu phẩy cuối cùng].

Tiếp theo, bạn sử dụng kết hợp đã quen thuộc của các hàm tìm và thay thế để có được vị trí của dấu phẩy cuối cùng trong chuỗi. Số ví dụ [dấu phẩy thứ 3 trong trường hợp của chúng tôi] được cung cấp bởi công thức thay thế LEN nêu trên:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]8

Có vẻ như dấu phẩy thứ 3 là ký tự thứ 23 trong A2, có nghĩa là chúng ta cần trích xuất 22 ký tự trước nó. Vì vậy, chúng tôi đặt công thức trên trừ 1 trong đối số Num_chars của trái:

=RIGHT[A2, LEN[A2] - SEARCH[",", A2]]9

Cách xóa văn bản trước lần xuất hiện cuối cùng của một ký tự

Để xóa mọi thứ trước phiên bản cuối cùng của một ký tự cụ thể, công thức chung là:

Phải [Cell, Len [ô] - Tìm ["#", thay thế [ô, "char", "#", len [ô] - len [thay thế [ô, "char", ""]]]]]]]

Trong bảng mẫu của chúng tôi, để xóa văn bản trước dấu phẩy cuối cùng, công thức có dạng này:

=TRIM[RIGHT[A2, LEN[A2] - SEARCH[",", A2]]]0

Như một liên lạc hoàn thiện, chúng tôi làm tổ nó vào chức năng trang trí để loại bỏ các không gian hàng đầu:

=TRIM[RIGHT[A2, LEN[A2] - SEARCH[",", A2]]]1

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

Tóm lại, chúng tôi có được vị trí của dấu phẩy cuối cùng như được giải thích trong ví dụ trước và trừ nó khỏi tổng chiều dài của chuỗi:

=TRIM[RIGHT[A2, LEN[A2] - SEARCH[",", A2]]]2

Kết quả là, chúng tôi nhận được số lượng ký tự sau dấu phẩy cuối cùng và chuyển nó sang đúng hàm, do đó, nó mang lại nhiều ký tự từ cuối chuỗi.

Chức năng tùy chỉnh để xóa văn bản ở hai bên của ký tự

Như bạn đã thấy trong các ví dụ trên, bạn có thể giải quyết hầu hết mọi trường hợp sử dụng bằng cách sử dụng các hàm gốc của Excel trong các kết hợp khác nhau. Vấn đề là bạn cần nhớ về một số công thức khó khăn. Hmm, điều gì sẽ xảy ra nếu chúng ta viết chức năng của riêng mình để bao gồm tất cả các kịch bản? Nghe có vẻ như một ý tưởng tốt. Vì vậy, hãy thêm mã VBA sau vào sổ làm việc của bạn [các bước chi tiết để chèn VBA vào Excel ở đây]:

Hàm loại bỏ [asstring str, asstring phân định, xuất hiện asinteger, is_after Asboolean] Dim delimiter_num, start_num, delimiter_len asintegerdim str_result asstring delimiter_num = 0 start start_num, str, delimiter, vbTextCompare] nếu 0 Xóa.Remove by Position feature located on the Ablebits Data tab > Text group > Remove.

Dưới đây, chúng tôi sẽ bao gồm hai kịch bản phổ biến nhất.

Xóa mọi thứ trước hoặc sau khi văn bản nhất định

Giả sử tất cả các chuỗi nguồn của bạn chứa một số từ hoặc văn bản phổ biến và bạn muốn xóa mọi thứ trước hoặc sau văn bản đó. Để hoàn thành, chọn dữ liệu nguồn của bạn, chạy công cụ Xóa By By và định cấu hình nó giống như hiển thị bên dưới:

1. Chọn tất cả các ký tự trước văn bản hoặc tất cả các ký tự sau tùy chọn văn bản và nhập văn bản [hoặc ký tự] khóa vào hộp bên cạnh.
2. Tùy thuộc vào việc các chữ cái chữ hoa và chữ thường nên được coi là khác nhau hay cùng một ký tự, hãy kiểm tra hoặc bỏ chọn hộp nhạy cảm trường hợp.
3. Nhấn Xóa.

Trong ví dụ này, chúng tôi đang xóa tất cả các ký tự trước từ "lỗi" trong các ô A2: A8:

Và nhận được chính xác kết quả mà chúng tôi đang tìm kiếm:

Xóa văn bản giữa hai ký tự

Trong tình huống khi thông tin không liên quan nằm giữa 2 ký tự cụ thể, đây là cách bạn có thể nhanh chóng xóa nó:

1. Chọn Xóa tất cả các chuỗi con và nhập hai ký tự vào các hộp dưới đây.
2. Nếu các ký tự "giữa" cũng nên được gỡ bỏ, hãy kiểm tra hộp Delimiters bao gồm.
3. Nhấp vào Xóa.
   

Ví dụ, chúng tôi xóa mọi thứ giữa hai ký tự Tilde [~] và nhận được các chuỗi được làm sạch hoàn hảo như kết quả:

Để thử các tính năng hữu ích khác bao gồm với công cụ đa chức năng này, tôi khuyến khích bạn tải xuống phiên bản đánh giá ở cuối bài này. Cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Tải xuống có sẵn

Xóa các ký tự đầu tiên hoặc cuối cùng - Ví dụ [tệp .xlsm] Ultimate Suite - Phiên bản dùng thử [tệp .exe]
Ultimate Suite - trial version [.exe file]

Bạn cũng có thể quan tâm

Làm cách nào để loại bỏ các ký tự từ bên phải trong Excel?

Làm cách nào để loại bỏ tất cả các ký tự trước một không gian trong Excel?

Làm cách nào để trích xuất văn bản trước không gian cuối cùng trong Excel?