Có thể bạn quan tâm: Đổi Số Thành Chữ Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Mở đầu: Tại sao lại cần chuyển đổi sang Number?
Trong quá trình làm việc với Excel, chúng ta thường gặp các tình huống dữ liệu được nhập dưới dạng text (chuỗi ký tự) dù thực chất chúng là các giá trị số. Điều này có thể gây ra nhiều vấn đề:
- Công thức không hoạt động: Các hàm tính toán như
SUM,AVERAGE,COUNTIFsẽ bỏ qua các ô chứa số dạng text. - Sắp xếp và lọc sai: Khi sắp xếp, Excel sẽ coi các ô text như là chuỗi, dẫn tới thứ tự không mong muốn.
- Định dạng hiển thị: Một số không hiển thị đúng định dạng (ví dụ: ngày tháng, tiền tệ) nếu chưa được chuyển sang kiểu Number.
- Tốc độ tính toán chậm: Khi dữ liệu phải được chuyển đổi liên tục trong các công thức, hiệu năng sẽ giảm.
Vì vậy, việc convert to number (chuyển đổi sang kiểu số) là một kỹ năng căn bản nhưng vô cùng quan trọng để làm việc hiệu quả trong Excel.
Có thể bạn quan tâm: Chia Đôi Ô Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Kiểm tra kiểu dữ liệu hiện tại
Trước khi thực hiện chuyển đổi, chúng ta cần xác định ô hoặc vùng dữ liệu đang ở dạng nào. Các cách kiểm tra nhanh:
| Phương pháp | Mô tả |
|---|---|
Kiểm tra dấu cộng (+) ở phía trái ô | Nếu ô hiển thị ‘123 (dấu nháy đơn), đó là text. |
Sử dụng hàm ISTEXT | =ISTEXT(A1) trả về TRUE nếu A1 là text. |
Sử dụng TYPE | =TYPE(A1) trả về 2 cho text, 1 cho number. |
| Kiểm tra định dạng | Home → Number → Number Format. Nếu hiển thị “General” nhưng giá trị vẫn không tính, có thể là text. |
Có thể bạn quan tâm: Bài Tập Thực Hành Excel: Cẩm Nang Toàn Diện Cho Người Mới Bắt Đầu Và Người Dày Dặn Kinh Nghiệm
2. Các phương pháp chuyển đổi cơ bản
2.1. Dùng tính năng “Text to Columns”
- Chọn vùng dữ liệu cần chuyển đổi.
- Vào Data → Text to Columns.
- Chọn Delimited → Next → Next (không chọn bất kỳ delimiter nào).
- Ở phần Column data format, chọn General.
- Nhấn Finish.
Lưu ý: Phương pháp này rất nhanh, phù hợp khi dữ liệu không có ký tự đặc biệt hoặc dấu phẩy thập phân.
2.2. Dùng công cụ “Error Checking”
Nếu Excel nhận ra các ô là text, thường xuất hiện một dấu chấm than màu xanh lá cây ở góc trên bên trái ô.
- Click vào ô có dấu chấm than.
- Chọn Convert to Number từ menu xổ xuống.
Phương pháp này chỉ áp dụng cho các ô được Excel tự động nhận diện là “Number stored as text”.
2.3. Nhân với 1 hoặc cộng 0
Công thức đơn giản:
=A11=A1+0
Sau khi nhập công thức, kéo công thức xuống hết vùng. Khi sao chép giá trị (Copy → Paste Values), các ô sẽ trở thành Number.
2.4. Dùng hàm VALUE
Hàm VALUE chuyển một chuỗi ký tự thành số:
=VALUE(A1)
Nếu ô A1 chứa "123,45" (với dấu phẩy là dấu thập phân tùy ngôn ngữ), VALUE sẽ trả về 123,45.
2.5. Sử dụng Paste Special → Add
- Nhập số 0 vào một ô trống.
- Sao chép ô chứa 0 (
Ctrl + C). - Chọn vùng dữ liệu cần chuyển đổi.
- Click Home → Paste → Paste Special → Add → OK.
Kết quả: Excel sẽ cộng 0 vào mỗi ô, buộc chúng chuyển sang kiểu Number.
3. Xử lý các trường hợp phức tạp
3.1. Dữ liệu có ký tự không mong muốn
Ví dụ: "123,456 USD" hoặc "=123".
Bước 1: Loại bỏ ký tự không cần thiết bằng hàm SUBSTITUTE hoặc TEXTJOIN.
=SUBSTITUTE(A1," USD","")
=SUBSTITUTE(A1,"=","")
Bước 2: Đảm bảo dấu thập phân và dấu phân cách hàng nghìn đúng với thiết lập ngôn ngữ.
- Nếu dữ liệu dùng dấu chấm làm phân cách thập phân (
"123.45"), nhưng hệ thống của bạn dùng dấu phẩy, cần thay đổi:
=SUBSTITUTE(A1,".",",")
Sau khi làm sạch, áp dụng VALUE hoặc nhân 1 để chuyển.
3.2. Dữ liệu ngày tháng được nhập dưới dạng text
Ví dụ: "01/02/2026" (text) vs ngày thực tế.
Cách 1: Dùng hàm DATEVALUE:
=DATEVALUE(A1)
Sau đó định dạng ô thành Date.
Cách 2: Nếu ngày tháng theo định dạng dd-mm-yyyy nhưng hệ thống đang dùng mm-dd-yyyy, cần dùng TEXT để tái cấu trúc:
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))
3.3. Dữ liệu số lớn vượt quá giới hạn 15 chữ số
Excel chỉ lưu trữ 15 chữ số đáng tin cậy. Nếu bạn có số thẻ tín dụng hay mã số dài hơn, chuyển sang Text là an toàn. Tuy nhiên, nếu muốn tính toán một phần (ví dụ: lấy 2 chữ số cuối), có thể dùng hàm RIGHT trước khi chuyển:
=VALUE(RIGHT(A1,2))
3.4. Dữ liệu chứa ký tự Unicode (tiếng Việt)
Khi dữ liệu có dấu tiếng Việt và được nhập dưới dạng Text, việc chuyển sang Number không ảnh hưởng tới ký tự. Tuy nhiên, nếu có ký tự đặc biệt (ví dụ: dấu chấm “.” trong số thập phân) và ngôn ngữ hệ thống là Việt Nam (dấu phẩy là thập phân), cần thay thế:
Có thể bạn quan tâm: Cách Chuyển Đổi File Excel Xls Sang Xlsx Nhanh Chóng, An Toàn Và Hiệu Quả
=VALUE(SUBSTITUTE(A1,".",","))
4. Tự động hoá quy trình với VBA
Khi phải xử lý hàng nghìn ô, việc làm thủ công sẽ mất thời gian. Dưới đây là một macro VBA phổ biến để convert to number cho toàn bộ vùng đã chọn:
Sub ConvertToNumber() Dim rng As Range Dim cell As Range On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeConstants, xlTextValues) If rng Is Nothing Then MsgBox "Không có ô text nào trong vùng chọn.", vbInformation Exit Sub End If Application.ScreenUpdating = False For Each cell In rng If IsNumeric(cell.Value) Then cell.Value = cell.Value 1 'hay cell.Value = CDbl(cell.Value) End If Next cell Application.ScreenUpdating = True MsgBox "Đã chuyển đổi thành công!", vbInformation
End Sub
Cách sử dụng:
- Nhấn
Alt + F11→Insert → Module. - Dán mã trên vào cửa sổ module.
- Đóng VBA editor.
- Chọn vùng dữ liệu →
Alt + F8→ ChọnConvertToNumber→ Run.
4.1. Macro nâng cao: Xử lý ký tự đặc biệt
Nếu dữ liệu có tiền tố $, €, hoặc hậu tố USD, VND, chúng ta có thể mở rộng macro:
Sub ConvertWithClean() Dim rng As Range, cell As Range, txt As String Set rng = Selection.SpecialCells(xlCellTypeConstants, xlTextValues) Application.ScreenUpdating = False For Each cell In rng txt = cell.Value txt = Replace(txt, "$", "") txt = Replace(txt, "€", "") txt = Replace(txt, "USD", "") txt = Replace(txt, "VND", "") txt = Trim(txt) If IsNumeric(txt) Then cell.Value = CDbl(txt) End If Next cell Application.ScreenUpdating = True MsgBox "Hoàn tất chuyển đổi và làm sạch dữ liệu.", vbInformation
End Sub
4.2. Tự động chạy khi mở workbook
Nếu bạn muốn mọi dữ liệu nhập vào luôn ở dạng Number, có thể đặt macro vào Workbook_Open:
Private Sub Workbook_Open() Call ConvertToNumber
End Sub
5. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #VALUE! | Dữ liệu không phải số, hoặc chứa ký tự không thể chuyển | Kiểm tra và làm sạch dữ liệu bằng SUBSTITUTE, TRIM. |
| Số không thay đổi sau Convert to Number | Dữ liệu thực sự đã là Number nhưng hiển thị dưới dạng Text vì định dạng | Đổi định dạng ô sang Number hoặc General. |
| Mất dữ liệu thập phân | Dấu thập phân không phù hợp với thiết lập ngôn ngữ (., vs ,) | Thay đổi dấu bằng SUBSTITUTE hoặc thay đổi Regional Settings trong Windows. |
| Công thức trả về 0 | Dữ liệu chứa khoảng trắng hoặc ký tự không nhìn thấy | Dùng TRIM để loại bỏ khoảng trắng. |
| Không thể copy/paste | Worksheet bảo vệ hoặc dữ liệu trong bảng pivot | Bỏ bảo vệ sheet hoặc tạm thời tắt chế độ bảo vệ. |
6. Tối ưu hoá hiệu suất khi làm việc với dữ liệu lớn
- Tắt tính toán tự động:
Formulas → Calculation Options → Manual. Sau khi chuyển đổi, bật lạiAutomatic. - Sử dụng
Paste Special → Values: Giảm số lần tính toán lại. - Sử dụng Power Query: Khi dữ liệu đến từ nguồn ngoài (CSV, DB), Power Query cho phép change type ngay trong quá trình nhập, giảm công việc sau này.
- Lưu file ở định dạng
.xlsx(không.xls), vì.xlsxhỗ trợ các tính năng mới và tốc độ nhanh hơn.
7. Power Query: Convert to Number trong môi trường ETL
Power Query (Get & Transform) là công cụ mạnh mẽ để xử lý dữ liệu trước khi đưa vào bảng tính.
7.1. Bước cơ bản
- Data → Get Data → From File → From Workbook (hoặc CSV, Text, Database…).
- Khi dữ liệu được tải vào Power Query Editor, chọn cột cần chuyển.
- Click chuột phải → Change Type → Decimal Number (hoặc Whole Number).
- Nếu có lỗi (error), nhấn vào biểu tượng Error → Replace Errors → nhập giá trị mặc định (ví dụ:
0).
7.2. Xử lý ký tự đặc biệt trong Power Query
Sử dụng Replace Values:
- Chọn cột → Transform → Replace Values.
- Thay thế
$bằng rỗng,USDbằng rỗng, v.v. - Sau khi làm sạch, thực hiện Change Type.
7.3. Lưu lại và tải dữ liệu
- Click Close & Load → Dữ liệu sẽ xuất hiện trong Excel dưới dạng Table. Các giá trị đã là Number và sẵn sàng tính toán.
Power Query giúp giảm thiểu việc phải viết macro hay công thức phức tạp, đồng thời giữ lại lịch sử chuyển đổi (step-by-step) để chỉnh sửa dễ dàng.
8. Thực hành: 5 bài tập thực tế
Bài 1: Chuyển đổi cột “Giá bán” (text) sang Number và tính tổng
- Chọn cột
Giá bán. - Dùng
Data → Text to Columns → General. - Đặt công thức
=SUM(B2:B1000).
Bài 2: Làm sạch cột “Số tiền” chứa ký tự $ và , (phân cách hàng nghìn)
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""))
Sau đó copy-paste values và định dạng Currency.
Bài 3: Chuyển ngày tháng dạng “dd/mm/yyyy” (text) sang Date
=DATEVALUE(A2)
Sau đó Format → Date → dd/mm/yyyy.
Bài 4: Sử dụng macro để chuyển toàn bộ sheet
Sub ConvertAllSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Value = _ ws.Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Value 1 Next ws MsgBox "Đã chuyển đổi toàn bộ sheet!", vbInformation
End Sub
Bài 5: Power Query – Nhập CSV chứa số dạng “1.234,56” (dấu chấm nghìn, dấu phẩy thập phân)
- Khi import, chọn Locale → German (Germany) (hoặc bất kỳ locale nào dùng dấu chấm làm nghìn, dấu phẩy thập phân).
- Power Query sẽ tự động nhận dạng cột là Decimal Number.
9. Kết luận
Việc convert to number trong Excel không chỉ là một thao tác đơn giản mà còn là nền tảng để xây dựng các báo cáo, phân tích dữ liệu chính xác và hiệu quả. Từ các phương pháp nhanh như “Error Checking” hay “Text to Columns”, đến các kỹ thuật nâng cao với VBA và Power Query, mỗi công cụ đều có ưu điểm riêng tùy vào quy mô và tính chất dữ liệu.
Những điểm quan trọng cần ghi nhớ:
- Luôn kiểm tra kiểu dữ liệu trước khi chuyển đổi (
ISTEXT,TYPE). - Làm sạch dữ liệu (loại bỏ ký tự không mong muốn) trước khi dùng
VALUEhoặc nhân 1. - Khi làm việc với dữ liệu lớn, tạm thời tắt tính toán tự động và sử dụng Power Query hoặc VBA để tối ưu hiệu suất.
- Đừng quên định dạng lại sau khi chuyển (Number, Currency, Date) để dữ liệu hiển thị đúng và dễ đọc.
Bằng cách nắm vững các kỹ thuật trên, bạn sẽ tự tin xử lý mọi tình huống “số được lưu dưới dạng text” trong Excel, từ đó nâng cao độ chính xác của các phép tính và giảm thiểu lỗi trong công việc hàng ngày. Chúc bạn thành công!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.