Có thể bạn quan tâm: Excel Countif Not Empty – Hướng Dẫn Chi Tiết Đếm Ô Không Rỗng Trong Bảng Tính
Mở Đầu
Trong môi trường làm việc hiện đại, Excel là công cụ không thể thiếu đối với các nhà quản lý, kế toán, nhân viên phân tích dữ liệu và cả những người làm việc văn phòng thông thường. Một trong những nhiệm vụ thường gặp nhất là trích xuất số từ các ô chứa dữ liệu hỗn hợp (text + số) hoặc các chuỗi ký tự phức tạp. Việc này không chỉ giúp chúng ta làm sạch dữ liệu, mà còn tạo điều kiện cho các phép tính, phân tích và báo cáo chính xác hơn.
Bài viết dưới đây sẽ cung cấp cho bạn hướng dẫn chi tiết về cách trích xuất số trong Excel, từ các công thức cơ bản, hàm nâng cao, cho tới cách sử dụng Power Query và VBA. Ngoài ra, chúng tôi sẽ chia sẻ các mẹo nhanh, lỗi thường gặp và cách xử lý chúng, giúp bạn trở thành “bậc thầy” trong việc làm việc với dữ liệu số trong Excel.
Có thể bạn quan tâm: Cách Đếm Ô Chứa Văn Bản Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành
1. Khi Nào Cần Trích Xuất Số?
- Dữ liệu nhập từ nguồn ngoài: Khi tải dữ liệu từ web, file CSV, hoặc hệ thống ERP, thường gặp các ô chứa cả chữ và số (ví dụ: “Invoice #12345”, “Giá: 1,250,000 VND”).
- Chuẩn hoá dữ liệu: Để thực hiện các phép tính, chúng ta cần tách số ra khỏi chuỗi ký tự.
- Tạo báo cáo tự động: Khi muốn tổng hợp, tính trung bình, hoặc so sánh các giá trị số trong một cột chứa dữ liệu hỗn hợp.
- Xây dựng mô hình dữ liệu: Trong Power Pivot hoặc Power Query, việc tách số giúp tạo các cột số thực (numeric) để thực hiện các phép tính phức tạp.
Có thể bạn quan tâm: Excel Conditional Formatting Formula If: Hướng Dẫn Chi Tiết Từ A Đến Z
2. Các Phương Pháp Cơ Bản Với Công Thức (Formulas)
2.1. Sử Dụng Hàm MID, LEFT, RIGHT Kết Hợp Với FIND/SEARCH
Đây là cách truyền thống, phù hợp khi số luôn nằm ở vị trí cố định hoặc có một ký tự phân tách (ví dụ: dấu “#”, “:”).
Ví dụ 1: Ô A2 chứa “Invoice #12345”.
=--MID(A2, FIND("#", A2) + 1, LEN(A2) - FIND("#", A2))
FIND("#", A2)trả về vị trí ký tự “#”.MIDlấy chuỗi con từ vị trí sau “#” tới hết.- Dấu
--(double unary) chuyển chuỗi thành số.
Ví dụ 2: Ô B2 chứa “Giá: 1,250,000 VND”.
=--SUBSTITUTE(TRIM(MID(B2, FIND(":", B2) + 1, LEN(B2))), " VND", "")
TRIMloại bỏ khoảng trắng thừa.SUBSTITUTEloại bỏ “ VND”.--chuyển thành số.
2.2. Hàm TEXTBEFORE và TEXTAFTER (Excel 365)
Excel 365 giới thiệu các hàm mới rất mạnh mẽ cho việc thao tác chuỗi.
- Lấy số sau ký tự “#”
=--TEXTAFTER(A2, "#")
- Lấy số trước ký tự “VND”
=--TEXTBEFORE(B2, " VND")
Các hàm này tự động trả về chuỗi, nên cần -- hoặc VALUE để chuyển sang số.
2.3. Hàm NUMBERVALUE
Khi chuỗi số có dấu phân cách thập phân và hàng nghìn khác nhau (ví dụ: “1.250.000,75”), NUMBERVALUE cho phép chỉ định ký tự phân cách.
=NUMBERVALUE("1.250.000,75", ",", ".")
- Tham số thứ 2 là dấu thập phân, tham số thứ 3 là dấu hàng nghìn.
2.4. Kết Hợp REGEXEXTRACT (Excel 365)
Excel 365 cũng hỗ trợ hàm REGEXEXTRACT để lấy số dựa trên biểu thức chính quy (regular expression).
=--REGEXEXTRACT(A2, "\d+")
\d+nghĩa là một hoặc nhiều chữ số liên tiếp.- Nếu ô chứa số âm hoặc số thập phân, có thể dùng
-?\d+(\.\d+)?.
Ví dụ: “Giá giảm: -250.75 USD”
=--REGEXEXTRACT(A2, "-?\d+(\.\d+)?")
2.5. Hàm SUMPRODUCT Kết Hợp MID Để Lấy Tất Cả Các Số
Khi một ô có nhiều số rải rác (ví dụ: “A12 B34 C56”), chúng ta có thể dùng công thức mảng.
=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW($1:$100), 1))ROW($1:$100), 0), ROW($1:$100))+1, 1)10^(ROW($1:$100)-1)/10)
Công thức này khá phức tạp, nhưng nó tách và cộng tất cả các số liên tiếp trong một chuỗi.
3. Sử Dụng Power Query Để Trích Xuất Số
Power Query (Get & Transform) là công cụ mạnh mẽ cho việc làm sạch dữ liệu. Khi bạn có hàng nghìn dòng dữ liệu, công thức có thể chậm và khó bảo trì, trong khi Power Query cho phép thao tác trực quan và tái sử dụng.
3.1. Các Bước Cơ Bản
- Chọn dữ liệu → Data → From Table/Range để tạo query.
- Trong Power Query Editor, chọn cột chứa chuỗi cần trích xuất.
- Vào Add Column → Extract → Digits (hoặc Extract → Text Between Delimiters).
- Kết quả sẽ là cột mới chứa các ký tự số.
- Đổi kiểu dữ liệu thành Whole Number hoặc Decimal Number.
- Close & Load để trả về Excel.
3.2. Sử Dụng M-Code (Custom Column)
Nếu cần tùy biến, bạn có thể viết công thức M:
= Table.AddColumn(#"Previous Step", "Number", each let txt = ColumnName, digits = Text.Select(txt, {"0".."9"}), result = if digits = "" then null else Number.FromText(digits) in result)
Text.Selectgiữ lại chỉ các ký tự từ 0‑9.Number.FromTextchuyển chuỗi thành số.
3.3. Xử Lý Số Nhiều Lần Xuất Hiện
Nếu một ô có nhiều số và bạn muốn tách thành các cột riêng (ví dụ: “12/34/56”), có thể dùng Split Column → By Delimiter hoặc Split Column → By Number of Characters. Đối với delimiter không cố định, sử dụng Split Column → By Non‑Digit to Digit (tùy biến bằng M code).
4. VBA – Giải Pháp Linh Hoạt Khi Công Thức Không Đủ
Có thể bạn quan tâm: Cách Chuyển Đổi Văn Bản Thành Ngày Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
VBA (Visual Basic for Applications) cho phép bạn viết macro để tự động hoá quy trình trích xuất số, đặc biệt khi:
- Dữ liệu có cấu trúc phức tạp.
- Cần thực hiện trên nhiều sheet hoặc workbook.
- Muốn tạo User Defined Function (UDF) để sử dụng như hàm trong worksheet.
4.1. UDF Đơn Giản: Lấy Số Đầu Tiên
Function ExtractFirstNumber(txt As String) As Double Dim RegEx As Object, Matches As Object Set RegEx = CreateObject("VBScript.RegExp") RegEx.Pattern = "-?\d+(\.\d+)?" RegEx.Global = False If RegEx.Test(txt) Then Set Matches = RegEx.Execute(txt) ExtractFirstNumber = CDbl(Matches(0).Value) Else ExtractFirstNumber = CVErr(xlErrNA) End If
End Function
- Sử dụng RegExp để tìm số đầu tiên.
-?cho phép số âm,(\.\d+)?cho số thập phân.
Cách dùng: =ExtractFirstNumber(A2)
4.2. UDF Lấy Tất Cả Các Số Thành Mảng (Excel 365)
Function ExtractAllNumbers(txt As String) As Variant Dim RegEx As Object, Matches As Object, i As Long, arr() Set RegEx = CreateObject("VBScript.RegExp") RegEx.Pattern = "-?\d+(\.\d+)?" RegEx.Global = True If RegEx.Test(txt) Then Set Matches = RegEx.Execute(txt) ReDim arr(1 To Matches.Count) For i = 1 To Matches.Count arr(i) = CDbl(Matches(i - 1).Value) Next i ExtractAllNumbers = Application.Transpose(arr) Else ExtractAllNumbers = CVErr(xlErrNA) End If
End Function
- Khi dùng trong Excel 365, hàm trả về spill range (mảng) và tự động mở rộng.
4.3. Macro Tự Động Trích Xuất Số Cho Toàn Bộ Cột
Sub ExtractNumbersColumn() Dim ws As Worksheet, rng As Range, cell As Range Set ws = ActiveSheet Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) For Each cell In rng cell.Offset(0, 1).Value = ExtractFirstNumber(cell.Value) 'Kết quả vào cột B Next cell
End Sub
- Đặt
ExtractFirstNumberở trên, chạy macro, kết quả sẽ xuất hiện ở cột B.
5. Xử Lý Các Trường Hợp Đặc Biệt
5.1. Số Với Dấu Phân Cách Ngày Tháng
Ví dụ: “Ngày 12/05/2026” → muốn lấy ngày, tháng, năm riêng.
- Ngày:
=DAY(DATEVALUE(TEXTAFTER(A2, "Ngày "))) - Tháng:
=MONTH(DATEVALUE(TEXTAFTER(A2, "Ngày "))) - Năm:
=YEAR(DATEVALUE(TEXTAFTER(A2, "Ngày ")))
5.2. Số Được Mã Hóa Bằng Chữ (One, Two, Three)
Nếu dữ liệu chứa số bằng chữ, cần một bảng ánh xạ:
| Text | Value |
|---|---|
| One | 1 |
| Two | 2 |
| Three | 3 |
Sử dụng VLOOKUP hoặc XLOOKUP để chuyển.
=XLOOKUP(TRIM(MID(A2, FIND(" ",A2)+1, 10)), $E$2:$F$10, $F$2:$F$10, "")
5.3. Số Được Mã Hóa Bằng Đơn Vị Tiền Tệ
Ví dụ: “$1,250.75” → cần loại bỏ ký hiệu tiền tệ và chuyển thành số.
=NUMBERVALUE(SUBSTITUTE(A2, "$", ""), ".", ",")
Nếu có nhiều ký hiệu (€, ¥), dùng SUBSTITUTE lồng nhau hoặc REGEXREPLACE (Excel 365):
=NUMBERVALUE(REGEXREPLACE(A2, "^\d.,-", ""), ",", ".")
5.4. Số Được Định Dạng Bằng Văn Bản (Text) Và Không Thể Nhận Diện
Đôi khi Excel lưu số dưới dạng text mà không nhận dạng (ví dụ: “00123”). Dùng:
=VALUE(A2) 'hoặc
=--A2
Nếu vẫn không chuyển, kiểm tra Trailing spaces và non‑breaking space (CHAR(160)). Dùng CLEAN và TRIM.
=VALUE(TRIM(CLEAN(A2)))
6. So Sánh Các Phương Pháp: Khi Nào Nên Dùng Gì?
| Phương Pháp | Ưu Điểm | Nhược Điểm | Khi Nào Chọn |
|---|---|---|---|
| Công Thức (MID, LEFT, RIGHT, FIND, TEXTAFTER, TEXTBEFORE) | Nhanh, không cần macro, phù hợp với dữ liệu nhỏ | Cần biết vị trí ký tự, không linh hoạt với dữ liệu đa dạng | Khi số luôn xuất hiện ở vị trí cố định hoặc có delimiter rõ ràng |
| REGEXEXTRACT (Excel 365) | Đọc hiểu cú pháp ngắn gọn, hỗ trợ pattern phức tạp | Chỉ có trong Office 365, không có trong các phiên bản cũ | Khi dữ liệu phức tạp, có nhiều dạng số, cần linh hoạt |
| Power Query | Xử lý hàng nghìn dòng nhanh, giao diện kéo thả, tái sử dụng | Cần học Power Query, không thể dùng trực tiếp trong công thức | Khi làm sạch dữ liệu lớn, cần tái sử dụng quy trình, hoặc muốn tự động hoá qua refresh |
| VBA/UDF | Tùy biến tối đa, có thể gói thành macro, chạy trên nhiều sheet | Cần viết code, bảo trì, có thể gây lỗi bảo mật nếu không đúng | Khi công thức không đáp ứng, muốn tạo hàm tùy chỉnh, hoặc tự động hoá quy trình phức tạp |
| NUMBERVALUE + SUBSTITUTE | Đơn giản, xử lý các định dạng tiền tệ/định dạng số quốc tế | Cần biết ký tự phân cách, không tự động nhận dạng | Khi dữ liệu chứa ký hiệu tiền tệ hoặc dấu phân cách khác nhau |
7. Thực Hành: 10 Bài Tập Thực Tế
| Bài Tập | Dữ Liệu Mẫu | Yêu Cầu | Công Thức / Phương Pháp |
|---|---|---|---|
| 1 | “Order #98765” | Lấy số 98765 | =--TEXTAFTER(A2,"#") |
| 2 | “Giá: 2.500.000 VND” | Lấy số 2500000 | =NUMBERVALUE(SUBSTITUTE(TEXTBEFORE(A2," VND"),".",""),".",",") |
| 3 | “12/05/2026” | Lấy ngày, tháng, năm | =DAY(DATEVALUE(A2)), =MONTH(DATEVALUE(A2)), =YEAR(DATEVALUE(A2)) |
| 4 | “A12 B34 C56” | Tổng các số (12+34+56) | =SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW($1:$100), 1))ROW($1:$100),0), ROW($1:$100))+1,1)10^(ROW($1:$100)-1)/10) |
| 5 | “-$1,200.50” | Lấy giá trị -1200.5 | =NUMBERVALUE(SUBSTITUTE(REGEXREPLACE(A2,"^\d\.,-",""),",",""),".",",") |
| 6 | “Invoice No: ABC-00123-X” | Lấy số 00123 (giữ leading zeros) | =TEXTAFTER(TEXTAFTER(A2,"-"),"-") (sau đó dùng TEXT để định dạng) |
| 7 | “Ngày 15/08/2026” | Lấy ngày 15 | =DAY(DATEVALUE(TEXTAFTER(A2,"Ngày "))) |
| 8 | “Weight: 75kg” | Lấy số 75 | =--TEXTBEFORE(TEXTAFTER(A2,":"),"kg") |
| 9 | “12abc34def56” | Tạo danh sách 12;34;56 trong cột mới | Power Query → Add Column → Extract → Digits → Split by each occurrence |
| 10 | “One hundred and twenty three” | Chuyển thành 123 (bằng bảng ánh xạ) | Tạo bảng mapping số chữ → dùng XLOOKUP kết hợp TEXTSPLIT |
8. 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! khi dùng VALUE | Chuỗi chứa ký tự không phải số (ví dụ: ký tự đặc biệt, khoảng trắng không chuẩn) | Dùng CLEAN + TRIM, hoặc SUBSTITUTE để loại bỏ ký tự không mong muốn. |
#NAME? khi dùng REGEXEXTRACT | Excel phiên bản không hỗ trợ hàm này | Nâng cấp lên Office 365 hoặc dùng VBA/Power Query. |
| Số bị làm tròn | Định dạng ô không đủ chữ số, hoặc dùng INT/ROUND vô tình | Kiểm tra định dạng ô, sử dụng VALUE hoặc NUMBERVALUE để giữ nguyên. |
| Không nhận dạng dấu phân cách | Dữ liệu có dấu “,” làm thập phân trong khi hệ thống dùng “.” | Dùng NUMBERVALUE(text, ",", ".") để chỉ định. |
| Trailing spaces/Non‑breaking space | Ký tự CHAR(160) không được TRIM loại bỏ | Dùng SUBSTITUTE(A2, CHAR(160), "") trước TRIM. |
| VBA macro không chạy | Macro bị chặn (Security), hoặc thiếu reference Microsoft VBScript Regular Expressions | Vào File → Options → Trust Center → Trust Center Settings → Macro Settings cho phép, hoặc dùng CreateObject("VBScript.RegExp") không cần reference. |
9. Tối Ưu Hóa Hiệu Suất Khi Xử Lý Hàng Ngàn Dòng
- Tránh dùng công thức mảng (array formulas) trên toàn cột nếu không cần, vì chúng làm chậm tính toán.
- Sử dụng bảng (Table) và structured references để giảm phạm vi tính toán.
- Power Query: Khi dữ liệu > 10.000 dòng, chuyển sang Power Query để làm sạch rồi tải lại.
- VBA: Khi phải xử lý trên toàn workbook, viết macro sử dụng
ScreenUpdating = FalsevàCalculation = xlCalculationManualđể tăng tốc.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'... code ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
10. Kết Luận
Việc trích xuất số trong Excel không chỉ là một thao tác kỹ thuật mà còn là nền tảng để làm sạch dữ liệu, tự động hoá quy trình và đảm bảo tính chính xác trong báo cáo. Từ những công thức đơn giản như MID, FIND đến các hàm hiện đại như TEXTAFTER, REGEXEXTRACT, từ Power Query cho đến VBA, bạn luôn có một công cụ phù hợp cho mọi tình huống.
Hãy nhớ:
– Xác định định dạng dữ liệu đầu vào (có delimiter, vị trí cố định, hay hỗn hợp).
– Chọn phương pháp phù hợp (công thức nhanh, Power Query cho dữ liệu lớn, VBA cho tùy biến).
– Kiểm tra lỗi và định dạng sau khi trích xuất để tránh sai lệch.
Bằng cách nắm vững các kỹ thuật trên, bạn sẽ tự tin xử lý mọi loại dữ liệu trong Excel, tiết kiệm thời gian và nâng cao chất lượng công việc. 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ẻ.