Có thể bạn quan tâm: Text To Column Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Mở Đầu
Trong công việc văn phòng, đặc biệt là khi xử lý dữ liệu nhân sự, khách hàng hay học sinh, việc tách năm sinh từ một chuỗi ngày tháng là một nhu cầu thường gặp. Excel, với khả năng xử lý dữ liệu mạnh mẽ, cung cấp nhiều công cụ và hàm giúp thực hiện công việc này một cách nhanh chóng và chính xác. Bài viết này sẽ hướng dẫn bạn cách tách năm sinh trong Excel từ những tình huống thực tế, bao gồm:
- Tách năm sinh từ ngày tháng dạng chuẩn (dd/mm/yyyy, mm/dd/yyyy, yyyy-mm-dd…).
- Tách năm sinh từ chuỗi ký tự không đồng nhất (ví dụ: “Sinh năm 1995”, “1995-05-12”, “12/05/1995”).
- Sử dụng các hàm Excel cơ bản (YEAR, TEXT, LEFT, RIGHT, MID, FIND, SEARCH, DATEVALUE…).
- Áp dụng công thức mảng và hàm mới (FILTER, LET, LAMBDA) cho phiên bản Excel 365/2026.
- Kỹ thuật trích xuất năm sinh bằng Power Query (Get & Transform Data).
- Xử lý lỗi và làm sạch dữ liệu (trường hợp ngày tháng không hợp lệ, dữ liệu thiếu hoặc dạng văn bản lẫn số).
- Tự động hóa bằng VBA (Macro) cho các bảng dữ liệu lớn.
Sau khi đọc xong bài viết, bạn sẽ nắm vững mọi cách tiếp cận để tách năm sinh trong Excel, từ cách đơn giản nhất dành cho người mới bắt đầu cho tới các giải pháp nâng cao cho chuyên gia dữ liệu.
Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Repair Corrupted Excel File Online: Giải Pháp Nhanh Chóng Và Hiệu Quả
1. Hiểu Rõ Định Dạng Ngày Tháng Trong Excel
1.1. Excel lưu trữ ngày tháng như thế nào?
Excel không lưu trữ ngày tháng dưới dạng chuỗi ký tự mà lưu dưới dạng số serial (số nguyên). Số serial này đại diện cho số ngày tính từ ngày 1/1/1900 (đối với Windows) hoặc 1/1/1904 (đối với Mac). Ví dụ:
- Ngày 1/1/1900 → Serial 1
- Ngày 31/12/2026 → Serial 45204
Nếu ô chứa ngày tháng được định dạng là Text, Excel sẽ coi đó là chuỗi ký tự và không thực hiện các phép tính ngày tháng.
1.2. Kiểm tra định dạng ô
- Ctrl+1 → Mở hộp thoại Format Cells → Tab Number → Date hoặc Custom.
- Nếu ô được định dạng là Text, bạn cần chuyển đổi sang kiểu Date hoặc General trước khi áp dụng hàm.
Có thể bạn quan tâm: Phần Mềm Kế Toán Excel: Giải Pháp Hiệu Quả Cho Doanh Nghiệp Nhỏ Và Vừa
2. Tách Năm Sinh Khi Dữ Liệu Đã Là Ngày Tháng Đúng Định Dạng
2.1. Sử dụng hàm YEAR
Đây là cách nhanh nhất và chính xác nhất nếu ô chứa ngày tháng đã được Excel nhận dạng.
Công thức:
=YEAR(A2)
- A2: ô chứa ngày tháng (ví dụ: 12/05/1995).
- Kết quả trả về:
1995.
2.2. Khi ngày tháng được nhập dưới dạng chuỗi (Text)
Nếu ô chứa ngày tháng là chuỗi, bạn cần chuyển đổi sang ngày thực (Date) bằng hàm DATEVALUE hoặc TEXT.
2.2.1. Sử dụng DATEVALUE
=YEAR(DATEVALUE(A2))
- Lưu ý: DATEVALUE chỉ làm việc với các định dạng ngày tháng chuẩn mà hệ thống ngôn ngữ của Excel nhận diện (ví dụ: dd/mm/yyyy hoặc mm/dd/yyyy tùy vào cài đặt regional).
2.2.2. Khi định dạng không chuẩn (yyyy-mm-dd, dd.mm.yyyy…)
Bạn có thể dùng SUBSTITUTE để thay dấu phân cách, rồi dùng DATEVALUE.
=YEAR(DATEVALUE(SUBSTITUTE(A2,"-","/")))
Hoặc:
=YEAR(DATEVALUE(SUBSTITUTE(A2,".","/")))
2.3. Khi ngày tháng là số serial (đã là kiểu Date) nhưng hiển thị dưới dạng Text
Sử dụng hàm TEXT để ép kiểu:
=YEAR(TEXT(A2,"dd/mm/yyyy"))
3. Tách Năm Sinh Từ Chuỗi Văn Bản Không Đồng Nhất
Trong thực tế, dữ liệu thường không sạch sẽ. Dưới đây là các kịch bản phổ biến và cách xử lý.
3.1. Chuỗi có chứa năm ở dạng 4 chữ số (1990, 2005…)
3.1.1. Sử dụng hàm MID + SEARCH (hoặc FIND)
Giả sử ô A2 chứa: "Sinh năm 1995".
=VALUE(MID(A2, SEARCH("199",A2),4))
- SEARCH(“199”,A2): Tìm vị trí bắt đầu của “199”. Nếu năm có thể là 2000–2099, ta dùng “20” hoặc “19”.
- MID: Lấy 4 ký tự bắt đầu từ vị trí tìm được.
- VALUE: Ép chuỗi thành số.
3.1.2. Sử dụng hàm REGEXEXTRACT (Excel 365)
Excel 365 đã tích hợp hàm TEXTSPLIT, TEXTAFTER, nhưng chưa có hàm regex riêng. Tuy nhiên, bạn có thể dùng LET + FILTERXML để mô phỏng:
=LET( txt, A2, xml, "<t><s>" & SUBSTITUTE(txt," ","</s><s>") & "</s></t>", arr, FILTERXML(xml,"//snumber(.)=number(.)"), MAX(arr) // trả về giá trị năm lớn nhất trong chuỗi
)
Công thức trên tách các “token” (từ) trong chuỗi, lọc ra những token là số, và trả về số lớn nhất (giả sử là năm sinh).
3.2. Chuỗi có dạng ngày tháng đầy đủ (dd/mm/yyyy, mm-dd-yyyy…)
3.2.1. Sử dụng hàm DATEVALUE + YEAR
=YEAR(DATEVALUE(A2))
Nếu ngày tháng dùng dấu / hoặc -, Excel thường nhận ra.
3.2.2. Khi dấu phân cách là . hoặc không đồng nhất
=YEAR(DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2,".","/"),"-","/")))
3.3. Nhiều ngày trong một ô, cần lấy năm của ngày đầu tiên
Ví dụ: "01/01/1990; 15/06/1995; 20/12/2000".
=YEAR(DATEVALUE(TRIM(LEFT(A2, FIND(";",A2&";")-1))))
- FIND(“;”,A2&”;”): Tìm vị trí dấu
;đầu tiên (thêm&";"để tránh lỗi nếu không có dấu;). - LEFT: Lấy phần trước dấu
;. - TRIM: Loại khoảng trắng dư thừa.
- DATEVALUE + YEAR: Chuyển sang ngày và lấy năm.
3.4. Khi năm sinh nằm ở vị trí cố định (ví dụ: ký tự 1-4)
Nếu dữ liệu luôn có năm ở vị trí 1-4, dùng LEFT:
=VALUE(LEFT(A2,4))
Nếu năm ở vị trí cuối cùng (độ dài không cố định), dùng RIGHT:
=VALUE(RIGHT(A2,4))
4. Áp Dụng Công Thức Mảng (Dynamic Array) Trong Excel 365
Excel 365 cho phép công thức trả về mảng động, giúp xử lý nhiều trường hợp phức tạp hơn.
4.1. Lấy tất cả các năm trong một cột
Có thể bạn quan tâm: Thêm Sheet Mới Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Giả sử cột A chứa các chuỗi không đồng nhất, muốn tạo một cột B chỉ hiển thị năm sinh.
=LET( txt, A2:A1000, arr, FILTERXML("<t><s>"&SUBSTITUTE(txt," ","</s><s>")&"</s></t>","//snumber(.)=number(.)"), years, FILTER(arr, (arr>=1900)(arr<=YEAR(TODAY())) ), IFERROR(INDEX(years, SEQUENCE(ROWS(txt))),"")
)
- FILTERXML: Tách các token và lấy những token là số.
- FILTER: Chỉ giữ lại các số trong khoảng hợp lý (1900‑năm hiện tại).
- INDEX + SEQUENCE: Đặt kết quả vào cột tương ứng.
4.2. Hàm LAMBDA Tùy Chỉnh
Bạn có thể tạo hàm tùy chỉnh để tái sử dụng:
=LET( ExtractYear, LAMBDA(txt, LET( cleaned, SUBSTITUTE(SUBSTITUTE(txt,"-","/"),".","/"), num, IFERROR(YEAR(DATEVALUE(cleaned)), VALUE(MID(txt, SEARCH("19",txt),4) & IFERROR(MID(txt, SEARCH("20",txt),4),"")) ), IF(ISNUMBER(num), num, "") ) ), ExtractYear(A2)
)
Sau khi định nghĩa hàm ExtractYear, bạn có thể gọi nó cho bất kỳ ô nào: =ExtractYear(A2).
5. Sử Dụng Power Query Để Tách Năm Sinh
Power Query (Get & Transform) là công cụ mạnh mẽ để làm sạch dữ liệu trước khi đưa vào bảng tính.
5.1. Bước 1: Nhập Dữ Liệu Vào Power Query
- Chọn bảng dữ liệu → Data → From Table/Range.
- Power Query Editor mở ra.
5.2. Bước 2: Tách Năm
5.2.1. Khi cột là kiểu Date
- Chọn cột ngày tháng → Add Column → Date → Year → Year.
- Cột mới sẽ chứa năm.
5.2.2. Khi cột là Text chứa ngày hoặc năm
- Chọn cột → Add Column → Custom Column.
- Nhập công thức:
= try Date.Year(Date.FromText(Column1)) otherwise try Number.FromText(Text.Middle(Column1, Text.PositionOf(Column1,"19"),4)) otherwise null
- try … otherwise: Xử lý lỗi nếu chuyển đổi không thành công.
- Date.FromText: Chuyển chuỗi thành ngày.
- Date.Year: Lấy năm.
- Nếu không phải ngày, dùng Text.Middle để lấy 4 ký tự bắt đầu từ “19” hoặc “20”.
5.3. Bước 3: Đóng & Tải Lại
- Home → Close & Load → Kết quả sẽ là một bảng mới trong Excel với cột năm đã tách.
6. Xử Lý Lỗi Và Kiểm Tra Độ Chính Xác
6.1. Kiểm tra giá trị trả về là năm hợp lệ
=IF(AND(B2>=1900, B2<=YEAR(TODAY())), B2, "Năm không hợp lệ")
6.2. Đánh dấu các ô lỗi
Sử dụng Conditional Formatting:
- Chọn cột năm.
- Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Công thức:
=OR(ISERROR(B2), B2<1900, B2>YEAR(TODAY()))
- Đặt màu nền đỏ để cảnh báo.
6.3. Loại bỏ khoảng trắng và ký tự không mong muốn
=TRIM(CLEAN(A2))
- CLEAN: Loại bỏ ký tự không hiển thị (ASCII < 32).
- TRIM: Loại khoảng trắng đầu/cuối và dư thừa.
7. Tự Động Hóa Bằng VBA (Macro)
Khi dữ liệu rất lớn (hàng trăm nghìn), công thức có thể làm chậm file. VBA giúp thực hiện nhanh hơn.
7.1. Macro Tách Năm Từ Cột A → Cột B
Sub ExtractYear() Dim ws As Worksheet Dim rng As Range, cell As Range Dim txt As String, yr As Long Set ws = ThisWorkbook.Sheets("Sheet1") Set rng = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp)) Application.ScreenUpdating = False For Each cell In rng txt = Trim(cell.Value) yr = 0 ' Cố gắng chuyển thành ngày On Error Resume Next yr = Year(CDate(txt)) If Err.Number <> 0 Then Err.Clear ' Tìm 4 chữ số liên tiếp Dim reg As Object Set reg = CreateObject("VBScript.RegExp") reg.Pattern = "\b(19|20)\d{2}\b" reg.Global = True If reg.Test(txt) Then yr = CLng(reg.Execute(txt)(0)) End If End If On Error GoTo 0 If yr >= 1900 And yr <= Year(Date) Then cell.Offset(0, 1).Value = yr Else cell.Offset(0, 1).Value = "Invalid" End If Next cell Application.ScreenUpdating = True MsgBox "Hoàn thành tách năm sinh!", vbInformation
End Sub
- CDate: Chuyển chuỗi thành ngày nếu có thể.
- RegExp: Tìm pattern năm (1900‑2099).
- Kết quả được ghi vào cột B (Offset(0,1)).
7.2. Gán Macro vào nút
- Developer → Insert → Button (Form Control).
- Gán macro
ExtractYearcho nút. - Nhấn nút mỗi khi cần cập nhật.
8. Những Lưu Ý Khi Làm Việc Với Dữ Liệu Thực Tế
| Vấn đề | Giải pháp |
|---|---|
| Ngày tháng nhập dạng văn bản | Dùng DATEVALUE, SUBSTITUTE hoặc chuyển sang kiểu Date trong Power Query. |
| Năm sinh bị thiếu hoặc không hợp lệ | Kiểm tra bằng IF(AND(year>=1900, year<=YEAR(TODAY())),…). Đánh dấu lỗi bằng Conditional Formatting. |
| Có nhiều định dạng trong cùng một cột | Sử dụng công thức đa điều kiện (IFERROR, IFS) hoặc Power Query để chuẩn hoá. |
| Dữ liệu quá lớn, công thức chậm | Dùng VBA hoặc Power Query để xử lý trước khi đưa vào bảng tính. |
| Ký tự đặc biệt, khoảng trắng thừa | TRIM(CLEAN(text)) trước khi áp dụng các hàm ngày tháng. |
9. Tổng Kết
Việc tách năm sinh trong Excel không chỉ là một thao tác đơn giản mà còn là một chuỗi các bước xử lý dữ liệu tùy thuộc vào độ sạch và định dạng của nguồn. Bằng cách:
- Xác định kiểu dữ liệu (Date vs Text).
- Áp dụng hàm YEAR cho dữ liệu ngày tháng chuẩn.
- Sử dụng các hàm chuỗi (MID, LEFT, RIGHT, SEARCH, SUBSTITUTE) kết hợp với DATEVALUE cho các dạng không chuẩn.
- Khai thác công thức mảng, LAMBDA trong Excel 365 để tái sử dụng và mở rộng.
- Làm sạch dữ liệu bằng Power Query khi cần xử lý hàng loạt.
- Tự động hoá bằng VBA cho tập dữ liệu lớn.
Bạn sẽ có khả năng trích xuất năm sinh một cách nhanh chóng, chính xác và hiệu quả, đồng thời giảm thiểu lỗi và tăng tính chuyên nghiệp cho báo cáo hay phân tích dữ liệu.
Hãy thử áp dụng các phương pháp trên vào dự án thực tế của bạn và chia sẻ kết quả. Nếu còn thắc mắc hoặc muốn tìm hiểu sâu hơn về một kỹ thuật nào đó, đừng ngần ngại bình luận dưới đây!
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ẻ.