Chia sẻ cách xử lý chuỗi ký tự 'lạ' sau khi copy từ web về Excel

Chào mọi người, dạo này mình hay phải copy dữ liệu từ các trang web về Excel để làm báo cáo. Tuy nhiên, có một vấn đề mình gặp phải khá thường xuyên là sau khi copy về, các chuỗi ký tự đôi khi bị dính thêm những ký tự 'lạ' mà mắt thường không thấy được, làm ảnh hưởng đến việc tính toán hoặc lọc dữ liệu.

Ví dụ, mình copy một danh sách tên sản phẩm, nhìn trên web thì thấy bình thường, nhưng khi paste vào Excel thì có những ký tự 'trông như dấu cách' nhưng lại không phải là dấu cách, hoặc những ký tự không in được. Khi dùng các hàm như VLOOKUP hay SUMIFS thì báo lỗi hoặc trả về kết quả sai.

Sau một hồi loay hoay, mình tìm ra được một cách khá hiệu quả để xử lý vấn đề này mà không cần dùng đến VBA phức tạp. Đó là sử dụng kết hợp hàm CLEAN và SUBSTITUTE.

Cách làm như sau:

1. Tạo một cột phụ bên cạnh cột chứa dữ liệu bị lỗi.
2. Tại ô đầu tiên của cột phụ, nhập công thức: =CLEAN(A1) (giả sử dữ liệu lỗi nằm ở cột A, bắt đầu từ A1). Hàm CLEAN sẽ loại bỏ 32 ký tự điều khiển không in được.
3. Tiếp theo, bạn cần xử lý các ký tự 'trông như dấu cách' nhưng không phải. Bạn có thể dùng hàm SUBSTITUTE. Tuy nhiên, để xác định ký tự 'lạ' đó là gì thì hơi khó. Một cách khác là bạn có thể thử dùng hàm TRIM sau hàm CLEAN để loại bỏ các khoảng trắng thừa. Công thức đầy đủ sẽ là: =TRIM(CLEAN(A1)).
4. Nếu vẫn còn ký tự lạ, bạn có thể thử xác định ký tự đó bằng cách dùng hàm CODE để xem mã ASCII của nó và dùng SUBSTITUTE để thay thế. Ví dụ, nếu bạn xác định được ký tự lạ có mã ASCII là 160 (thường gặp khi copy từ web), bạn có thể dùng: =SUBSTITUTE(TRIM(CLEAN(A1)), CHAR(160), " ").
5. Sau khi áp dụng công thức cho tất cả các dòng, bạn tiến hành Copy cột phụ này, sau đó Paste Special -> Values đè lên cột dữ liệu gốc. Cuối cùng, xóa cột phụ đi.

Hy vọng mẹo nhỏ này sẽ giúp ích cho các bạn khi làm việc với dữ liệu copy từ nguồn bên ngoài. Có ai có cách nào hay hơn thì chia sẻ thêm nhé!

Chào bạn,

Vấn đề này mình cũng gặp hoài luôn khi copy dữ liệu từ web về Excel. Những ký tự "lạ" đó thường là các ký tự không in được (non-printable characters), phổ biến nhất là ký tự xuống dòng hoặc tab không thấy rõ.

Cách mình hay làm là dùng hàm CLEAN kết hợp với TRIM. Hàm CLEAN sẽ loại bỏ các ký tự không in được, còn TRIM thì sẽ loại bỏ các dấu cách thừa ở đầu, cuối và các dấu cách kép ở giữa chuỗi.

Bạn thử dùng công thức sau ở một cột mới nhé:

=TRIM(CLEAN(A1))

(Thay A1 bằng ô chứa dữ liệu bạn muốn xử lý)

Sau đó, bạn chỉ cần copy kết quả này và paste lại vào cột cũ là xong. Hy vọng giúp ích cho bạn!

Chào bạn,

Vấn đề này đúng là rất hay gặp, nhất là khi làm việc với dữ liệu từ nhiều nguồn khác nhau. Những ký tự "lạ" đó đôi khi khiến việc sắp xếp và phân tích dữ liệu trở nên khó khăn thật.

Ngoài cách dùng TRIM(CLEAN()) mà bạn kia chia sẻ, mình còn hay dùng một cách khác cũng khá hiệu quả, đó là dùng chức năng Find and Replace.

Bạn có thể thử mở hộp thoại Find and Replace (Ctrl + H), sau đó trong ô "Find what:", bạn nhấn giữ phím Alt và gõ số 0160 trên bàn phím số (NumPad). Ô "Replace with:" thì bạn để trống hoặc điền dấu cách bình thường. Sau đó nhấn "Replace All". Ký tự Alt+0160 này thường là ký tự khoảng trắng không ngắt (non-breaking space) mà mắt thường khó thấy.

Hoặc nếu bạn muốn loại bỏ hết các ký tự không hiển thị, bạn có thể thử tìm kiếm ký tự đặc biệt bằng cách vào Insert > Symbol, chọn Font (normal text), Subset (Common Miscellaneous) và tìm các ký tự như ký tự xuống dòng (CR - Carriage Return, LF - Line Feed) rồi thay thế chúng bằng dấu cách hoặc để trống.

Cách này đôi khi cũng tiện vì không cần thêm cột phụ.

Bạn thử xem cách nào phù hợp hơn với mình nhé!

Mình cũng hay gặp tình huống này lắm khi copy dữ liệu từ web. Ký tự "lạ" đúng là "khó chịu" thật, làm sai lệch hết dữ liệu.

Ngoài các cách dùng hàm TRIM(CLEAN()) hay Find and Replace mà các bạn đã chia sẻ, mình còn hay áp dụng thêm một mẹo nhỏ nữa.

Đó là sử dụng chức năng Text to Columns.

Bạn bôi đen vùng dữ liệu cần xử lý, vào tab Data, chọn Text to Columns. Chọn Delimited, rồi nhấn Next. Ở bước chọn ký tự phân tách, bạn có thể tích vào ô Other và thử nhập một ký tự mà bạn nghi ngờ (ví dụ: ký tự xuống dòng hoặc tab). Đôi khi Excel sẽ tự động nhận diện và tách chúng ra. Sau đó, bạn có thể chọn kiểu dữ liệu là General và hoàn tất.

Cách này đôi khi giúp mình "nhìn" rõ hơn những ký tự ẩn đó và xử lý chúng một cách trực quan hơn.