Biến đổi dữ liệu Text dạng '1,234.56' sang Số chuẩn trong Excel

Chào mọi người,

Trong quá trình làm việc với dữ liệu nhập từ nhiều nguồn khác nhau, đôi khi chúng ta gặp phải trường hợp số liệu bị lưu dưới dạng Text, đặc biệt là khi có dấu phân cách hàng nghìn (,) và dấu thập phân (.) theo quy ước của một số quốc gia. Ví dụ, một số hiển thị là '1,234.56 thay vì 1234.56.

Việc này gây khó khăn khi thực hiện các phép tính toán học hoặc phân tích dữ liệu. Hôm nay, mình muốn chia sẻ một thủ thuật đơn giản nhưng rất hiệu quả để xử lý dạng dữ liệu này mà không cần dùng đến Power Query phức tạp.

Cách 1: Sử dụng hàm VALUE kết hợp SUBSTITUTE

Đây là cách phổ biến và dễ hiểu nhất. Chúng ta sẽ dùng hàm SUBSTITUTE để loại bỏ dấu phân cách hàng nghìn (,), sau đó dùng hàm VALUE để chuyển đổi chuỗi Text thành số.

Giả sử dữ liệu của bạn nằm ở ô A1, công thức sẽ là:

=VALUE(SUBSTITUTE(A1,",",""))

Nếu dấu thập phân của bạn là dấu phẩy (,) và dấu phân cách hàng nghìn là dấu chấm (.) (ví dụ: '1.234,56), bạn cần điều chỉnh lại hàm SUBSTITUTE:

=VALUE(SUBSTITUTE(A1,".",""))

Lưu ý: Hàm VALUE sẽ tự động nhận diện dấu thập phân theo cài đặt hệ thống của máy tính bạn. Nếu bạn muốn chắc chắn, có thể kết hợp thêm hàm REPLACE hoặc SUBSTITUTE nữa để chuẩn hóa dấu thập phân.

Cách 2: Sử dụng Text to Columns (nếu số lượng lớn và cần làm thủ công)

Nếu bạn không quen dùng công thức hoặc muốn xử lý nhanh một lần cho nhiều ô:

1. Chọn vùng dữ liệu Text cần chuyển đổi.
2. Vào tab Data > Text to Columns.
3. Chọn Delimited nếu dữ liệu có dấu phân cách, hoặc Fixed width nếu dữ liệu có độ rộng cố định. Trong trường hợp này, thường là Delimited.
4. Nhấn Next.
5. Bỏ chọn tất cả các dấu phân cách mặc định.
6. Nếu số của bạn có dấu , là hàng nghìn và . là thập phân, bạn có thể không cần chọn dấu phân cách nào ở bước này. Excel thường sẽ nhận diện đúng. Nếu có dấu phân cách khác, hãy chọn nó.
7. Nhấn Next.
8. Ở bước cuối cùng, chọn General hoặc Number làm định dạng cột.
9. Nhấn Finish.

Cách này đôi khi vẫn gặp lỗi nếu Excel không tự nhận diện đúng. Tuy nhiên, nó hữu ích khi bạn cần làm thủ công.

Hy vọng mẹo nhỏ này sẽ giúp ích cho công việc của các bạn!

Cảm ơn bạn đã chia sẻ thủ thuật này! Mình cũng thường xuyên gặp phải tình trạng dữ liệu số bị lưu dưới dạng text với các dấu phân cách khác nhau. Cách bạn đề cập rất tiện lợi, nhất là khi không muốn dùng thêm cột phụ.

Mình có một cách khác cũng khá nhanh, đó là dùng chức năng Text to Columns. Bạn chọn cột dữ liệu, vào tab Data -> Text to Columns, chọn Delimited hoặc Fixed width tùy trường hợp, rồi ở bước cuối, bạn chọn định dạng là Number. Với các số dạng '1,234.56', Excel thường tự nhận diện dấu chấm làm thập phân. Nếu không, bạn có thể vào Regional Settings của Windows để điều chỉnh tạm thời dấu phân cách hoặc dùng hàm SUBSTITUTE để thay thế dấu phẩy trước khi chuyển đổi.

Bạn có gặp trường hợp nào mà cách này không xử lý được không?

Chào bạn, cảm ơn bạn đã chia sẻ cách dùng Text to Columns nhé. Mình thấy cách này cũng rất hay, nhất là với những ai quen thuộc với các tùy chọn trong Data tab.

Đôi khi mình cũng gặp phải trường hợp dữ liệu text dạng số này, đặc biệt là khi lấy dữ liệu từ web hoặc các hệ thống cũ. Ngoài cách của bạn và chủ bài viết, mình còn hay dùng tổ hợp hàm VALUE kết hợp với SUBSTITUTE. Ví dụ, với ô A1 chứa '1,234.56', mình sẽ dùng công thức =VALUE(SUBSTITUTE(A1,",","")). Cách này đơn giản và linh hoạt, chỉ cần thay đổi dấu phân cách trong hàm SUBSTITUTE là xử lý được nhiều trường hợp.

Bạn đã thử dùng cách này chưa, thấy nó có hiệu quả với các định dạng số phức tạp hơn không?

Cái này hay nè! Mình cũng hay gặp phải tình trạng số liệu nhập về cứ lơ lửng ở dạng text thế này, mà sửa thủ công thì đúng là mất thời gian.

Nhân tiện bạn nhắc đến dấu phân cách, mình có một mẹo nhỏ với hàm SUBSTITUTE kết hợp với VALUE. Ví dụ, nếu ô A1 có '1,234.56', bạn chỉ cần dùng công thức =VALUE(SUBSTITUTE(A1,",",".")). Nó sẽ loại bỏ dấu phẩy và thay thế bằng dấu chấm để Excel hiểu là số thập phân. Hoặc nếu là dạng '1.234,56' thì dùng =VALUE(SUBSTITUTE(A1,".","")) rồi SUBSTITUTE dấu phẩy. Linh hoạt lắm.

Cách này có xử lý được trường hợp số nguyên có dấu phẩy ngăn hàng nghìn không bạn nhỉ?