Đôi khi bạn muốn xóa một số chữ cái cuối cùng khỏi một chuỗi trong bảng tính của mình. Đối với điều này, bạn có thể sử dụng một chức năng rất tiện dụng.
=REPLACE[A2,1,B2,""]
5=REPLACE[A2,1,B2,""]
5 trả về X ký tự đầu tiên trong một chuỗi, dựa trên số lượng ký tự bạn chỉ định. Thích. TRÁI[văn bản,num_chars]
Giả sử bạn muốn xóa 3 ký tự cuối khỏi chuỗi trong ô
=REPLACE[A2,1,B2,""]
7, bạn có thể sử dụng=REPLACE[A2,1,B2,""]
8Hay tổng quát hơn
=REPLACE[A2,1,B2,""]
9Chức năng LEFT[] này hoạt động như thế nào?
Ví dụ, trong ô
=REPLACE[A2,1,B2,""]
7 bạn có một chuỗi “IncomeMAX” và bạn muốn xóa ba ký tự cuối [“MAX”] Cột B hiện hiển thị tên nhóm trong cột A với ba ký tự đầu tiên bị xóa khỏi mỗi tên nhómCông thức này hoạt động như thế nào
Hàm RIGHT[] trong Excel trích xuất một số ký tự cụ thể từ phía bên phải của chuỗi
Hàm LEN[] trong Excel dùng để tìm độ dài của chuỗi
Do đó, công thức của chúng tôi yêu cầu Excel trích xuất số lượng ký tự bằng độ dài của chuỗi trừ đi ba ký tự
Do đó, công thức của chúng tôi hiển thị toàn bộ chuỗi với ba ký tự đầu tiên bị xóa
Ghi chú. Khoảng trống ở đầu chuỗi được tính là ký tự. Trước tiên, bạn có thể cần xóa các khoảng trống để có được kết quả mong muốn
Trong khi xử lý dữ liệu trong Excel, làm sạch dữ liệu là một bước quan trọng để trình bày dữ liệu một cách có ý nghĩa. Để xóa dữ liệu, bạn có thể cần xóa các ký tự khỏi văn bản của mình. Về vấn đề này, blog này sẽ hướng dẫn bạn hơn 7 phương pháp loại bỏ ký tự từ bên trái trong Excel
Tải xuống Xóa ký tự từ bên trái trong Excel. xlsx
Mục lục
Giới thiệu về Bộ dữ liệu
Tập dữ liệu tôi đã sử dụng trong toàn bộ bài viết có 3 cột
- Dữ liệu thô. Cột này chứa các văn bản có ký tự không mong muốn ở đầu
- Số ký tự cần xóa. Nó có một danh sách các số đề cập đến số ký tự cần xóa từ bên trái
- Dọn dẹp dữ liệu. Tôi đã sử dụng cột này để giữ dữ liệu sau khi loại bỏ các ký tự không mong muốn từ bên trái
Cách dễ nhất để loại bỏ các ký tự từ bên trái trong Excel
Cách dễ nhất để xóa các ký tự từ bên trái là chèn công thức bằng hàm REPLACE
cú pháp
=REPLACE[old_text, start_num, num_chars, new_text]
Hướng dẫn sử dụng
Bước 1. Nhập công thức vào ô trên cùng của cột “Dữ liệu đã xóa”
=REPLACE[A2,1,B2,""]
Giải thích công thức
- Ô A2 chứa văn bản
- 1 là số vị trí ký tự từ nơi bắt đầu thay thế
- B2 có số đến số ký tự cần xóa từ bên trái
- “” đại diện cho một giá trị null. Hàm REPLACE thay thế số ký tự được nêu trong ô B2 bằng khoảng trống [“”]
Bước 2. Nhấn ENTER để chèn công thức vào ô C2
Bước 3. Đưa con trỏ đến góc dưới cùng bên phải của ô C2. Biểu tượng Fill Handle sẽ xuất hiện
Bước 4. Nhấp đúp vào biểu tượng để sao chép công thức đến ô C10
Kết quả cuối cùng
Các ký tự không mong muốn được xóa khỏi bên trái của văn bản trong cột Dữ liệu thô. Bạn sẽ tìm thấy kết quả trong cột Dọn dẹp dữ liệu
Phương pháp thay thế #1. Xóa ký tự từ bên trái trong Excel bằng hàm MID và LEN
Công thức thay thế đầu tiên để loại bỏ các ký tự từ bên trái có hai hàm, hàm MID & hàm LEN
cú pháp
=MID[text,1+num_chars,LEN[text]]
Hướng dẫn sử dụng
Bước 1. Viết công thức này vào ô trên cùng của cột, “Dữ liệu đã dọn sạch”
=MID[A2,1+B2,LEN[A2]]
Giải thích công thức
- A2 chứa văn bản [#$%Pháp] với các ký tự không mong muốn ở bên trái
- B2 có số đến số ký tự cần xóa từ bên trái là 3
- LEN[A2] xác định tổng số ký tự trong ô A2 là 9
- Hàm MID trả về LEN[A2] e. 9 ký tự bắt đầu từ [1 + B2 =] ký tự thứ 4 của #$%France. Ở đây, ký tự thứ 4 là F. Như vậy đầu ra là Pháp
Bước 2. Nhấn ENTER để chèn công thức
Bước 3. Nhấp đúp vào biểu tượng Fill Handle để sao chép công thức xuống các ô còn lại
Kết quả cuối cùng
Giờ đây, các ký tự không mong muốn sẽ bị xóa khỏi phần đầu của văn bản trong cột Dữ liệu thô. Bạn sẽ thấy kết quả này trong cột Dọn dẹp dữ liệu
Phương pháp thay thế #2. Xóa các ký tự từ bên trái bằng hàm LEFT và SUBSTITUTE trong Excel
Bạn có thể viết một công thức khác với hàm LEFT và SUBSTITUTE. Chức năng này cũng sẽ loại bỏ các ký tự không mong muốn ngay từ đầu
cú pháp
=SUBSTITUTE[Text,LEFT[Text,num_chars],""]
Hướng dẫn sử dụng
Bước 1. Nhập công thức vào ô C2
=SUBSTITUTE[A2,LEFT[A2,B2],""]
Phân tích công thức
- TRÁI[A2,B2] chọn 3 ký tự [Được đề cập trong ô B2] từ phía bên trái của A2 [#$%Pháp]
- SUBSTITUTE[A2,LEFT[A2,B2],””] thay thế 3 ký tự đầu tiên của ô A2 bằng một giá trị trống [“”]
Bước 2. Nhấn ENTER để chèn công thức vào ô C2
Bước 3. Nhấp đúp vào biểu tượng “+” ở góc dưới bên phải để sao chép công thức trên
Kết quả cuối cùng
Cuối cùng, các ký tự không mong muốn sẽ bị xóa khỏi phần đầu của văn bản trong cột Dữ liệu thô. Bạn sẽ tìm thấy đầu ra trong cột Dọn dẹp dữ liệu
Phương pháp thay thế #3. Xóa các ký tự từ bên trái bằng các hàm RIGHT và LEN
Công thức thay thế thứ ba bao gồm các hàm RIGHT và LEN
cú pháp
=RIGHT[text,LEN[text]-num_chars]
Hướng dẫn sử dụng
Bước 1. Sử dụng công thức này trong ô C2
=RIGHT[A2,LEN[A2]-B2]
Giải thích công thức
- LEN[A2] tính độ dài của văn bản trong ô A2 [#$% France] là 9
- B2 cho biết số ký tự cần xóa khỏi đầu văn bản là 3
- Hàm RIGHT trích xuất [9 – 3 =] 6 ký tự từ phần bên phải của #$% France. Vì vậy, đầu ra trở thành Pháp
Bước 2. Nhấn ENTER để chèn công thức
Bước 3. Nhấp chuột trái vào biểu tượng “+” và giữ nó. Sau đó kéo xuống cho đến ô C10
Kết quả cuối cùng
Tại thời điểm này, các ký tự không mong muốn sẽ bị xóa khỏi phần đầu của văn bản trong cột Dữ liệu thô. Bạn sẽ nhận được kết quả cuối cùng trong cột Dọn dẹp dữ liệu
Phương pháp thay thế #4. Sử dụng Flash Fill để bỏ qua các ký tự từ bên trái trong Excel
Nếu bạn muốn loại bỏ các ký tự đặc biệt ở đầu văn bản, bạn có thể sử dụng tính năng Flash Fill
Tính năng này hoạt động dựa trên nhận dạng mẫu. Nếu bạn cho Excel biết rằng bạn muốn loại bỏ một phần cụ thể, Excel sẽ tự thực hiện phần còn lại. Nhưng bạn phải đảm bảo với Excel rằng có một mẫu trong tác vụ đó
Một khía cạnh quan trọng khác của tính năng này là tính năng Flash Fill chỉ hoạt động trong các cột liền kề
Nhìn vào ảnh chụp màn hình sau. Cột Dữ liệu thô có các văn bản có ký tự đặc biệt không mong muốn ở bên trái. Cột liền kề “Cleaned Up Data” sẽ được sử dụng để lưu trữ đầu ra
Hướng dẫn sử dụng
Bước 1. Chèn hai văn bản không có ký tự mà bạn muốn loại bỏ
Bước 2. Chọn toàn bộ cột bao gồm các văn bản bạn vừa nhập
Bước 3. Nhấp vào trình đơn thả xuống Điền trong tab Trang chủ
Bước 4. Chọn Flash Fill
Kết quả cuối cùng
Tính năng Flash Fill đã phát hiện mẫu và tự động xóa tất cả các ký tự đặc biệt từ bên trái
Phương pháp thay thế #5. Xóa các ký tự từ bên trái trong Excel bằng Trình hướng dẫn chuyển văn bản thành cột
Nếu bạn có văn bản và số được phân tách bằng dấu cách, thì bạn cũng có thể sử dụng Trình hướng dẫn chuyển văn bản thành cột để tách chúng ra. Bằng cách này, bạn có thể loại bỏ các ký tự không mong muốn từ bên trái
Hướng dẫn sử dụng
Bước 1. Trước tiên hãy chọn phạm vi dữ liệu của bạn
Bước 2. Nhấp vào Dữ liệu trong dải băng chính
Bước 3. Click vào lệnh Text to Columns của nhóm Data Tools
Bước 4. Chọn Fixed width trong hộp thoại Convert Text to Columns Wizard và nhấn Next
Bạn sẽ thấy các văn bản và số được chia nhỏ trong phần Xem trước dữ liệu
Bước_5. Chỉ cần nhấn Tiếp theo
Bước_6. Đảm bảo rằng định dạng dữ liệu Cột được đặt thành Chung và nhấn Kết thúc
Kết quả cuối cùng
Các văn bản và số được chia thành hai cột khác nhau. Bạn có thể sử dụng thủ thuật này để loại bỏ các ký tự từ bên trái
Phương pháp thay thế #6. Tạo một hàm do người dùng xác định bằng VBA Script để xóa các ký tự từ bên trái trong Excel
Hãy tạo một chức năng do người dùng xác định để thực hiện nhiệm vụ của chúng tôi dễ dàng hơn
Hàm do người dùng xác định sẽ xóa ngay các ký tự từ bên trái nếu tôi cung cấp văn bản và số lượng ký tự cần xóa
Vậy hãy bắt đầu
Hướng dẫn sử dụng
Bước 1. Nhấp chuột phải vào tên bảng tính của bạn và chọn Xem mã
Thao tác này sẽ mở Trình soạn thảo Visual Basic
Bước 2. Nhấp vào Mô-đun từ menu Chèn
Bước 3. Chỉ cần sao chép đoạn mã này
________số 8Bước 4. Nhấn CTRL + V để dán mã vào mô-đun trống mà chúng tôi vừa tạo
Tập lệnh VBA này tạo một hàm có tên ClearCharLeft yêu cầu hai đối số
Bước_5. Nhấn CTRL + S để lưu tệp
Một hộp thoại sẽ bật lên
Bước_6. Nhấp vào Không
Bước_7. Lưu tệp Excel của bạn dưới dạng Excel Macro-Enabled Workbook
Như vậy là chúng ta đã hoàn thành việc tạo một hàm do người dùng định nghĩa
Bây giờ, hãy xóa các ký tự từ bên trái bằng chức năng
cú pháp
=ClearCharLeft[cell_ref,char_num]
Hướng dẫn sử dụng
Bước 1. Viết công thức này vào ô C2
=REPLACE[A2,1,B2,""]
0
Giải thích công thức
- A2 chứa văn bản có các ký tự không mong muốn cần xóa
- B2 cho biết số ký tự cần xóa
Bước 2. Nhấn ENTER để chèn công thức
Bước 3. Nhấp đúp vào biểu tượng Fill Handle để sao chép công thức
Kết quả cuối cùng
Bạn sẽ nhận thấy các ký tự không mong muốn bị xóa khỏi phần đầu của văn bản trong cột Dữ liệu thô. Bạn sẽ nhận được kết quả cuối cùng trong cột Dọn dẹp dữ liệu
Xóa các ký tự từ trái sang một văn bản cụ thể trong Excel
Chúng ta có thể kết hợp các hàm RIGHT, LEN, & FIND để tạo công thức loại bỏ các ký tự từ bên trái cho đến khi gặp một văn bản hoặc ký hiệu cụ thể
Trong trường hợp cụ thể này, chúng tôi sẽ sử dụng một công thức để loại bỏ các ký tự từ bên trái cho đến khi gặp dấu phẩy [,] đầu tiên
cú pháp
=REPLACE[A2,1,B2,""]
1Hướng dẫn sử dụng
Bước 1. Nhập công thức này vào ô B2
=REPLACE[A2,1,B2,""]
2Giải thích công thức
- FIND[“,”,A2] tìm kiếm dấu phẩy đầu tiên trong ô
- LEN[A2] trả về tổng số ký tự trong ô A2
- LEN[A2]-FIND[“,”,A2] trừ số ký tự trước dấu phẩy đầu tiên gặp trong ô A2 khỏi tổng số ký tự trong ô A2
- RIGHT[A2,LEN[A2]-FIND[“,”,A2]] trích xuất các ký tự sau dấu phẩy đầu tiên từ ô A2
Bước 2. Nhấn ENTER để chèn công thức trên
Bước 3. Nhấp đúp vào biểu tượng Fill Handle để sao chép xuống công thức
Kết quả cuối cùng
Tất cả các ký tự trước dấu phẩy đầu tiên đều bị xóa. Những kết quả này được lưu trữ trong cột, “Dữ liệu đã được dọn sạch”
Xóa các ký tự từ trái và phải trong Excel
Ở đây, tôi sẽ tạo một công thức sử dụng hàm MID và LEN để xóa ký tự cả bên trái và bên phải trong Excel
cú pháp
=REPLACE[A2,1,B2,""]
3Hướng dẫn sử dụng
Bước 1. Sử dụng công thức này trong ô B2
=REPLACE[A2,1,B2,""]
4
Giải thích công thức
- 9 được sử dụng để xóa “Quốc gia. ” [9 ký tự bao gồm khoảng trắng] từ ô A2
- 9+8 Ở đây, 9 là xóa “Quốc gia. ” [9 ký tự]. Sau đó, 8 là để loại bỏ “, Châu Âu” [8 ký tự]
- LEN[A2] trả về tổng số ký tự trong ô A2 là 23
- LEN[A2] – [9+8] bằng [23 – 17 =] 6
- MID[A2, 9+1, LEN[A2] – [9+8]] cuối cùng trở thành MID[A2, 10, 6]. Như vậy hàm MID trích 6 ký tự giữa bắt đầu từ ký tự số 10. Vì vậy, chúng tôi đưa Pháp ra khỏi đất nước. Pháp, Châu Âu
Bước 2. Nhấn ENTER ngay
Bước 3. Nhấp đúp vào biểu tượng Fill Handle để sao chép công thức
Kết quả cuối cùng
Các ký tự không mong muốn được loại bỏ cả từ bên trái và bên phải. Xem kết quả ở cột “Cleaned Up Data”
Phần kết luận
Tôi đã cố gắng thảo luận về hơn 7 phương pháp khác nhau để loại bỏ các ký tự không mong muốn từ bên trái trong Excel. Một số phương pháp dựa trên việc sử dụng các công thức khác nhau, trong khi một số phương pháp sử dụng các nút lệnh khác nhau. Tôi thực sự hy vọng rằng bạn đã tìm thấy các giải pháp thích hợp từ bài viết này. Cảm ơn