Chào các bạn, mình là thành viên mới của diễn đàn. Mình thường xuyên làm việc với dữ liệu Excel và gặp khá nhiều tình huống dở khóc dở cười với dữ liệu dạng Text. Gần đây, mình nhận được một file báo cáo mà các cột số liệu lại bị định dạng Text, lại còn lẫn lộn các ký tự như dấu phẩy, dấu chấm, hoặc thậm chí là các ký tự lạ khác. Việc này gây khó khăn khi thực hiện các phép tính toán học.
Mình đã thử nhiều cách, và hôm nay muốn chia sẻ với mọi người một phương pháp khá hiệu quả để xử lý loại dữ liệu này, giúp chuyển đổi về dạng số chuẩn để tính toán.
Các bước thực hiện:
- Bước 1: Chuẩn hóa ký tự thập phân và dấu phân cách hàng nghìn
- Sử dụng hàm
SUBSTITUTEđể thay thế các ký tự không mong muốn. Ví dụ, nếu số của bạn ở ô A1 và có ký tự ',' là thập phân, ta có thể dùng:=SUBSTITUTE(A1, "", "")Tiếp theo, thay thế dấu phân cách hàng nghìn (ví dụ dấu chấm):
=SUBSTITUTE(SUBSTITUTE(A1, ".", ""), ",", ".")Lưu ý: Thứ tự thay thế rất quan trọng để tránh nhầm lẫn.
- Bước 2: Chuyển đổi sang dạng Số
- Hàm
VALUE: Đây là hàm trực tiếp nhất để chuyển đổi một chuỗi văn bản đại diện cho số thành số. - Phép toán nhân với 1: Nhân chuỗi văn bản với 1 cũng sẽ ép nó về dạng số. Ví dụ:
hoặc=VALUE(SUBSTITUTE(SUBSTITUTE(A1, ".", ""), ",", "."))= (SUBSTITUTE(SUBSTITUTE(A1, ".", ""), ",", "."))*1
Đầu tiên, ta cần xử lý các ký tự phân biệt thập phân (thường là dấu phẩy ',' trong tiếng Việt) và dấu phân cách hàng nghìn (thường là dấu chấm '.' hoặc khoảng trắng).
Sau khi đã chuẩn hóa các ký tự, ta có thể dùng các hàm sau để ép kiểu về dạng số:
Một số trường hợp đặc biệt:
- Nếu dữ liệu có lẫn cả chữ và số, bạn cần kết hợp thêm các hàm như
LEFT,RIGHT,MIDđể trích xuất phần số trước khi áp dụng các bước trên. - Trường hợp ký tự thập phân là dấu chấm và hàng nghìn là dấu phẩy (theo chuẩn Anh-Mỹ), bạn chỉ cần đảo ngược lại hàm
SUBSTITUTE.
Hy vọng chia sẻ này hữu ích cho các bạn khi gặp phải tình huống tương tự. Nếu có cách nào khác hay hơn, mọi người cùng thảo luận nhé!