Excel là công cụ quản lý dữ liệu và trực quan hóa dữ liệu mạnh mẽ. Nó được sử dụng để lưu trữ, phân tích và tạo báo cáo về lượng lớn dữ liệu. Nó thường được các chuyên gia kế toán sử dụng để phân tích dữ liệu tài chính nhưng bất kỳ ai cũng có thể sử dụng để quản lý dữ liệu lớn. Dữ liệu được nhập vào các hàng và cột. Và sau đó, các công thức và hàm có thể được sử dụng để biến thông tin chi tiết về dữ liệu. Hãy xem cách xóa văn bản trước và sau một ký tự hoặc chuỗi văn bản cụ thể trong Excel.
Các bước để xóa văn bản trước hoặc sau một ký tự cụ thể trong Excel
Ví dụ: nếu bạn có tên và tuổi của mọi người trong trường dữ liệu được phân tách bằng dấu phẩy [,], bạn có thể muốn xóa tuổi sau dấu phẩy và chỉ giữ lại tên
Sau khi xóa văn bản hiện tại sau dấu phẩy [,],
Chúng tôi có thể thực hiện tác vụ này bằng cách sử dụng tìm và thay thế trong Excel
Sử dụng tìm và thay thế
Đối với phương pháp này, chúng tôi sẽ sử dụng ký tự đại diện dấu hoa thị [*] [Nó đại diện cho bất kỳ số lượng ký tự nào]. Giả sử chúng tôi có dữ liệu sau trong bảng excel và chúng tôi muốn xóa văn bản trước dấu phẩy [,]
Bước 1. Chọn dữ liệu và chọn tab Trang chủ
Bước 2. Nhấp vào tùy chọn tìm và chọn
Bước 3. Từ danh sách thả xuống, chọn tùy chọn 'Thay thế'. Hộp thoại tìm và thay thế sẽ xuất hiện trên màn hình
Bước 4. Để tìm trường nào, hãy nhập '*,' [Đại diện cho bất kỳ ký tự nào trước] và để trống trường thay thế
Thường thì chúng ta cần bỏ chữ trước khoảng trắng bằng công thức excel. Mặc dù có một số cách để xóa văn bản trước khoảng trắng, nhưng sử dụng công thức sẽ thuận tiện hơn. May mắn thay, chúng ta có thể viết những công thức này bằng cách sử dụng các hàm thông thường của excel hoặc chúng ta có thể sử dụng Hàm do người dùng xác định trong VBA. Bài viết này sẽ hướng dẫn các bạn áp dụng công thức khi bỏ chữ trước dấu cách
Mục lục
Tải sách bài tập thực hành
Bạn có thể tải xuống sách bài tập thực hành mà chúng tôi đã sử dụng để chuẩn bị bài viết này
Công thức xóa văn bản trước dấu cách. xlsm
5 cách sử dụng công thức Excel để xóa văn bản trước khoảng trắng
1. Xóa văn bản trước khoảng trắng bằng công thức Excel với các hàm RIGHT, LEN và SEARCH
Hãy xem xét một tập dữ liệu chứa tên đầy đủ của một số người. Ở đây họ và tên cách nhau bởi khoảng trắng. Bây giờ tôi sẽ bỏ phần chữ trước khoảng trống này và chỉ giữ lại họ. Trong khi thực hiện quy trình xóa này, tôi sẽ sử dụng kết hợp các hàm RIGHT, LEN và SEARCH. Thực hiện theo các bước dưới đây để có được kết quả cuối cùng
bước
- Đầu tiên, nhập công thức dưới đây vào Ô D5 và nhấn Enter
=RIGHT[B5,LEN[B5]-SEARCH[" ",B5]]
- Kết quả là, chúng ta sẽ nhận được kết quả dưới đây. Văn bản 'Andrew' - trước khi khoảng trắng bị xóa như chúng tôi muốn. Bây giờ, hãy sử dụng công cụ Fill Handle [+] để sao chép công thức trên phạm vi C6. C10
- Cuối cùng, văn bản trước khi tất cả họ và khoảng trắng bị xóa như trong ảnh chụp màn hình bên dưới
🔎 Công thức hoạt động như thế nào?
- TÌM KIẾM[” “,B5]
Ở đây, hàm TÌM KIẾM trả về vị trí của khoảng trắng [“ ”] trong chuỗi của Ô B5, đó là
{7}
- LEN[B5]
Sau đó, hàm LEN trả về tổng số ký tự có trong Ô B5
{11}
- LEN[B5]-TÌM KIẾM[” “,B5]
Sau đó, phần này của công thức trừ vị trí khoảng trắng khỏi tổng chiều dài của Ô B5 và cho kết quả
{4}
- PHẢI[B5,LEN[B5]-TÌM KIẾM[” “,B5]]
Cuối cùng, hàm RIGHT trích xuất 4 ký tự từ phía bên phải của Ô B5 và trả về
{dương}
Đọc thêm. Cách xóa văn bản khỏi ô Excel [9 cách dễ dàng]
2. Kết hợp các hàm RIGHT, LEN và FIND để xóa văn bản trước khoảng trắng đầu tiên trong Excel
Bạn có thể sử dụng văn bản trước khoảng trắng bằng cách kết hợp các hàm RIGHT, LEN và FIND. Sử dụng kết hợp này, bạn có thể xóa văn bản trước khoảng trắng thứ nhất nếu có nhiều hơn một khoảng trắng trong một chuỗi. Giả sử, tôi có một tập dữ liệu chứa dữ liệu tiểu sử của một số người [Tên, Nghề nghiệp, Tuổi] được phân tách bằng dấu cách. Bây giờ, tôi sẽ xóa văn bản trước khoảng trắng đầu tiên khỏi mỗi dữ liệu này
bước
- Nhập công thức dưới đây vào Ô D5 và nhấn Enter từ bàn phím
=RIGHT[B5,LEN[B5]-FIND[" ",B5]]
- Do đó, văn bản trước khoảng trắng đầu tiên bị xóa và khi áp dụng công cụ Fill Handle, đây là kết quả cuối cùng
- Công thức trên hoạt động theo cách tương tự được mô tả trong Phương thức 1, ngoại trừ, ở đây, hàm FIND trả về vị trí của khoảng trắng đầu tiên từ chuỗi đã nhập
Đọc thêm. Cách xóa văn bản cụ thể khỏi cột trong Excel [8 cách]
3. Xóa văn bản trước dấu cách cuối cùng bằng công thức Excel
Không giống như phương pháp trước, bây giờ tôi sẽ xóa văn bản trước dấu cách cuối cùng trong một chuỗi. Trong khi làm điều đó, tôi sẽ áp dụng một công thức dựa trên các hàm RIGHT, LEN, SEARCH và SUBSTITUTE trong excel
bước
- Nhập công thức sau vào ô D5. Sau đó nhấn Enter
=RIGHT[B5,LEN[B5]-SEARCH["#",SUBSTITUTE[B5," ","#",LEN[B5]-LEN[SUBSTITUTE[B5," ",""]]]]]
- Khi nhập công thức ở đây là đầu ra chúng tôi nhận được. Chúng ta có thể thấy rằng tất cả các văn bản trước dấu cách cuối cùng trong các chuỗi này không còn nữa
🔎 Công thức hoạt động như thế nào?
- SUBSTITUTE[B5,” “,””]
Ở đây, hàm SUBSTITUTE thay thế tất cả khoảng trống của Ô B5 thành không có gì và trả về
{“AndrewTeacher33“}
- LEN[SUBSTITUTE[B5,” “,””]]
Sau đó, hàm LEN trả về độ dài của kết quả của công thức SUBSTITUTE đã đề cập ở trên và trả về
{15}
- LEN[B5]-LEN[SUBSTITUTE[B5,” “,””]]
Phần này trở lại
{2}
- SUBSTITUTE[B5,” “,”#”,LEN[B5]-LEN[SUBSTITUTE[B5,” “,””]]]
Bây giờ, phần này của công thức trả lời
{“Giáo viên Andrew#33”}
- TÌM KIẾM[“#”,SUBSTITUTE[B5,” “,”#”,LEN[B5]-LEN[SUBSTITUTE[B5,” “,””]]]]
Sau đó, công thức trên trả về
{15}
- RIGHT[B5,LEN[B5]-SEARCH[“#”,SUBSTITUTE[B5,” “,”#”,LEN[B5]-LEN[SUBSTITUTE[B5,” “,””]]]]]
Tóm lại, công thức xóa các văn bản trước khoảng trắng thứ 2 và trả lời
{33}
Đọc thêm. Cách xóa tên đã xác định trong Excel [3 cách]
4. Áp dụng công thức để xóa văn bản trước khoảng trắng thứ N trong Excel
Lần này, tôi sẽ xóa văn bản trước khi khoảng trắng xuất hiện lần thứ N trong một chuỗi. Giả sử tôi có tập dữ liệu dưới đây. Từ những dữ liệu này, bây giờ, tôi sẽ xóa tất cả văn bản trước dấu cách thứ 2
bước
- Nhập công thức dưới đây vào Ô D5. Tiếp theo, nhấn Enter
=RIGHT[SUBSTITUTE[B5," ","#",2],LEN[B5]-FIND["#",SUBSTITUTE[B5," ","#",2]]]
- Sau khi nhập công thức ta sẽ được kết quả như bên dưới
🔎 Công thức hoạt động như thế nào?
- SUBSTITUTE[B5,” “,”#”,2]
Ở đây hàm SUBSTITUTE thay thế khoảng trắng thứ 2 của chuỗi Ô B5 bằng dấu '#'
{“Giáo viên Andrew#33 M”}
- TÌM[“#”,SUBSTITUTE[B5,” “,”#”,2]]
Sau đó, hàm FIND trả về vị trí của không gian thứ 2 là
{15}
- RIGHT[SUBSTITUTE[B5,” “,”#”,2],LEN[B5]-FIND[“#”,SUBSTITUTE[B5,” “,”#”,2]]]
Cuối cùng, hàm RIGHT, kết hợp với hàm LEN và hàm SUBSTITUTE xóa văn bản trước dấu cách thứ 2 của chuỗi Ô B5 và trả về
{33 giờ}
Đọc thêm. Cách xóa văn bản cụ thể khỏi ô trong Excel [11 cách dễ nhất]
5. Excel VBA UDF để xóa văn bản trước dấu cách
Trong phương pháp này, tôi sẽ tạo Hàm do người dùng xác định [UDF] bằng excel VBA. Sau này, sử dụng UDF đó, tôi sẽ xóa văn bản khỏi chuỗi
Cú pháp của UDF
Để hiểu rõ hơn về công thức, hãy làm quen với UDF mà tôi sẽ tạo
DeleteText[chuỗi, ký tự, lần xuất hiện, is_before]
Ở đâu
- chuỗi là chuỗi ban đầu
- ký tự biểu thị 'khoảng trắng' ở đây
- lần xuất hiện là vị trí của 'dấu cách' trong chuỗi gốc
- phía trước là giá trị boolean cho biết chúng ta muốn xóa văn bản từ phía nào của khoảng trống
Thực hiện theo các hướng dẫn bên dưới để tạo và áp dụng UDF nêu trên
bước
- Đầu tiên vào Developer > Visual Basic để đưa cửa sổ VBA vào. Bạn cũng có thể mang cửa sổ VBA bằng cách sử dụng Alt + F11
- Khi cửa sổ VBA xuất hiện, nhấp chuột phải vào Dự án VBA và đi tới Chèn > Mô-đun
- Thứ hai, nhập mã dưới đây vào Mô-đun mới được chèn
Function DeleteText[str As String, Character As String, occurrence As Integer, is_before As Boolean]
Dim Space_no, start_num, Space_len As Integer
Dim str_output As String
Space_no = 0
start_num = 1
str_output = ""
Space_len = Len[Character]
For i = 1 To occurrence
Space_no = InStr[start_num, str, Character, vbTextCompare]
If 0 < Space_no Then
start_num = Space_no + Space_len
End If
Next i
If 0 < Space_no Then
If False = is_before Then
str_output = Mid[str, 1, start_num - Space_len - 1]
Else
str_output = Mid[str, start_num]
End If
End If
DeleteText = str_output
End Function- Bây giờ, hãy chuyển đến trang tính excel nơi bạn muốn áp dụng hàm và bắt đầu nhập hàm mới tạo. Hàm sẽ xuất hiện dưới dạng các hàm excel khác [xem ảnh chụp màn hình]
- Sau đó, nhập các đối số của hàm để xóa tất cả khoảng trắng trước dấu cách thứ 2
=DeleteText[B5," ",2,TRUE]
- Sau đó nhấn Enter và áp dụng công cụ Fill Handle để sao chép công thức vào các ô còn lại
- Cuối cùng, ở đây chúng tôi nhận được kết quả mong đợi. Các văn bản trước khoảng trắng thứ 2 từ tất cả các chuỗi bị xóa
Nội dung liên quan. Cách xóa các chữ cái khỏi ô trong Excel [10 phương pháp]
Những điều cần ghi nhớ
- Ngoài việc sử dụng các công thức excel, bạn có thể sử dụng tùy chọn Flash Fill trong excel để xóa văn bản trước dấu cách
Phần kết luận
Trong bài viết trên, tôi đã cố gắng thảo luận một số phương pháp sử dụng công thức excel để loại bỏ văn bản trước khoảng trắng một cách công phu. Hy vọng rằng, những phương pháp và giải thích này sẽ đủ để giải quyết vấn đề của bạn. Vui lòng cho tôi biết nếu bạn có bất kỳ thắc mắc nào