Trong thế giới dữ liệu ngày nay, Excel luôn là công cụ không thể thiếu cho các nhà phân tích, nhà quản lý và bất kỳ ai cần xử lý dữ liệu nhanh chóng và chính xác. Một trong những thao tác thường gặp nhưng lại khá phức tạp là trích xuất số từ văn bản. Bất kể bạn đang làm việc với dữ liệu bán hàng, dữ liệu tài chính hay dữ liệu khảo sát, việc lấy ra các giá trị số từ chuỗi ký tự giúp bạn dễ dàng tính toán, thống kê và phân tích hơn. Bài viết này sẽ cung cấp một loạt các phương pháp, công thức, hàm và thậm chí là cách viết macro để bạn có thể tự tin xử lý mọi tình huống khi cần trích xuất số từ văn bản trong Excel.
Có thể bạn quan tâm: Hướng Dẫn Chi Tiết: Làm Thế Nào Để Tạo Biểu Đồ Stacked Bar Chart Trong Excel
1. Tại sao lại cần trích xuất số từ văn bản?
Trước khi đi vào chi tiết kỹ thuật, hãy xem xét một số tình huống thực tế:
- Dữ liệu bán hàng: Bạn có một cột “Mã sản phẩm” như
ABC-12345, trong đó12345là mã số cần dùng cho phân tích. Bạn muốn tách mã số này ra một cột riêng. - Dữ liệu tài chính: Một cột chứa giá trị như
$1,234.56hoặc1.234,56 €. Bạn muốn lấy số thực để tính tổng, bình quân. - Dữ liệu khảo sát: Thông tin phản hồi có dạng
Đánh giá: 4/5hoặcTuổi: 27 tuổi. Bạn cần trích xuất số tuổi hoặc điểm đánh giá. - Dữ liệu nhập khẩu: Cột chứa pubid như
Order #987654hoặcID: 54321. Bạn cần lấy số ID để tham chiếu tới hệ thống khác.
Khi trích xuất được số, bạn có thể dùng các hàm tính toán (SUM, AVERAGE, COUNTIF, v.v.) mà không cần phải lọc thủ công hay thay đổi dữ liệu gốc. Điều này giúp giảm thiểu lỗi, tăng tính chính xác và tiết kiệm thời gian.
Có thể bạn quan tâm: How To Create A Budget Spreadsheet In Excel: A Step-by-step Guide For Beginners And Advanced Users
2. Các công cụ cơ bản trong Excel để trích xuất số
Excel cung cấp nhiều hàm và tính năng hữu ích cho việc trích xuất số. Dưới đây là những công cụ thường dùng:
| Công cụ | Mô tả | Ví dụ |
|---|---|---|
| H productividad | Hàm MID, LEFT, RIGHT, FIND, SEARCH | =MID(A1, FIND("-", A1)+1, LEN(A1)) |
| Giá trị.il | Hàm VALUE, NUMBERVALUE | =VALUE(SUBSTITUTE(A1, "$", "")) |
| Bộ lọc | Hàm TEXTJOIN, FILTERXML | =FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//snumber(.)=number(.)") |
| Giải pháp nâng cao | Hàm LET, LAMBDA, SEQUENCE, BYROW | =LET(txt, A1, arr, TEXTSPLIT(txt, " "), FILTER(arr, ISNUMBER(--arr))) |
| VBA macro | Tự viết hàm tùy chỉnh | Function ExtractNumber(txt As String) As Double |
| Power Query | Trích xuất dữ liệu bằng truy vấn | = Table.TransformColumns(#"Previous Step",{{"Column1", each Text.Select(_, {"0".."9"}), type text}}) |
Trong phần tiếp theo, chúng ta sẽ đi sâu vào từng phương pháp, từ những công thức đơn giản nhất cho đến các giải pháp nâng cao, giúp bạn lựa chọn phương pháp phù hợp với từng trường hợp cụ thể.
Có thể bạn quan tâm: Cách Đếm Số Ô Trong Excel: Từ Cơ Bản Đến Nâng Passo
3. Phương pháp 1: Sử dụng công thức đơn giản
3.1 Trích xuất số từ chuỗi có dấu gạch ngang
Giả sử ô A1 chứa ABC-12345. Để lấy 12345, ta có thể dùng hàm MID kết hợp với FIND:
=MID(A1, FIND("-", A1) + 1, LEN(A1))
Giải thích: FIND("-", A1) tìm vị trí dấu gạch ngang, cộng 1 để bắt đầu lấy từ ký tự tiếp theo, và LEN(A1) cho spoil độ dài. Kết quả trả về là chuỗi số, nếu bạn muốn chuyển thành số thực, dùng VALUE:
=VALUE(MID(A1, FIND("-", A1) + 1, LEN(A1)))
3.2 Trích xuất số trong chuỗi có ký tự đặc biệt
Nếu dữ liệu có nhiều ký tự như ID: 54321;, ta có thể dùng SUBSTITUTE và TRIM:
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(A1, "ID:", ""), ";", "")))
3.3 Trích xuất số trong chuỗi có nhiều phần
Giả sử A1 = "Order #987654, Qty: 25". Bạn muốn lấy 987654 và 25. Sử dụng TEXTSPLIT (Excel 365) hoặc MID kết hợp SEARCH:
=TRIM(MID(A1, SEARCH("#", A1) + 1, SEARCH(",", A1) - SEARCH("#", A1) - 1))
Để lấy số thứ hai:
=TRIM(MID(A1, SEARCH("ാട:", A1) + 4, LEN(A1)))
Những công thức này yêu cầu bạn biết rõ cấu trúc chuỗi, vì vậy khi dữ liệu có nhiều biến eun, cần cẩn trọng.
4. Phương pháp 2: Dùng hàm TEXTSPLIT và FILTER (Excel 365)
4.1 Cách lấy tất cả các số trong một ô
Hàm TEXTSPLIT chia chuỗi thành mảng theo ký tự phân cách. Sau đó, FILTER lọc ra phần tử là số:
=LET( txt, A1, arr, TEXTSPLIT(txt, {"-", " ", ",", ";", ":"}), FILTER(arr, ISNUMBER(--arr))
)
Giải thích:
LETgiúp đặt biếntxtvàarrđể tránh viết lại nhiều lần.TEXTSPLIT(txt, {"-", " ", ",", ";", ":"})chia chuỗi bằng các ký tự đặc biệt.FILTER(arr, ISNUMBER(--arr))giữ lại những phần tử có thể chuyển rya--arrthành số (nếu không, trả về lỗi, doISNUMBERsẽ trả vềFALSE).
Kết quả trả về một mảng động (dynamic array) gồm các số. Nếu bạn muốn lấy số đầu tiên, dùng INDEX:
=INDEX(LET(txt, A1, arr, TEXTSPLIT(txt, {"-", " ", ",", ";", ":"}), FILTER(arr, ISNUMBER(--arr))), 1)
4.2 Trích xuất số trong chuỗi có định dạng phức tạp
Ví dụ A1 = "Total: $1,234.56; Tax: $123.45". Để lấy 1234.56 và 123.45:
=LET( txt, A1, nums, FILTER( TEXTSPLIT( SUBSTITUTE(SUBSTITUTE(txt, "$", ""), ",", ""), {" ", ";"}}, ISNUMBER(--TEXTSPLIT( SUBSTITUTE(SUBSTITUTE(txt, "$", ""), ",", ""), {" ", ";"})) ), nums
)
Kết quả là hai số đã được chuyển thành dạng số (định dạng số thực). Bạn có thể dùng TEXTJOIN để nối lại hoặc thao tác tiếp tục.
5. Phương pháp 3: Sử dụng hàm FILTERXML (Excel 2026 trở lên)
FILTERXML giúp trích xuất dữ liệu từ chuỗi XML, qua đó có thể lấy số từ văn bản.
5.1 Cách dùng FILTERXML để lấy số
Giả sử ô A1 beng AB12CD34EF56. Ta chuyển thành chuỗi XML:
=FILTERXML("<t><s>" & SUBSTITUTE(A1, "", "</s><s>") & "</s></t>", "//s. cast as xs:double?")
Trong thực tế, ta có thể dùng một cách thủ công hơn:
=FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, "$", ""), ",", ""), "</s></t>", "//s. cast as xs:double?")
Lưu ý: FILTERXML chỉ hoạt động với Excel 2026 trở lên và phải cài đặt tính năng XML.
5.2 Lợi страх
- Không cần vòng lặp hoặc macro.
- Đơn giản khi dữ liệu có định dạng cố định.
- Dễ dàng áp dụng cho nhiều ô cùng lúc.
Tuy nhiên, FILTERXML không hỗ trợ Excel Online và có thể gặp lỗi khi chuỗi quá dài.
6. Phương pháp 4: Giải pháp VBA tùy chỉnh
Khi dữ liệu quá phức tạp hoặc bạn muốn tự động hóa quy trình, viết một hàm macro trong VBA là giải pháp đáng tin cậy.
6.1 Hàm VBA để trích xuất số
Có thể bạn quan tâm: How To Delete All Blank Rows In Excel
Function ExtractNumbers(ByVal txt As String) As Double Dim i As Long, numStr As String Dim result As Double For i = 1 To Len(txt) If Mid(txt, i, 1) Like "0-9" Or Mid(txt, i, 1) = "." Then numStr = numStr & Mid(txt, i, 1) ElseIf Len(numStr) > 0 Then Exit For End If Next i If Len(numStr) > 0 Then result = CDbl(numStr) Else result = 0 End If ExtractNumbers = result
End Function
Sử dụng trong Excel:
=ExtractNumbers(A1)
Hàm này sẽ trả về số đầu tiên xuất hiện trong chuỗi, bỏ qua các ký tự khác. Bạn có thể mở rộng để lấy tất cả các số, lưu vào mảng hoặc thực hiện các phép tính khác.
6.2 Macro để trích xuất số tất cả ô trong cột
Sub ExtractAllNumbers() Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng If cell.Value <> "" Then cell.Offset(0, 1).Value = ExtractNumbers(cell.Value) End If Next cell
End Sub
Chạy macro này khi đã chọn cột chứa dữ liệu, hàm sẽնող tự động trích xuất số vào cột bên phải.
7. Phương pháp 5: Sử dụng Power Query
Power Query (Get & Transform) là công cụ mạnh mẽ để làm sạch dữ liệu. Khi muốn trích xuất số từ văn bản, bạn có thể dùng các bước sau:
- Chọn cột chứa dữ liệu cần làm sạch.
- Vào Transform > Replace Values: Thay các ký tự không cần thiết (ví dụ
$,,,;,:) bằng""(ô trống). - Transform > Split Column: Chia cột theo dấu phân cách (khoảng trắng allied) hoặc theo ký tự đặc biệt.
- Transform > Remove Errors: Loại bỏ các giá trị không phải số.
- Transform > Change Type: Chuyển đổi cột thành số (
Decimal Number).
Power Query cung cấp giao diện kéo thả, dễ iarraidh, thích hợp với người không quen công thức. Ngoài ra, bạn có thể lưu truy vấn và tự động làm mới khi dữ liệu thay đổi.
8. Các lưu ý khi trích xuất số
| Lưu ý | Mô tả | Ví dụ |
|---|---|---|
| Ký tự phân cách | Kiểm tra ký tự phân cách đa dạng (dấu cách, dấu phẩy, dấu chấm, dấu gạch ngang). | Order-1234; Qty: 50 |
| Định dạng số | Số thực có thể có dấu chấm hoặc dấu phẩy làm dấu thập phân. | 1,234.56 (định dạng Mỹ) vs 1.234,56 (định dạng châu Âu). |
| mismo | Khi có nhiều số trong cùng một chuỗi, quyết định lấy số nào (đầu, cuối, tất cả). | ABC-123-456 -> 123 Intensify or 456 |
| Xử lý lỗi | Các hàm có thể trả về #VALUE! nếu không tìm thấy số. | IFERROR(ExtractNumbers(A1),0) |
| Hiệu suất | Khi làm việc với hàng nghìn dòng, các công thức phức tạp có thể làm chậm Excel. | Sử dụng Power Query hoặc macro khi cần xử lý dữ liệu lớn. |
| Bảo mật | Macro và Power Query cần được bật; nếu không, các hàm không hoạt động. | Kiểm tra tùy chọn Trust Center trong Excel. |
| Định dạng hiển thị | Các số trích xuất có thể được định dạng lại (thập phân, phần trăm). | Format Cells > Number > 2 decimal places. |
9. Ví dụ thực tế: Tính tổng doanh thu từ dữ liệu bán hàng
Giả sử bạn có một bảng dữ liệu bán hàng với cột “Mô tả” chứa thông tin như:
| Mã sản phẩm | Mô tả | Giá bán |
|---|---|---|
| P001 | P001-ABC-12345 | |
| P002 | P002-XYZ-98765 | |
| P003 | P003-DEF-54321 |
Bạn muốn trích xuất số ID từ cột “Mô tả” và tính tổng doanh thu (giả sử giá bán là 1,000 đồng mỗi sản phẩm).
9.1 Trích xuất số ID
Sử dụng công thức:
=VALUE(MID(B2, FIND("-", B2)+1, LEN(B2)))
Kết quả:
| Mã sản phẩm | Mô tả | ID |
|---|---|---|
| P001 | P001-ABC-12345 | 12345 |
| P002 | P002-XYZ-98765 | 98765 |
| P003 | P003-DEF-54321 | 54321 |
9.2 Tính doanh thu
Giả sử cột D là Giá bán (đặt 1,000 đồng cho tất cả). Tính doanh thu:
=SUMPRODUCT(C2:C4, D2:D4)
Kết quả: 3 1,000 = 3,000 đồng.
Bạn có thể mở rộng bằng cách trích xuất số trong cột giá bán nếu dữ liệu có định dạng $1,000:
=VALUE(SUBSTITUTE(SUBSTITUTE(E2, "$", ""), ",", ""))
10. Tối ưu hóa công thức
Khi làm việc với(meta), các công thức phức tạp có thể làm chậm Excel. Dưới đây là một số mẹo:
10.1 Sử dụng LET để giảm tính toán lặp
=LET( txt, A1, pos, FIND("-", txt), num, MID(txt, pos + 1, LEN(txt)), VALUE(num)
)
10.2 Sử dụng hàm TEXTJOIN kết hợp FILTER
=TEXTJOIN(",", TRUE, FILTER(TEXTSPLIT(A1, {"-", " "}), ISNUMBER(--TEXTSPLIT(A1, {"-", " "}))))
10.3 Thay vì SEARCH và surrounded, dùng REGEXEXTRACT (Excel 365)
=REGEXEXTRACT(A1, "\d+")
Lưu ý:
REGEXEXTRACTchỉ tồn tại trong Excel 365 (có thể cần bật tính năng “Office Scripts”).
11. So sánh các phương pháp
| Phương pháp | Ưu điểm | Nhược điểm | Khi nào dùng |
|---|---|---|---|
| Công thức đơn giản (MID/FIND) | Dễ hiểu, không phụ thuộc phiên bản | Phụ thuộc vào cấu trúc chuỗi | Chuỗi có định dạng cố định |
| TEXTSPLIT + FILTER | Động, linh hoạt | Yêu cầu Excel 365 | Chuỗi có nhiều ký tự phân cách |
| FILTERXML | Không dùng VBA | Rất hạn chế, không hỗ trợ Excel Online | Chuỗi dạng.Mvc |
| VBA macro | Tùy chỉnh cao | Cần bật macro | Dữ liệu phức tạp, quy trình tự động |
| Power Query | Độc lập, mạnh mẽ | Cần học giao diện | Dữ liệu lớn, làm sạch dữ liệu |
| REGEXEXTRACT | Đơn giản, hỗ trợ regex | Cần Excel 365 | Tìm kiếm mẫu regex |
12. Kết luận
Việc trích xuất số từ văn bản trong Excel là một kỹ năng quan trọng, giúp bạn xử lý dữ liệu nhanh chóng, giảm thiểu lỗi và tăng hiệu quả công việc. Tùy thuộc vào độ phức tạp của dữ liệu, bạn có thể chọn từ:
- Công thức đơn giản khi dữ liệu có cấu trúc cố định.
- Hàm
TEXTSPLITvàFILTERkhi có nhiều ký tự phân cách. - Macro VBA cho các tác vụ tùy chỉnh cao.
- Power Query khi cần làm sạch dữ liệu lớn và tự động hóa.
Nhớ luôn kiểm tra kết quả và xử lý lỗi, đặc biệt khi dữ liệu không đồng nhất. Khi làm quen với các công cụ này, bạn sẽ nhanh chóng thành thạo và có thể áp dụng trong bất kỳ dự án dữ liệu nào.
Chúc bạn thành công trong việc trích xuất số và khai thác dữ liệu Excel một cách hiệu quả nhất!
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ẻ.