Chào các bác, dạo này em hay gặp một vấn đề khá khó chịu khi làm việc với Excel, đó là lỗi #VALUE! khi thực hiện các phép tính cộng trừ đơn giản.
Cụ thể là khi em copy dữ liệu từ một nguồn bên ngoài (có thể là web, file PDF, hoặc file Excel khác), đôi khi trong các ô số lại lẫn một vài ký tự không nhìn thấy được, ví dụ như khoảng trắng thừa, ký tự xuống dòng ẩn, hoặc các ký tự đặc biệt khác. Khi em thử cộng hoặc trừ các cột chứa những dữ liệu này, Excel báo lỗi #VALUE! và không cho ra kết quả.
Em đã thử dùng hàm TRIM để loại bỏ khoảng trắng thừa, nhưng nó không xử lý được các ký tự ẩn kia. Sau một hồi mò mẫm, em phát hiện ra một cách khá hiệu quả để làm sạch dữ liệu trước khi tính toán:
- Sử dụng kết hợp hàm
SUBSTITUTEvàCLEAN. HàmCLEANsẽ loại bỏ các ký tự không in được trong văn bản (như ký tự xuống dòng, tab...). HàmSUBSTITUTEsẽ giúp ta thay thế các ký tự cụ thể mà ta muốn loại bỏ (ví dụ như dấu chấm phẩy nếu nó đang là dấu phân cách thập phân trong dữ liệu của bạn). - Một cách khác là sử dụng tính năng Find and Replace (Ctrl+H), chọn Replace All và để trống ô Replace what, sau đó tìm và thay thế bằng khoảng trắng. Tuy nhiên, cách này đôi khi không hiệu quả với các ký tự ẩn hoàn toàn.
- Cách cuối cùng em hay dùng là chuyển đổi vùng dữ liệu sang dạng Text rồi lại chuyển về Number. Đôi khi chỉ cần thế này là Excel tự nhận diện và loại bỏ ký tự lạ.
Cách em hay dùng nhất là kết hợp =VALUE(CLEAN(SUBSTITUTE(A1,CHAR(10)," "))) (thay A1 bằng ô chứa dữ liệu của bạn, CHAR(10) là ký tự xuống dòng, bạn có thể thay bằng ký tự khác nếu cần). Sau đó copy công thức này xuống và paste Values (Dán Đặc Biệt -> Giá Trị) là xong.
Các bác có gặp trường hợp này chưa và có cách nào khác tối ưu hơn không ạ? Chia sẻ cho em với nhé!