Chào các bạn trong diễn đàn Sheet & Excel!
Dạo này mình hay phải làm việc với các file dữ liệu mà số liệu được nhập dưới dạng Text, lại còn lộn xộn đủ các kiểu dấu phẩy, dấu chấm, thậm chí là các ký tự đặc biệt khác. Ví dụ: 1.234,56, 1,234.56, hoặc 1 234,56, 1.234.56 USD...
Việc này gây khó khăn khi muốn thực hiện các phép tính toán học hoặc dùng các hàm như SUM, AVERAGE. Mình đã thử một vài cách nhưng thấy có một phương pháp khá hiệu quả và nhanh chóng mà muốn chia sẻ lại với mọi người.
Cách 1: Sử dụng chức năng Text to Columns
- Chọn vùng dữ liệu cần chuyển đổi.
- Vào tab Data -> Text to Columns.
- Chọn Delimited hoặc Fixed width tùy theo cấu trúc dữ liệu của bạn.
- Ở bước tiếp theo, nếu dữ liệu của bạn có các ký tự ngăn cách lộn xộn, bạn có thể chọn Other và nhập các ký tự đó vào (ví dụ: dấu phẩy, dấu chấm). Tuy nhiên, cách này đôi khi không xử lý hết các trường hợp đặc biệt.
- Quan trọng là ở bước cuối cùng, trong phần Column data format, bạn chọn Date và chọn định dạng số (ví dụ: General hoặc Number). Excel sẽ cố gắng nhận diện và chuyển đổi.
Cách 2: Sử dụng hàm SUBSTITUTE kết hợp với Value
Đây là cách mình thấy linh hoạt và mạnh mẽ hơn, đặc biệt khi dữ liệu có nhiều định dạng khác nhau. Bạn có thể dùng công thức kết hợp:
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,'.',''),',',''))Giải thích:
- Giả sử số liệu gốc ở ô A1.
SUBSTITUTE(A1,'.',''): Loại bỏ tất cả dấu chấm.SUBSTITUTE(...,',',''): Loại bỏ tất cả dấu phẩy khỏi kết quả trên.VALUE(...): Chuyển đổi chuỗi kết quả thành số.
Bạn có thể lồng thêm các hàm SUBSTITUTE khác nếu có ký tự đặc biệt hoặc các đơn vị tiền tệ đi kèm. Ví dụ, nếu có ' USD':
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,' USD',''),'.',''),',',''))Cách này giúp chuẩn hóa hoàn toàn dữ liệu về dạng số để có thể tính toán. Hy vọng mẹo nhỏ này hữu ích cho các bạn khi xử lý dữ liệu!
Có ai có cách nào hay hơn hoặc gặp trường hợp phức tạp hơn không, chia sẻ thêm cho mọi người cùng học hỏi nhé!