Bài viết này sẽ giới thiệu với các bạn cách chuyển đổi số được lưu trữ dưới dạng text thành number bằng cách sử dụng hàm VALUE hoặc một trong các tùy chọn: Error Checking, Paste Special trong Excel 2007, 2010, 2013, 2016, 2019 và 365.
Mục Lục Bài Viết
1, Cách Sử Dụng Hàm VALUE Chuyển Chữ Thành Số Trong Excel
1,1, Hàm VALUE Là Gì? Lưu Ý Khi Dùng Hàm VALUE Lấy Giá Trị Trong Excel
Hàm VALUE là hàm trả về giá trị số thông qua việc chuyển định dạng text sang số trong Excel, thường được kết hợp với các hàm cắt chuỗi ký tự như hàm MID, hàm LEFT, hàm RIGHT.
Cú pháp hàm: =VALUE(text)
Trong đó text là chuỗi giá trị số cần chuyển về định dạng number. Text có thể là chuỗi văn bản được đặt trong ngoặc kép, ví dụ VALUE(“100”) hoặc một tham chiếu đến một ô có chứa văn bản, ví dụ VALUE(A1) trong đó ô A1 chứa giá trị mà bạn muốn chuyển đổi.
Hàm chỉ lấy giá trị số trong Excel, do đó nếu chuỗi giá trị trong công thức (đối số text) không phải là 1 giá trị, hoặc định dạng số, ngày tháng, thời gian (những định dạng mà số sê-ri là số), hàm VALUE sẽ trả về lỗi #VALUE! như các ví dụ minh họa dưới đây:
Một số bạn sẽ thắc mắc lí do tại sao hoctin.vn sử dụng hàm VALUE trong khi có thể định dạng text thành number bằng cách vào tab Home >> chọn Number trong Format Cells:
Thực tế, trong một số trường hợp sau khi bạn nhập hoặc sao chép dữ liệu từ phần mềm hoặc các nguồn dữ liệu bên ngoài, giá trị số được lưu trữ trong các ô dưới định dạng văn bản có thể làm cho việc tính toán cho kết quả sai.
Các giá trị số được định dạng dưới dạng văn bản kiểu này sẽ được căn trái thay vì căn phải trong ô và thường được đánh dấu bằng một chỉ báo lỗi là hình tam giác màu xanh ở góc trên bên trái của ô tính.
Khi gặp trường hợp này, bạn có thể dễ dàng xử lý dữ liệu bị lỗi bằng cách sử dụng hàm VALUE vào cột phụ rồi copy giá trị vào bảng dữ liệu tính toán. Lưu ý: hàm VALUE chỉ chuyển text thành số khi các ô cần chuyển định dạng không chứa khoảng trắng nào bên trong.
Ví dụ dưới đây sử dụng hàm VALUE chuyển giá trị số bị lỗi định dạng từ text sang number trong Excel: cột áp dụng hàm này được căn lề phải tự động và chỉ báo lỗi hình tam giác màu xanh ở góc trên bên trái các ô tính đã biến mất.
1.2, Kết Hợp Hàm VALUE Và Các Hàm Lấy Ký Tự Để Chuyển Từ Text Sang Number Trong Excel
Khi sử dụng các hàm lấy ký tự (LEFT, RIGHT, MID…) để cắt giá trị số từ các chuỗi văn bản bất kỳ, định dạng của các giá trị số này ở dạng text, không phải là định dạng số nên cần sử dụng kết hợp hàm VALUE để chuyển về dạng số.
Ví dụ dưới đây tách số thứ tự của sinh viên trong danh sách lớp từ mã sinh viên. Chi tiết cách cắt chuỗi văn bản từ vị trí tùy chọn đã được hướng dẫn trong bài viết cách sử dụng các hàm lấy ký tự bất kỳ trong Excel.
Có thể thấy hàm RIGHT đã cắt 3 chữ số là số thứ tự của sinh viên từ mã sinh viên, tuy nhiên các kết quả của hàm này cột C có định dạng text nên có dạng “001”,…, “031”.
Để lấy số thứ tự của sinh viên trong danh sách lớp dưới dạng số, ví dụ số 1, 31 chứ không phải 001, 031, hoctin.vn kết hợp hàm RIGHT và hàm VALUE bằng công thức:
=VALUE (RIGHT(B3,3))
Kết quả lấy giá trị số trong Excel:
2, Sử Dụng Error Checking Chuyển Từ Text Sang Number Trong Excel
Khi gặp số có định dạng text được căn trái mặc định và được đánh dấu bằng chỉ báo lỗi, bạn có thể sử dụng tính năng Error Checking bằng cách bấm chọn dấu chấm than nằm kế bên ô tính >> chọn Convert to Number.
Lưu ý:
-
Để định dạng nhanh cho 1 dải ô gặp lỗi định dạng như trên, chọn ô có chỉ báo lỗi để hiện biểu tượng Error Checking, sau đó chọn toàn bộ vùng dữ liệu/ cột/ hàng bằng 1 trong 3 phím tắt Ctrl + A, Ctrl + Space, Shift + Space (dấu cách).
Việc chọn ô tính có chỉ báo lỗi trước khi mở rộng chọn nhiều ô tính khác để đảm bảo khi chọn nhiều vùng dữ liệu, biểu tượng- Error Checking vẫn hiện ra. Tiếp tục chọn Convert to Number để chuyển text thành number trong Excel.
- Bỏ qua cảnh báo lỗi của Excel: Nếu bạn không muốn chỉ báo lỗi là tam giác màu xanh xuất hiện ở góc trên cùng bên trái ô tính, chọn Ignore Error.
3, Sử Dụng Paste Special Khi Copy Để Chuyển Định Dạng Số Trong Excel
Hoctin.vn đã có bài viết về các cách copy trong Excel, trong đó có cách copy value trong Excel để chỉ sao chép giá trị.
Tuy nhiên, trong trường hợp giá trị số đã gặp lỗi định dạng text do dữ liệu được nhập từ phần mềm khác, thì việc copy giá trị bằng paste value không giúp dữ liệu đã sao chép được tự động về đúng định dạng number.
Chỉ cần một chút thay đổi so thao tác copy giá trị thông thường, bạn sẽ chuyển các giá trị số này về đúng định dạng:
Chọn vùng dữ liệu cần copy, sử dụng phím tắt Ctrl + C như khi sao chép thông thường
Sử dụng tổ hợp phím Ctrl + Alt + V để mở hộp thoại Paste Special >> chọn Add và ấn OK.
Kết quả sử dụng Paste Special để sao chép và chuyển sang giá trị số trong Excel:
Trên đây là các cách sử dụng hàm VALUE và các tính năng Error Checking, Paste Special trong Excel để chuyển định dạng text thành số. Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích.
Ngoài ra, để việc xử lý, định dạng dữ liệu (ví dụ việc lọc/ sắp xếp dữ liệu, chuyển chữ thường thành chữ hoa, định dạng ngày tháng, gộp ô trong Excel, …) đơn giản hơn, bạn có thể tham khảo bài viết:
Cách lọc và sắp xếp dữ liệu trong Excel
Các Cách Chuyển Chữ Thường Thành Chữ Hoa Trong Excel Và Ngược Lại