Đôi khi, dữ liệu trong bảng tính của bạn có thể không được định dạng như bạn cần. Việc xóa các ký tự thừa có thể rất tẻ nhạt và thực hiện thủ công sẽ tốn thời gian và dễ bị lỗi. May mắn thay, có nhiều cách dễ dàng để thao tác dữ liệu bằng các hàm và công thức Excel. Trong bài viết này, bạn sẽ học cách xóa ký tự cuối cùng trong Excel bằng cách sử dụng
- Các chức năng REPLACE và LEN
- Hàm LEFT và LEN
- Chức năng MID và LEN
- Tính năng FLASH FILL
- mã VBA
Trước khi chúng ta bắt đầu, tôi khuyến khích bạn tải xuống tệp bài tập. Nó cung cấp tất cả các công thức tôi đã sử dụng trong hướng dẫn này và có thể hữu ích trong việc hiểu cách thức hoạt động của từng công thức
Xóa ký tự cuối cùng trong Excel bằng hàm REPLACE và LEN
Hàm REPLACE thay thế một phần của chuỗi trong ô bằng một chuỗi mới mà bạn cung cấp. Để loại bỏ ký tự cuối cùng trong chuỗi văn bản, chúng ta có thể thay đổi nó thành một giá trị rỗng. Cú pháp của công thức này là
=REPLACE(old_text, start_num, num_chars, new_text)ở đâu
- old_text là văn bản hoặc giá trị số cần thay đổi
- start_num là vị trí bắt đầu của ký tự trong old_text mà chúng ta cần thay thế bằng new_text
- num_chars là số ký tự cần xóa
- new_text là văn bản sẽ thay thế các ký tự trong old_text
Vì mỗi tên có độ dài thay đổi nên thay vì chỉ định start_num, chúng ta sẽ sử dụng hàm LEN làm đối số cho hàm REPLACE. Hàm Excel LEN trả về tổng độ dài của một văn bản đã cho dưới dạng số ký tự. Sau đó, chúng ta có thể sử dụng công thức LEN làm đối số start_num, đối số này sẽ trả về tổng độ dài của chuỗi văn bản, bao gồm mọi khoảng trắng và dấu chấm câu. Bằng cách kết hợp hai chức năng, chúng tôi nhận được công thức dưới đây
=REPLACE(old_text, LEN(text), num_chars, new_text)Trong ví dụ bên dưới, chúng tôi muốn xóa tên lót ban đầu khỏi mỗi ô khỏi cột B. Để đạt được điều này, chúng tôi sẽ sử dụng các hàm REPLACE và LEN để thay thế các chữ cái đầu tên đệm bằng một chuỗi rỗng
Để viết công thức, hãy làm theo các bước sau
1. Nhấp vào ô mà bạn muốn xem giá trị mong muốn của mình
2. Nhập công thức này vào thanh công thức Excel
=REPLACE(B3, LEN(B3), 1, "")ở đâu
- B3 là giá trị ô từ cột B mà chúng tôi muốn thay đổi
- LEN(B3) là số ký tự của chuỗi văn bản từ ô B3
- 1 là đối số num_chars cho Excel biết có bao nhiêu ký tự cần xóa khỏi chuỗi văn bản
- ““ là chuỗi rỗng sẽ thay thế ký tự cuối cùng từ văn bản trong ô B3
3. Nhấn ENTER và bạn sẽ nhận được chuỗi không có ký tự cuối cùng. Chris McCully
4. Để áp dụng công thức cho các ô bên dưới, hãy kéo xuống Fill Handle
Ghi chú. Công thức này có thể được sử dụng để loại bỏ ký tự đầu tiên hoặc cuối cùng trong Excel. Điều chỉnh nó để nhìn vào ký tự đầu tiên thay vì ký tự cuối cùng và bạn sẽ sẵn sàng sử dụng. Bạn có thể thực hiện việc này bằng cách sử dụng 1 thay vì LEN(B3) và hàm REPLACE sẽ thay đổi ký tự đầu tiên
Xóa hai ký tự cuối trong Excel bằng hàm LEFT và LEN
Nếu bạn cần xóa nhiều ký tự trong Excel, bạn có thể sử dụng hàm LEFT để thay thế. Sử dụng hàm LEFT, chúng ta sẽ xác định chuỗi văn bản cần sửa, đếm số ký tự của chuỗi văn bản sẽ thay đổi và trừ đi số ký tự cần loại bỏ ở phía bên phải. công thức là
=LEFT(text, LEN(text)-num_of_char)ở đâu
- văn bản là chuỗi văn bản để sửa đổi
- LEN(text) đếm xem có bao nhiêu ký tự trong chuỗi
- num_of_char là số ký tự cần trừ từ vế phải
Ví dụ: nếu chúng ta muốn xóa tên đệm viết tắt bằng dấu chấm trên mỗi tên trong cột B, chúng ta cần xóa hai ký tự cuối cùng. Công thức sau đây sẽ giúp chúng ta đạt được điều này
1. Bấm vào ô mà bạn muốn xuất kết quả của công thức
2. Nhập công thức sau
=LEFT(B3, LEN(B3)-2)ở đâu
- B3 là ô chứa chuỗi
- LEN(B3) đếm độ dài của chuỗi văn bản trong ô B3
- 2 đại diện cho số ký tự cần xóa từ bên phải
Ghi chú. Nếu bạn cần xóa thêm ký tự từ bên phải, tất cả những gì bạn cần làm là thay thế số từ công thức trên
3. Nhấn ENTER để kết thúc công thức và bạn sẽ nhận được tên bỏ chữ đệm viết tắt tên
Ghi chú. Hãy nhớ rằng hàm LEN sẽ đếm các ký tự bất kể chúng là dấu cách hay dấu chấm câu, vì vậy ký tự khoảng trắng giữa McCully và P. sẽ không bị xóa
4. Kéo Fill Handle để áp dụng công thức cho các ô khác nếu cần
Mẹo. Bạn có thể sử dụng hàm RIGHT nếu cần xóa chữ cái hoặc ký tự đầu tiên trong chuỗi văn bản. Nhập RIGHT thay vì LEFT trong công thức trên và nó sẽ xóa các ký tự ở phía bên trái của chuỗi văn bản
Xóa ký tự cuối cùng trong Excel bằng hàm MID và LEN
Ngoài hàm LEFT, hàm MID là một cách khác để xóa ký tự trong Excel. Nó hoạt động giống như hàm LEFT và công thức gần như giống nhau, ngoại trừ việc nó có một đối số khác là start_num. Sử dụng hàm MID cũng cho phép bạn xóa các ký tự ở giữa chuỗi, thay vì chỉ ở đầu hoặc cuối. Phương pháp này rất hữu ích khi thao tác với chuỗi văn bản trong Excel. Công thức chung trông như thế này
=MID(text, start_num, LEN(text)-num_of_char)ở đâu
- văn bản là chuỗi văn bản để sửa đổi
- start_num là vị trí bắt đầu của ký tự trong chuỗi văn bản cần sửa đổi
- LEN(text) đếm độ dài của chuỗi văn bản cần sửa đổi
- num_of_char là số ký tự cần trừ từ bên phải
Trong ví dụ trước của chúng tôi với hàm LEFT, khoảng cách giữa tên và tên đệm ban đầu được giữ lại vì chúng tôi chỉ xóa hai ký tự từ bên phải. Nếu chúng ta cũng muốn xóa khoảng trắng, chúng ta cần xóa ba ký tự từ bên phải
1. Bấm vào một ô mà bạn muốn chèn công thức của mình
2. Trong thanh công thức Excel, viết
=MID(B3, 1, LEN(B3)-3)ở đâu
- B3 là ô chứa chuỗi
- 1 là start_num
- LEN(B3) là số ký tự trong chuỗi văn bản ở ô B3
- 3 là số cho Excel biết có bao nhiêu ký tự trong chuỗi cần xóa
3. Nhấn ENTER để kết thúc công thức. Kết quả hiển thị trong ô sẽ là Chris McCully
Xóa ký tự cuối cùng trong Excel bằng tính năng FLASH FILL
Flash Fill là một tính năng trong Microsoft Excel chỉ khả dụng trong Excel 2013 và các phiên bản mới hơn. Nếu Microsoft Excel phát hiện một mẫu khi nhập dữ liệu, công cụ này sẽ tự động hiển thị dữ liệu trong các ô bên dưới dựa trên mẫu bạn đã nhập. Nếu bạn bắt đầu nhập họ mà không có tên, Excel sẽ xác định mẫu và đề xuất dữ liệu có cùng cấu trúc
Đây là một hướng dẫn tuyệt vời trên YouTube về cách sử dụng Flash Fill để thao tác tên
Nếu phần xem trước dữ liệu không tự động hiển thị, bạn có thể làm theo hướng dẫn bên dưới
Ví dụ: nếu chúng tôi chỉ muốn trích xuất họ trong tất cả các ô từ cột B, chúng tôi có thể sử dụng Flash Fill bằng cách nhấp vào nút Xử lý điền
1. Nhập giá trị bạn muốn trích xuất vào ô liền kề với ô lưu trữ giá trị ban đầu. Trong ví dụ của chúng tôi, hãy chèn họ vào ô C3
2. Điều hướng đến ô thứ hai và bắt đầu nhập họ. Menu gợi ý Flash Fill sẽ xuất hiện
3. Nhấn ENTER để chấp nhận đề xuất. Excel sẽ điền vào tất cả các ô trống với dữ liệu tương ứng với mẫu đã xác định
Ngoài ra, sau bước 1, bạn có thể nhấn nút Flash Fill từ tab Data. Kết quả là như nhau
Mặc dù Flash Fill giúp công việc dễ dàng hơn và tiết kiệm nhiều thời gian, nhưng vẫn có một số hạn chế bạn cần lưu ý khi sử dụng tính năng này
- Không giống như các ô có công thức, kết quả sẽ không tự động cập nhật nếu dữ liệu ban đầu bị thay đổi
- Nếu nó không xác định được mẫu, nó sẽ dẫn đến lỗi
- Đôi khi, nó phát hiện sai mẫu và tạo ra kết quả sai
- Dữ liệu phải được sắp xếp theo chiều dọc vì Flash Fill không hoạt động theo chiều ngang
Mẹo. Bạn cũng có thể sử dụng phương pháp này để xóa ký tự đầu tiên khỏi chuỗi
Cách bỏ ký tự từ phải trong Excel bằng code VBA
Nếu bạn biết cách sử dụng VBA, thì bạn cũng có thể viết mã của riêng mình để xóa các ký tự từ ngay trong Excel. Ngoài ra, nếu bạn muốn sử dụng VBA nhưng không biết cách viết mã từ đầu, bạn có thể làm theo các bước sau và chèn mã bên dưới vào mô-đun VBA
1. Nhấn ALT + F11 để mở trình chỉnh sửa Microsoft Visual Basic
2. Nhấp vào chèn mô-đun hoặc nhấp đúp vào trang tính chứa văn bản của bạn
3. Sao chép và dán đoạn mã sau vào trình chỉnh sửa tập lệnh
Sub Remove_Last_Characters() On Error GoTo ErrorMessage Dim n As Integer n = Int(InputBox("How many last characters to remove? ")) For i = 1 To Selection.Rows.Count For j = 1 To Selection.Columns.Count Selection.Cells(i, j) = Left(Selection.Cells(i, j), Len(Selection.Cells(i, j)) - n) Next j Next i Exit Sub ErrorMessage: MsgBox "Please insert an integer number that is less than or equal to the length of the string." End Sub4. Lưu mã VBA và đóng trình soạn thảo Microsoft Visual Basic.
5. Chọn dữ liệu của bạn và nhấn ALT + F8. Cửa sổ macro sẽ xuất hiện
6. Chọn Macro Remove_Last_Ký tự và nhấp vào Chạy
7. Nhập số lượng ký tự bạn muốn xóa và nhấp vào OK. Mã VBA sẽ xóa N ký tự từ bên phải khỏi tất cả các ô đã chọn. Hãy nhớ rằng giá trị số bạn sử dụng phải là số nguyên
Một phương pháp tùy chọn dành cho người dùng Excel nâng cao hơn là tạo một hàm do người dùng xác định bằng VBA và truy cập hàm đó từ thanh công thức. Để lại bình luận bên dưới nếu bạn muốn xem phương pháp này được giải thích trong bài viết
Phải làm gì tiếp theo?
Ở đó bạn có nó. Đây là năm cách dễ dàng để loại bỏ các ký tự cuối cùng trong Excel. Nếu bạn có bất kỳ câu hỏi nào, hãy viết bình luận và tôi sẽ giúp bạn sớm nhất có thể
Nếu bạn đang muốn cải thiện kỹ năng Excel của mình, thì tôi thực sự khuyên bạn nên đọc các bài viết sau