Dán từ web / PDF / phần mềm về: số dính chữ, ngày ngược, khoảng trắng ma

Cơ bảnDọn & chuẩn hoá dữ liệu7 bước

Đầu bài

Copy bảng từ trang web hay bản xuất PDF về, nhìn thì ra dáng bảng nhưng SUM ra 0, ngày 03/04 không biết là mùng 3 tháng 4 hay mùng 4 tháng 3, mà TRIM cũng không làm sạch được.

Các bước làm

1
Dán thành CHỮ trước, đừng dán nguyên khối
Ctrl+Alt+V rồi chọn Text. Dán nguyên khối là kéo theo cả bảng HTML, ô gộp và hình ẩn của trang web — mấy thứ đó về sau không ai gỡ nổi.
2
Đo xem trong ô còn gì thừa
=LEN(ô) so với số ký tự đếm bằng mắt, lệch là có rác. =CODE(RIGHT(ô,1)) ra 160 nghĩa là khoảng trắng cứng của web — TRIM không bắt được con này, đó là lý do dọn hoài không sạch.
3
Diệt đúng con 160 rồi mới TRIM
=TRIM(SUBSTITUTE(CLEAN(ô), UNICHAR(160), " ")) — CLEAN bỏ ký tự điều khiển, SUBSTITUTE bỏ khoảng trắng cứng, TRIM gom cách thừa. Thiếu khúc giữa là công cốc.
4
Bóc số ra khỏi chuỗi kiểu "1.250.000 đ"
=VALUE(SUBSTITUTE(SUBSTITUTE(ô,".","")," đ","")) — bỏ dấu nghìn và đuôi tiền rồi mới ép về số. Còn ra #VALUE! là vẫn sót ký tự lạ, quay lại bước 2.
5
Ép cả cột ngày về đúng thứ tự mình muốn
Chọn cột → Alt › A › E (Text to Columns) → Next → Next → chọn Date và đúng thứ tự DMY → Finish. Đây là cách duy nhất bắt Excel hiểu ngày theo thứ tự MÌNH chỉ định thay vì tự đoán.
6
Việc lặt vặt lặp lại thì để Flash Fill làm
Gõ tay kết quả mẫu ở dòng đầu rồi Ctrl+E. Excel bắt chước mẫu cho cả cột — nhanh hơn ngồi nghĩ công thức cho mấy kiểu tách không có quy luật rõ ràng.
7
Chốt lại thành giá trị chết
Copy cột đã sạch → Alt › H › V › V dán đè lên chính nó rồi xoá cột thô. Để công thức trỏ vào dữ liệu bẩn thì hôm sau ai xoá nhầm cột là hỏng cả bảng.

Phím dùng trong bài

Ctrl+Alt+V Ctrl+E Alt › A › E Alt › H › V › V Ctrl+Enter

Bẫy hay dính

Số "trông giống số" mà căn TRÁI thì vẫn là chữ. Sửa xong phải thấy nó tự nhảy sang căn phải mới tính là số thật — đừng lấy việc nhìn thấy dấu phẩy phần nghìn làm bằng chứng, chuỗi chữ cũng có dấu phẩy được.

Cùng nhóm dọn & chuẩn hoá dữ liệu

Tất cả tình huống