=VALUE(A2) chuyển một số được lưu dưới dạng văn bản trong A2 thành số thật. Số dạng văn bản trông bình thường, nhưng SUM, AVERAGE và COUNT bỏ qua chúng, đó là lý do tổng có thể ra 0:
| A | B | C | |
|---|---|---|---|
| 1 | Imported | VALUE | |
| 2 | 120 | 120 | |
| 3 | 85 | 85 | |
| 4 | 240 | 240 | |
| 5 | 15 | 15 | |
| 6 | 0 | 460 |
A6 có tổng bằng 0 vì bốn ô ở cột A là văn bản (được gõ kèm dấu nháy đơn, như dữ liệu nhập từ tệp CSV hay trang web thường bị). Cột C chuyển từng ô và C6 cho tổng thật, 460.
Cách nhận biết số được lưu dưới dạng văn bản
Trong Excel, một số được lưu dưới dạng văn bản:
- nằm lệch về bên trái ô, trong khi số nằm lệch phải (trừ khi căn lề đã bị đổi);
- có một tam giác nhỏ màu xanh lá ở góc trên của ô, và một biểu tượng cảnh báo khi bạn chọn ô, ghi "Number Stored as Text";
- được COUNTA đếm nhưng COUNT thì không.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value | ISNUMBER | ISTEXT | Text numbers | |
| 2 | 120 | TRUE | FALSE | 2 | |
| 3 | 85 | FALSE | TRUE | ||
| 4 | 240 | TRUE | FALSE | ||
| 5 | 15 | FALSE | TRUE |
E2 đếm số ô trong vùng có dữ liệu nhưng không phải số: ở đây là hai ô được gõ kèm dấu nháy đơn. Với một cột số sạch, kết quả là 0.
Bốn công thức để chuyển đổi
| A | B | C | |
|---|---|---|---|
| 1 | Text | Result | How |
| 2 | 1250 | 1250 | VALUE |
| 3 | 1250 | double minus | |
| 4 | 1250 | multiply by 1 | |
| 5 | 1250 | add 0 |
Bất kỳ phép tính nào cũng buộc Excel đọc văn bản thành số, và VALUE là cách viết tường minh. -- (hai dấu trừ: trừ, rồi trừ thêm lần nữa) là lựa chọn phổ biến bên trong công thức khác, vì nó ngắn: =SUMPRODUCT(--A2:A5) cộng một cột số dạng văn bản mà không cần cột phụ. VALUE cũng đọc được văn bản có ký hiệu tiền tệ, dấu phân cách hàng nghìn hoặc dấu phần trăm: VALUE("$1,250") là 1250 và VALUE("12%") là 0.12.
| A | B | |
|---|---|---|
| 1 | Quantity (text) | Quantity |
| 2 | 125 |
Đến lượt bạn: Số lượng ở A2 được nhập vào dưới dạng văn bản. Ở B2, chuyển nó thành số.
Văn bản có đơn vị hoặc dấu phân cách khác
VALUE trả về #VALUE! khi văn bản có bất kỳ thứ gì nó không đọc được thành số. Hai trường hợp thường gặp:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Fixed | Without the fix |
| 2 | 120 kg | 120 | #VALUE! |
| 3 | 1.234,5 | 1234.5 | #VALUE! |
- Một đơn vị hoặc một từ: xóa nó trước bằng SUBSTITUTE, như ở B2.
- Dấu phẩy làm dấu thập phân, như
1.234,5từ hệ thống của Đức hay Brazil: NUMBERVALUE (Excel 2013 trở lên) nhận dấu thập phân và dấu phân cách nhóm làm đối số thứ hai và thứ ba. VALUE chỉ biết các dấu phân cách trong Excel của bạn, nên C3 bị lỗi.
Khoảng trắng trước hoặc sau chữ số không cản được VALUE, nhưng khoảng trắng không ngắt (non-breaking space) từ trang web thì có thể; SUBSTITUTE(A2,CHAR(160),"") xóa chúng trước. Về các nguyên nhân chung của lỗi này, xem #VALUE!.
| A | B | |
|---|---|---|
| 1 | Amount | Total |
| 2 | 120 | |
| 3 | 45 | |
| 4 | 80 |
Đến lượt bạn: Các số tiền trong A2:A4 là số được lưu dưới dạng văn bản. Ở B2, trả về tổng của chúng bằng một công thức.
Chuyển đổi tại chỗ không cần công thức
Công thức đặt các số vào một cột mới. Để sửa chính các ô đó:
- Convert to Number. Chọn các ô (ô được chọn đầu tiên phải là ô có tam giác xanh), bấm vào biểu tượng cảnh báo cạnh vùng chọn và chọn Convert to Number. Đây là cách sửa nhanh nhất.
- Text to Columns. Chọn cột, vào Data > Text to Columns và bấm Finish ngay. Excel nhập lại từng ô và biến các số dạng văn bản thành số.
- Paste Special, Multiply. Gõ 1 vào một ô trống và sao chép nó. Chọn các số dạng văn bản, vào Home > Paste > Paste Special, chọn Multiply, bấm OK.
Nếu các ô được định dạng Text (Home > Number Format hiện "Text"), hãy đổi sang General trước; nếu không, mọi thứ bạn gõ vào sẽ lại được lưu dưới dạng văn bản.
Lỗi thường gặp: tra cứu giữa văn bản và số
Giá trị tra cứu 101 không khớp với văn bản 101: VLOOKUP, XLOOKUP và MATCH trả về #N/A, và =A2=101 là FALSE, dù hai ô trông giống hệt nhau. Hãy chuyển một bên để cả hai cùng kiểu. Khi cột tra cứu là cột văn bản, hãy chuyển giá trị bạn cần tra:
=VLOOKUP(TEXT(E2,"0"), A2:C6, 3, FALSE) E2 is a number, column A holds text numbers
=VLOOKUP(--E2, A2:C6, 3, FALSE) E2 holds a text number, column A holds numbers
Trang ISNUMBER và ISTEXT hướng dẫn cách kiểm tra từng bên.
Câu hỏi thường gặp
Làm sao để chuyển văn bản thành số trong Excel?
Với công thức, dùng =VALUE(A2) hoặc =--A2. Không dùng công thức thì chọn các ô, bấm vào biểu tượng cảnh báo hiện bên cạnh và chọn Convert to Number.
Vì sao SUM trả về 0 trong Excel?
Các số đang được lưu dưới dạng văn bản, và SUM bỏ qua văn bản. Chúng thường nằm lệch trái và có một tam giác nhỏ màu xanh lá. Chuyển chúng bằng =VALUE(A2), hoặc cộng trực tiếp bằng =SUMPRODUCT(--A2:A10).
Vì sao Convert to Number không chạy hoặc không hiện ra?
Excel chỉ đưa ra lựa chọn này với văn bản mà nó đọc được thành số. Một khoảng trắng không ngắt từ trang web, một đơn vị như kg hay dấu phẩy thập phân mà Excel của bạn không dùng sẽ làm tam giác xanh không xuất hiện. Hãy làm sạch văn bản trước: =VALUE(SUBSTITUTE(A2,CHAR(160),"")) hoặc =NUMBERVALUE(A2,",",".").
Làm sao để chuyển các số dùng dấu phẩy thập phân?
Dùng NUMBERVALUE và chỉ rõ các dấu phân cách: =NUMBERVALUE(A2,",",".") biến 1.234,5 thành 1234.5. VALUE chỉ hiểu các dấu phân cách theo cài đặt Excel của chính bạn.
