Hướng dẫn how to remove part of text in a cell in excel - cách xóa một phần văn bản trong ô trong excel

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:

Chuỗi kết quả đi đến đối số văn bản của quyền:

Tiếp theo, chúng ta cần xác định số lượng ký tự để trích xuất từ ​​cuối chuỗi. Đối với điều này, chúng tôi tìm thấy vị trí của ký hiệu băm trong chuỗi trên (là 13):

=RIGHT(A2, LEN(A2) - SEARCH(",", A2))6

Và trừ nó từ tổng chiều dài chuỗi (bằng 28):

Sự khác biệt (15) đi đến đối số thứ hai về đúng hướng dẫn nó kéo 15 ký tự cuối cùng từ chuỗi trong đối số đầu tiên:

=RIGHT(A2, LEN(A2) - SEARCH(",", A2))7

Đầu ra là một chuỗi con "(102) 123-4568", rất gần với kết quả mong muốn, ngoại trừ một không gian hàng đầu. Vì vậy, chúng tôi sử dụng chức năng trang trí để loại bỏ nó.

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

=RIGHT(A2, LEN(A2) - SEARCH(",", A2))8

Trong trường hợp các giá trị của bạn được phân tách bằng một số lượng phân cách thay đổi, bạn có thể muốn xóa mọi thứ sau phiên bản cuối cùng của dấu phân cách đó. Điều này có thể được thực hiện với công thức sau:

=RIGHT(A2, LEN(A2) - SEARCH(",", A2))9

Trái (ô, tìm ("#", thay thế (ô, "char", "#", len (ô) - len (thay thế (ô, "char", "")))) -1)

Giả sử cột A chứa nhiều thông tin khác nhau về nhân viên, nhưng giá trị sau dấu phẩy cuối cùng luôn là số điện thoại. Mục tiêu của bạn là xóa số điện thoại và giữ tất cả các chi tiết khác.

Để đạt được mục tiêu, bạn có thể xóa văn bản sau dấu phẩy cuối cùng trong A2 với công thức này:

Sao chép công thức xuống cột và bạn sẽ nhận được kết quả 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 RemoveText(str As String, delimiter As String, occurrence As Integer, is_after As Boolean) Dim delimiter_num, start_num, delimiter_len As Integer Dim str_result As String delimiter_num = 0 start_num = 1 str_result = "" delimiter_len = Len(delimiter) For i = 1 To occurrence delimiter_num = InStr(start_num, str, delimiter, vbTextCompare) If 0 < delimiter_num Then start_num = delimiter_num + delimiter_len End If Next i If 0 < delimiter_num Then If True = is_after Then str_result = Mid(str, 1, start_num - delimiter_len - 1) Else str_result = Mid(str, start_num) End If End If RemoveText = str_result End Function

Chức năng của chúng tôi được đặt tên là RemoveText và nó có cú pháp sau:

RemoLETEXT (Chuỗi, Delimiter, Accultence, IS_After)

Where:

Chuỗi - là chuỗi văn bản gốc. Có thể được biểu diễn bằng một tham chiếu tế bào. - is the original text string. Can be represented by a cell reference.

DELIMITER - ký tự trước/sau đó để xóa văn bản. - the character before/after which to remove text.

Sự xuất hiện - Ví dụ của dấu phân cách. - the instance of the delimiter.

IS_After - Một giá trị boolean chỉ ra phía nào của dấu phân cách để xóa văn bản. Có thể là một ký tự duy nhất hoặc một chuỗi các ký tự. - a Boolean value that indicates on which side of the delimiter to remove text. Can be a single character or a sequence of characters.

• Đúng - Xóa mọi thứ sau dấu phân cách (bao gồm cả dấu phân cách).
• Sai - Xóa mọi thứ trước dấu phân cách (bao gồm cả dấu phân cách).

Khi mã của chức năng được chèn vào sổ làm việc của bạn, bạn có thể xóa các chuỗi con khỏi các ô bằng các công thức nhỏ gọn và thanh lịch.

Ví dụ: để xóa mọi thứ sau dấu phẩy đầu tiên trong A2, công thức trong B2 là:

=TRIM(RIGHT(A2, LEN(A2) - SEARCH(",", A2)))3

Để xóa mọi thứ trước dấu phẩy đầu tiên trong A2, công thức trong C2 là:

=TRIM(RIGHT(A2, LEN(A2) - SEARCH(",", A2)))4

Vì chức năng tùy chỉnh của chúng tôi chấp nhận một chuỗi cho dấu phân cách, chúng tôi đặt một dấu phẩy và một không gian (",") trong đối số thứ 2 để giải thích những rắc rối trong việc cắt tỉa các không gian hàng đầu sau đó.string for the delimiter, we put a comma and a space (", ") in the 2nd argument to spare the trouble of trimming leading spaces afterwards.

Chức năng tùy chỉnh của chúng tôi hoạt động đẹp, phải không? Nhưng nếu bạn nghĩ đó là giải pháp toàn diện, bạn vẫn chưa thấy ví dụ tiếp theo :)

Xóa mọi thứ trước, sau hoặc giữa các ký tự

Để có được nhiều tùy chọn hơn để loại bỏ các ký tự hoặc văn bản riêng lẻ khỏi nhiều ô, theo khớp hoặc vị trí, hãy thêm bộ cuối cùng của chúng tôi vào hộp công cụ Excel của bạn.

Tại đây, chúng tôi sẽ xem xét kỹ hơn về tính năng Remove By By vị trí nằm trên tab dữ liệu ABLEBITS> Nhóm văn bản> 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ỏ dữ liệu một phần khỏi một ô trong Excel?

Làm cách nào để cắt văn bản trong một ô trong Excel?

Làm cách nào để trích xuất một văn bản một phần từ một ô?