Khi làm việc với dữ liệu văn bản trong Excel, bạn có thể có nhu cầu xóa văn bản trước hoặc sau một ký tự hoặc chuỗi văn bản cụ thể.
Ví dụ: nếu bạn có tên và dữ liệu chỉ định của mọi người, bạn có thể muốn xóa chỉ định sau dấu phẩy và chỉ giữ tên [hoặc ngược lại nơi bạn giữ chỉ định và xóa tên].
Đôi khi, nó có thể được thực hiện với một công thức đơn giản hoặc tìm và thay thế nhanh, và đôi khi, nó cần các công thức hoặc cách giải quyết phức tạp hơn.
Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách xóa văn bản trước hoặc sau một ký tự cụ thể trong Excel [sử dụng các ví dụ khác nhau].
Vì vậy, hãy để bắt đầu với một số ví dụ đơn giản.
- Xóa văn bản sau khi một ký tự sử dụng tìm và thay thế
- Xóa văn bản bằng các công thức
- Xóa văn bản bằng Flash Fill
- Xóa văn bản bằng VBA [chức năng tùy chỉnh]
Xóa văn bản sau khi một ký tự sử dụng tìm và thay thế
Xóa văn bản bằng các công thức
Xóa văn bản bằng Flash Fill
Xóa văn bản bằng VBA [chức năng tùy chỉnh]
- Nếu bạn muốn nhanh chóng xóa tất cả văn bản sau một chuỗi văn bản cụ thể [hoặc trước chuỗi văn bản], bạn có thể làm điều đó bằng cách sử dụng các ký tự tìm và thay thế và hoang dã.
- Giả sử bạn có một bộ dữ liệu như được hiển thị bên dưới và bạn muốn xóa chỉ định sau ký tự dấu phẩy và giữ văn bản trước dấu phẩy.
- Dưới đây là các bước để làm điều này:
- Sao chép và dán dữ liệu từ cột A đến cột & NBSP; B [điều này cũng là để giữ dữ liệu gốc]
- Với các ô trong cột B được chọn, nhấp vào tab Home,* [i.e., comma followed by an asterisk
sign]
- Trong nhóm chỉnh sửa, nhấp vào tùy chọn Tìm & Chọn
- Trong các tùy chọn xuất hiện trong thả xuống, nhấp vào tùy chọn Thay thế. Điều này sẽ mở hộp thoại Tìm và thay thế
Trong ‘Tìm trường nào, nhập,* [tức là dấu phẩy theo sau là dấu hoa thị]
Để lại ‘Thay thế trường bằng trường trống
Nhấp vào nút Thay thế tất cả
Các bước trên sẽ tìm thấy dấu phẩy trong tập dữ liệu và xóa tất cả văn bản sau dấu phẩy [bao gồm cả dấu phẩy].
Vì điều này thay thế văn bản từ các ô đã chọn, nên bạn nên sao chép văn bản vào một cột khác và sau đó thực hiện hoạt động tìm và thay thế này hoặc tạo bản sao lưu dữ liệu của bạn để bạn còn nguyên dữ liệu gốc
Cái này hoạt động ra sao?
* [dấu hoa thị] là một ký tự ký tự đại diện có thể đại diện cho bất kỳ số lượng ký tự nào.
Khi tôi sử dụng nó sau dấu phẩy [trong ‘Tìm trường nào] và sau đó nhấp vào thay thế tất cả nút, nó tìm thấy dấu phẩy đầu tiên trong ô và coi đó là một trận đấu.: This method works well then you only have one comma in each cell [as in our example]. In case you have multiple commas, this method would always find the first comma and then remove everything after it. So you cannot use this method if you want to replace all the text after the second comma and keep the first one as is.
Điều này là do dấu Asterisk [*] được coi là phù hợp với toàn bộ chuỗi văn bản theo dấu phẩy.
Xóa văn bản bằng các công thức
Xóa văn bản bằng Flash Fill
Xóa văn bản bằng VBA [chức năng tùy chỉnh]
Nếu bạn muốn nhanh chóng xóa tất cả văn bản sau một chuỗi văn bản cụ thể [hoặc trước chuỗi văn bản], bạn có thể làm điều đó bằng cách sử dụng các ký tự tìm và thay thế và hoang dã.
=LEFT[A2,FIND[",",A2]-1]
Giả sử bạn có một bộ dữ liệu như được hiển thị bên dưới và bạn muốn xóa chỉ định sau ký tự dấu phẩy và giữ văn bản trước dấu phẩy.
Dưới đây là các bước để làm điều này:
Sao chép và dán dữ liệu từ cột A đến cột & NBSP; B [điều này cũng là để giữ dữ liệu gốc]
Với các ô trong cột B được chọn, nhấp vào tab Home
Trong nhóm chỉnh sửa, nhấp vào tùy chọn Tìm & Chọn
Trong các tùy chọn xuất hiện trong thả xuống, nhấp vào tùy chọn Thay thế. Điều này sẽ mở hộp thoại Tìm và thay thế
=LEFT[A2,FIND["!",SUBSTITUTE[A2,",","!",2]]-1]
Trong ‘Tìm trường nào, nhập,* [tức là dấu phẩy theo sau là dấu hoa thị]
Để lại ‘Thay thế trường bằng trường trống
Nhấp vào nút Thay thế tất cả
Các bước trên sẽ tìm thấy dấu phẩy trong tập dữ liệu và xóa tất cả văn bản sau dấu phẩy [bao gồm cả dấu phẩy].
Vì điều này thay thế văn bản từ các ô đã chọn, nên bạn nên sao chép văn bản vào một cột khác và sau đó thực hiện hoạt động tìm và thay thế này hoặc tạo bản sao lưu dữ liệu của bạn để bạn còn nguyên dữ liệu gốc
Cái này hoạt động ra sao?
Ví dụ, trong bộ dữ liệu dưới đây, một số ô có hai dấu phẩy và một số ô có ba dấu phẩy và tôi cần trích xuất tất cả văn bản trước dấu phẩy cuối cùng.
Trong trường hợp này, tôi cần bằng cách nào đó tìm ra vị trí của sự xuất hiện cuối cùng của dấu phẩy và sau đó trích xuất mọi thứ ở bên trái của nó.
Dưới đây là công thức sẽ làm điều đó
=LEFT[A2,FIND["!",SUBSTITUTE[A2,",","!",LEN[A2]-LEN[SUBSTITUTE[A2,",",""]]]]-1]
Công thức trên sử dụng hàm LEN để tìm độ dài tổng thể của văn bản trong ô cũng như độ dài của văn bản mà không có dấu phẩy.
Khi tôi trừ hai giá trị này, nó cung cấp cho tôi tổng số dấu phẩy trong ô.
Vì vậy, nó sẽ cho tôi 3 cho Cell A2 và 2 cho Cell A4.
Giá trị này sau đó được sử dụng trong công thức thay thế để thay thế dấu phẩy cuối cùng bằng dấu chấm than. Và sau đó bạn có thể sử dụng chiết xuất chức năng bên trái mọi thứ ở bên trái của dấu chấm than [đó là dấu phẩy cuối cùng được sử dụng]
Như bạn có thể thấy trong các ví dụ, sử dụng kết hợp các công thức văn bản cho phép bạn xử lý nhiều tình huống khác nhau.
Ngoài ra, vì kết quả được liên kết với dữ liệu gốc, khi bạn thay đổi dữ liệu gốc, kết quả sẽ tự động cập nhật.
Xóa văn bản bằng Flash Fill
Flash Fill là một công cụ được giới thiệu trong Excel 2013 và có sẵn trong tất cả các phiên bản sau đó.
Nó hoạt động bằng cách xác định các mẫu khi bạn nhập thủ công dữ liệu và sau đó ngoại suy để cung cấp cho bạn dữ liệu cho toàn bộ cột.
Vì vậy, nếu bạn muốn xóa văn bản trước hoặc sau một nhân vật cụ thể, bạn chỉ cần hiển thị flash Fairy kết quả sẽ như thế nào [bằng cách nhập thủ công một vài lần] và flash fill sẽ tự động hiểu mẫu và cung cấp cho bạn kết quả.
Hãy để tôi chỉ cho bạn điều này với một ví dụ.
Dưới đây tôi có bộ dữ liệu nơi tôi muốn xóa tất cả văn bản sau dấu phẩy.
Dưới đây là các bước để thực hiện việc này bằng Flash Fill:
- Trong ô B2, là cột liền kề của dữ liệu của chúng tôi, nhập thủ công ‘Jeffery Haggins, [đó là kết quả dự kiến]
- Trong ô B3, nhập ‘Tim Scott, [đó là kết quả dự kiến cho ô thứ hai]
- Chọn phạm vi B2: B10
- Nhấp vào tab Trang chủ
- Trong nhóm chỉnh sửa, nhấp vào tùy chọn thả xuống điền vào
- Nhấp vào FLASH FILL
Các bước trên sẽ cung cấp cho bạn kết quả như hình dưới đây:
Bạn cũng có thể sử dụng điều khiển phím tắt bàn phím flash FILL + E sau khi chọn các ô trong cột kết quả [cột B trong ví dụ của chúng tôi]Control + E after selecting the cells in the result column [Column B in our example]
Flash Fill là một công cụ tuyệt vời và trong hầu hết các trường hợp, nó có thể nhận ra mẫu và cho bạn kết quả đúng. Nhưng trong một số trường hợp, nó có thể không nhận ra mẫu một cách chính xác và có thể cung cấp cho bạn kết quả sai.
Vì vậy, hãy chắc chắn rằng bạn đã kiểm tra kỹ kết quả điền flash của bạn
Và giống như chúng tôi đã xóa tất cả các văn bản sau một ký tự cụ thể bằng cách sử dụng flash điền, bạn có thể sử dụng cùng các bước để xóa văn bản trước một ký tự cụ thể. Chỉ cần hiển thị flash điền cách kết quả sẽ trông giống như internet của tôi theo cách thủ công trong cột liền kề và nó sẽ làm phần còn lại.
Xóa văn bản bằng VBA [chức năng tùy chỉnh]
Toàn bộ khái niệm loại bỏ văn bản trước hoặc sau một nhân vật cụ thể phụ thuộc vào việc tìm vị trí của nhân vật đó.
Như thể thấy ở trên, việc tìm thấy sự xuất hiện cuối cùng của nhân vật đó có nghĩa là sử dụng một pha chế của các công thức.
Nếu đây là điều bạn cần làm khá thường xuyên, bạn có thể đơn giản hóa quy trình này bằng cách tạo chức năng tùy chỉnh bằng VBA [được gọi là các hàm do người dùng xác định].
Sau khi được tạo, bạn có thể sử dụng lại chức năng này nhiều lần. Nó cũng đơn giản hơn và dễ sử dụng hơn rất nhiều [vì hầu hết các công việc nặng được thực hiện bởi mã VBA ở phía sau].
Bên dưới mã VBA mà bạn có thể sử dụng để tạo chức năng tùy chỉnh trong Excel:
Function LastPosition[rCell As Range, rChar As String] 'This function gives the last position of the specified character 'This code has been developed by Sumit Bansal [//trumpexcel.com] Dim rLen As Integer rLen = Len[rCell] For i = rLen To 1 Step -1 If Mid[rCell, i - 1, 1] = rChar Then LastPosition = i - 1 Exit Function End If Next i End Function
Bạn cần đặt mã VBA trong một mô -đun thông thường trong trình soạn thảo VB hoặc trong sổ làm việc vĩ mô cá nhân. Khi bạn có nó ở đó, bạn có thể sử dụng nó như bất kỳ chức năng bảng tính thông thường nào khác trong sổ làm việc.
Hàm này có 2 đối số:
- Tham chiếu ô mà bạn muốn tìm thấy sự xuất hiện cuối cùng của một ký tự hoặc chuỗi văn bản cụ thể
- Nhân vật hoặc chuỗi văn bản có vị trí bạn cần tìm
Giả sử bây giờ bạn có bộ dữ liệu dưới đây và bạn muốn xóa tất cả văn bản sau dấu phẩy cuối cùng và chỉ có văn bản trước dấu phẩy cuối cùng.
Dưới đây là công thức sẽ làm điều đó:
=LEFT[A2,LastPosition[A2,","]-1]
Trong công thức trên, tôi đã chỉ định để tìm vị trí của dấu phẩy cuối cùng. Trong trường hợp bạn muốn tìm vị trí của một số ký tự hoặc chuỗi văn bản khác, bạn nên sử dụng nó làm đối số thứ hai trong hàm.
Như bạn có thể thấy đây là ngắn hơn và dễ sử dụng hơn, so với công thức văn bản dài mà chúng tôi đã sử dụng phần trước
Nếu bạn đặt mã VBA vào một mô -đun trong sổ làm việc, bạn sẽ chỉ có thể sử dụng chức năng tùy chỉnh này trong sổ làm việc cụ thể đó. Nếu bạn muốn sử dụng điều này trong tất cả các sổ làm việc trên hệ thống của mình, bạn cần sao chép và dán mã này vào sổ làm việc vĩ mô cá nhân.
Vì vậy, đây là một số thư bạn có thể sử dụng để nhanh chóng xóa văn bản trước hoặc sau một ký tự cụ thể trong Excel.
Nếu nó là một điều đơn giản một lần, thì bạn có thể làm điều đó bằng cách sử dụng và thay thế. Điều gì sẽ xảy ra nếu nó phức tạp hơn một chút, thì bạn cần sử dụng kết hợp các công thức Excel sẵn có hoặc thậm chí tạo công thức tùy chỉnh của riêng bạn bằng VBA.
Tôi hy vọng bạn tìm thấy hướng dẫn này hữu ích.
Các hướng dẫn Excel khác mà bạn có thể thích:
- Đếm các ký tự trong một ô [hoặc phạm vi của ô] bằng cách sử dụng các công thức trong Excel
- Cách xóa ký tự đầu tiên khỏi chuỗi trong Excel
- Cách xóa dấu phẩy trong Excel [từ văn bản và số]
- Cách trích xuất từ đầu tiên từ chuỗi văn bản trong Excel
- Riêng biệt và họ trong excel [tên phân chia bằng công thức]
- Xóa các ký tự từ bên trái trong Excel [Công thức dễ dàng]