Khi chỉnh sửa hoặc làm sạch dữ liệu trong trang tính của bạn, người ta thường cần cắt và loại bỏ một số phần nhất định của chuỗi, để định dạng nhất quán hoặc để làm cho tập dữ liệu trông gọn gàng và nguyên bản
Ví dụ: bạn có thể cần xóa văn bản sau ký hiệu '@' của địa chỉ email để chỉ lấy tên người dùng
Bạn có thể cần xóa các mục của danh sách sau một ký hiệu cụ thể, chẳng hạn như ký hiệu dấu phẩy thứ ba. Có nhiều trường hợp khác mà bạn có thể cần xóa văn bản sau một ký tự cụ thể trong Excel
Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn 3 cách để làm điều đó
- Sử dụng Tìm và Thay thế
- Sử dụng một công thức
- Sử dụng VBA
Chúng tôi cũng sẽ chỉ cho bạn cách xóa văn bản sau ký tự thứ n của một ký tự cụ thể [như sau dấu gạch nối thứ hai hoặc dấu phẩy thứ ba]
Mục lục
- Xóa văn bản sau một ký tự cụ thể
- Phương pháp 1. Sử dụng Tìm và Thay thế để Xóa Văn bản sau một Ký tự Cụ thể
- Phương pháp 2. Sử dụng Công thức để Xóa Văn bản sau một Ký tự Cụ thể
- Phương pháp 3. Sử dụng VBA để xóa văn bản sau một ký tự cụ thể
- Xóa văn bản sau trường hợp thứ n của một ký tự cụ thể
Xóa văn bản sau một ký tự cụ thể
Giả sử chúng ta có bộ dữ liệu địa chỉ email sau đây và chúng ta muốn xóa mọi thứ sau ký tự '@' trong mỗi ô
How to Remove Question Marks from Text in Excel?
Phương pháp 3. Sử dụng VBA để xóa văn bản sau một ký tự cụ thể
Cuối cùng, nếu bạn cảm thấy thoải mái với VBA và muốn hoàn thành công việc của mình nhanh chóng thông qua macro, thì đây là một phương pháp tuyệt vời dành cho bạn
Đây là mã chúng tôi sẽ sử dụng. Bạn có thể chọn và sao chép nó
Sub remove_text_after_char[] Dim rng As Range Dim cell As Range Set rng = Application.Selection For Each cell In rng cell.Offset[0, 1].Value = Left[cell, InStr[cell, "@"] - 1] Next cell End Sub
Thực hiện theo các bước sau
- Từ Ruy-băng Menu Nhà phát triển, chọn Visual Basic
- Khi cửa sổ VBA của bạn mở ra, hãy nhấp vào Chèn-> Mô-đun. Bây giờ bạn có thể bắt đầu mã hóa. Nhập hoặc sao chép-dán các dòng mã trên vào cửa sổ mô-đun. Mã của bạn hiện đã sẵn sàng để chạy
- Chọn dãy ô chứa văn bản muốn chuyển đổi. Đảm bảo cột bên cạnh nó trống vì đây là nơi macro sẽ hiển thị kết quả
- Điều hướng đến Nhà phát triển->Macro->remove_text_after_char->Run
- Bây giờ, bạn sẽ thấy các chuỗi bị cắt ngắn bên cạnh phạm vi ô đã chọn của mình
Ghi chú. Bạn có thể thay đổi ký hiệu ở dòng 6 từ “@” thành ký hiệu bạn yêu cầu
Hãy nhớ sao lưu trang tính của bạn, vì kết quả của mã VBA thường không thể đảo ngược
Xóa văn bản sau trường hợp thứ n của một ký tự cụ thể
Bây giờ chúng ta hãy xem một trường hợp đặc biệt khi bạn cần xóa tất cả văn bản sau một trường hợp cụ thể của một ký tự
Ví dụ: trong tập dữ liệu được hiển thị bên dưới, giả sử bạn muốn xóa tất cả văn bản sau dấu phẩy thứ hai. [Có thể bạn chỉ muốn giữ hai mục đầu tiên]
Đối với điều này, chúng ta chỉ có thể điều chỉnh công thức mà chúng ta đã sử dụng trong phương pháp 2, bằng cách thêm hàm SUBSTITUTE. Hàm SUBSTITUTE cho phép bạn thay thế một phiên bản nhất định của một ký tự trong văn bản bằng một ký tự khác. Cú pháp của nó là
=SUBSTITUTE [original_string, old_character, new_character,instance_number]
Nơi đây,
- original_string là văn bản hoặc tham chiếu đến ô mà bạn muốn làm việc
- old_character là ký tự mà bạn muốn thay thế
- new_character là ký tự mà bạn muốn thay thế old_character bằng
- instance_number là phiên bản của old_character mà bạn muốn thay thế. Điều đó có nghĩa là nếu bạn muốn thay thế dấu phẩy thứ hai trong original_string, giá trị này sẽ là 2
Vì vậy, đây là các bước cần thực hiện nếu bạn muốn xóa tất cả văn bản sau dấu phẩy thứ hai trong tập dữ liệu của chúng tôi
- Chọn ô đầu tiên của cột mà bạn muốn kết quả xuất hiện. Trong ví dụ của chúng tôi, nó sẽ là ô B2
- Nhập công thức.
=LEFT[A2,FIND["#",SUBSTITUTE[A2,",","#",2]]-1].
- Nhấn phím quay lại
- Điều này sẽ cung cấp cho bạn văn bản thu được sau khi xóa mọi thứ sau ký hiệu dấu phẩy thứ hai khỏi chuỗi trong ô A2
- Bấm đúp vào núm điều khiển điền [nằm ở góc dưới cùng bên trái] của ô B2. Điều này sẽ sao chép công thức vào tất cả các ô khác của cột B. Bạn cũng có thể chọn kéo thanh điều khiển điền xuống để đạt được hiệu ứng tương tự. Đây là cả cột gốc và cột được chuyển đổi cạnh nhau
- Nếu bạn chỉ muốn giữ lại các phiên bản đã chuyển đổi của văn bản, hãy chọn các ô này [B2. B5], sao chép chúng và dán chúng vào cùng một vị trí với các giá trị
- Sau đó, bạn có thể xóa cột A nếu cần
Công thức này hoạt động như thế nào?
Để hiểu cách thức hoạt động của công thức này, chúng ta cần chia nhỏ nó ra
- Đầu tiên, chúng tôi sử dụng hàm SUBSTITUTE để tìm phiên bản thứ hai của ký hiệu dấu phẩy trong văn bản. SUBSTITUTE[A2,””,,”#”,2]. Để phân biệt nó với các ký hiệu dấu phẩy khác trong ô, chúng tôi đã thay thế dấu phẩy này bằng ký hiệu băm [#]. Chúng tôi cũng có thể sử dụng bất kỳ biểu tượng nào khác, điều đó không thực sự quan trọng. Điều quan trọng là chúng tôi đảm bảo rằng đó là một biểu tượng khác với tất cả các biểu tượng khác trong văn bản. Điều này đảm bảo rằng hàm FIND [mà chúng ta sẽ sử dụng tiếp theo] có thể tìm thấy ký tự này
- Tiếp theo chúng ta sử dụng hàm FIND để tìm vị trí của ký tự ‘#’ trong văn bản. TÌM[“#”,SUBSTITUTE[A2,”,”,”#”,2]]
- Chúng tôi muốn tất cả các ký tự trước [và không bao gồm] biểu tượng “#”. Vì vậy, chúng tôi trừ 1 từ chỉ số này. TÌM[“#”,SUBSTITUTE[A2,”,”,”#”,2]]-1. Điều này đưa ra chỉ mục của ký tự cuối cùng trước ký hiệu “#” [ban đầu là dấu phẩy thứ hai]
- Cuối cùng, chúng tôi đã sử dụng hàm LEFT để chỉ trích xuất các ký tự bắt đầu từ bên trái cho đến chỉ mục đó. TRÁI[A2,FIND[“#”,SUBSTITUTE[A2,”,”,”#”,2]]-1]
Như vậy ta lấy được tất cả các ký tự trong A2 từ ký tự ngoài cùng bên trái đến ký tự trước ký hiệu dấu phẩy thứ hai
Nếu bạn muốn trích xuất một chuỗi con trước bất kỳ sự xuất hiện nào khác của ký hiệu dấu phẩy, bạn có thể sử dụng cùng một công thức, nhưng chỉ cần thay thế số “2” bằng ví dụ về dấu phẩy mà bạn muốn dừng tại đó
Vì vậy, nếu bạn muốn xóa mọi thứ sau dấu phẩy thứ ba, công thức của bạn sẽ thay đổi thành
=LEFT[A2,FIND["#",SUBSTITUTE[A2,",","#",3]]-1]
Nếu bạn muốn trích xuất một chuỗi con trước khi xuất hiện lần thứ n của bất kỳ ký tự nào khác, bạn chỉ cần thay ký hiệu dấu phẩy thành ký tự cần thiết
Trong hướng dẫn này, chúng tôi đã chỉ cho bạn ba cách để xóa văn bản sau một ký tự cụ thể. Chúng tôi cũng đã chỉ cho bạn cách xóa văn bản sau bất kỳ trường hợp nào của một ký tự trong một văn bản nhất định
Chúng tôi đã cố gắng trình bày các phương pháp theo các bước đơn giản và dễ dàng. Chúng tôi hy vọng bạn thấy hướng dẫn của chúng tôi dễ hiểu và dễ áp dụng