• Home » 
  • Excel Tips » 
  • Tách Chữ Và Số Trong Excel: 4 Phương Pháp Xử Lý Dữ Liệu Hỗn Hợp Hiệu Quả

Tách Chữ Và Số Trong Excel: 4 Phương Pháp Xử Lý Dữ Liệu Hỗn Hợp Hiệu Quả

By 0
Dữ liệu nhân viên bao gồm chữ và số cần tách trong Excel
Table of Contents

Thời gian đọc ước tính: 12 phút

Những điểm chính

  • Flash Fill: Phương pháp nhanh nhất cho các tác vụ đơn giản, một lần, hoạt động dựa trên nhận diện mẫu.
  • Hàm Excel (Formula): Sử dụng kết hợp LEFT, MID, MIN, FIND để xử lý dữ liệu động, tự cập nhật khi dữ liệu nguồn thay đổi.
  • VBA (Macro): Tạo hàm tùy chỉnh để tự động hóa quy trình cho các tập dữ liệu lớn hoặc lặp lại thường xuyên.
  • Power Query: Công cụ mạnh mẽ nhất để xử lý dữ liệu hàng loạt, cho phép làm sạch và chuyển đổi dữ liệu mà không ảnh hưởng đến bảng gốc.

Việc dữ liệu văn bản và số liệu bị dính liền trong cùng một ô là một vấn đề phổ biến khi làm việc với Excel, đặc biệt là khi xuất dữ liệu từ các phần mềm quản lý cũ hoặc file PDF. Bạn có thể gặp các mã nhân viên như “MKT1029” hoặc mã sản phẩm “IPHONE14”. Để phân tích dữ liệu chính xác, bạn cần tách riêng phần chữ (MKT, IPHONE) và phần số (1029, 14) ra các cột khác nhau.

Mặc dù Excel không có một nút bấm đơn lẻ nào tên là “Tách chữ và số”, nhưng nền tảng này cung cấp bộ công cụ đa dạng để giải quyết vấn đề này. Bài viết này sẽ hướng dẫn bạn 4 phương pháp từ cơ bản đến nâng cao để tách biệt dữ liệu, đảm bảo tính chính xác và hiệu quả cho công việc báo cáo của bạn.

1. Sử dụng Flash Fill để tách văn bản và số (Nhanh & Đơn giản)

Flash Fill (tính năng Tự động điền) được giới thiệu từ phiên bản Excel 2013, là giải pháp thông minh nhất cho những người dùng không muốn thao tác với công thức phức tạp. Tính năng này hoạt động bằng cách “học” mẫu dữ liệu mà bạn nhập thủ công và áp dụng logic đó cho toàn bộ cột.

Khi nào nên dùng Flash Fill?

Phương pháp này phù hợp nhất khi bạn có danh sách dữ liệu tĩnh và cấu trúc dữ liệu tương đối đồng nhất. Ví dụ: cột A chứa “Sale2023”, “HR2024”, Flash Fill sẽ dễ dàng nhận ra quy luật tách chữ và số.

Các bước thực hiện

Giả sử bạn có dữ liệu nhân viên ở cột A, bao gồm mã phòng ban (chữ) và mã số nhân viên (số) dính liền nhau. Chúng ta muốn tách chúng ra cột B (Chữ) và cột C (Số).

Dữ liệu nhân viên bao gồm chữ và số cần tách trong ExcelDữ liệu nhân viên bao gồm chữ và số cần tách trong Excel

  1. Nhập mẫu thủ công: Tại ô B2, bạn hãy gõ chính xác phần văn bản muốn tách từ ô A2. Trong ví dụ này, bạn nhập “MKT”.
  2. Kích hoạt Flash Fill:
    • Chọn ô B2 vừa nhập.
    • Di chuyển con trỏ chuột đến góc dưới bên phải của ô B2 cho đến khi xuất hiện dấu cộng màu đen (Fill Handle).
    • Nhấn giữ chuột trái và kéo xuống các ô còn lại trong cột.

Nhập kết quả mẫu MKT vào ô B2 để dạy ExcelNhập kết quả mẫu MKT vào ô B2 để dạy ExcelKéo thả chuột tại góc dưới ô để sao chép dữ liệuKéo thả chuột tại góc dưới ô để sao chép dữ liệu

  1. Chọn tùy chọn Flash Fill: Sau khi kéo chuột, danh sách có thể chỉ sao chép chữ “MKT”. Bạn hãy nhấp vào biểu tượng Auto Fill Options (Tùy chọn tự động điền) xuất hiện ở cuối vùng chọn và chọn Flash Fill.

Chọn tùy chọn Flash Fill trong menu Auto Fill OptionsChọn tùy chọn Flash Fill trong menu Auto Fill Options

Ngay lập tức, Excel sẽ điền phần văn bản tương ứng cho các ô còn lại dựa trên mẫu bạn đã cung cấp.

Kết quả sau khi tách phần văn bản ra khỏi chuỗi ký tựKết quả sau khi tách phần văn bản ra khỏi chuỗi ký tự

Bạn thực hiện tương tự cho cột C để lấy phần số. Nhập số “1029” vào ô C2 và dùng Flash Fill cho cả cột.

Lưu ý quan trọng:
Kết quả từ Flash Fill là dữ liệu tĩnh. Nếu bạn sửa đổi nội dung ở cột A (ví dụ: sửa MKT1029 thành SAL1029), cột B và C sẽ không tự động cập nhật. Bạn sẽ cần chạy lại Flash Fill. Nếu dữ liệu của bạn thay đổi thường xuyên, hãy xem xét phương pháp dùng hàm dưới đây.

2. Sử dụng Hàm kết hợp để tách dữ liệu (Tự động cập nhật)

Đối với các báo cáo cần tính động (dynamic), việc sử dụng công thức là bắt buộc. Khi dữ liệu gốc thay đổi, kết quả tách cũng sẽ thay đổi theo.

Thách thức của dữ liệu không đồng nhất

Nếu độ dài của chuỗi ký tự luôn cố định (ví dụ: luôn là 3 chữ cái đầu và 4 số cuối), bạn chỉ cần dùng hàm LEFT hoặc RIGHT đơn giản. Tuy nhiên, thực tế thường phức tạp hơn: mã phòng ban có thể là 2 chữ (HR), 3 chữ (MKT) hoặc 4 chữ (PROD), và số lượng chữ số cũng có thể thay đổi.

Vì vậy, chúng ta cần một công thức thông minh hơn để tự động xác định vị trí bắt đầu của con số đầu tiên.

Công thức tách phần Chữ (Văn bản)

Để lấy phần chữ nằm bên trái chuỗi, bạn sử dụng công thức mảng sau tại ô B2:

=LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1)

Sử dụng hàm LEFT kết hợp FIND và MIN để tách chữSử dụng hàm LEFT kết hợp FIND và MIN để tách chữ

Công thức tách phần Số

Để lấy phần số nằm bên phải chuỗi, bạn dùng công thức sau tại ô C2:

=MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),100)

Sử dụng hàm MID để lấy phần số từ vị trí tìm đượcSử dụng hàm MID để lấy phần số từ vị trí tìm được

Giải thích chi tiết cơ chế hoạt động

Công thức này trông có vẻ phức tạp, nhưng logic của nó rất mạch lạc. Hãy cùng phân tích từng thành phần của công thức tách chữ:

  1. FIND({0,1,2,3,4,5,6,7,8,9}, A2):
    Hàm này yêu cầu Excel tìm vị trí của tất cả các chữ số từ 0 đến 9 trong ô A2. Kết quả trả về sẽ là một mảng các vị trí.

    • Ví dụ với ô A2 là “MKT1029”:
      • Số 1 nằm ở vị trí thứ 4.
      • Số 0 nằm ở vị trí thứ 5.
      • Các số không có trong chuỗi (như 3, 4, 5…) sẽ trả về lỗi #VALUE!.
    • Mảng kết quả thô: {#VALUE!, 4, 5, #VALUE!, ...}

  2. IFERROR(..., ""):
    Hàm này sẽ loại bỏ các lỗi #VALUE! và thay thế bằng chuỗi rỗng. Mảng lúc này chỉ còn chứa vị trí của các con số thực sự xuất hiện: {"", 4, 5, "", ...}.

  3. MIN(...):
    Hàm MIN sẽ tìm giá trị nhỏ nhất trong mảng trên. Giá trị nhỏ nhất chính là vị trí của con số đầu tiên xuất hiện trong chuỗi. Trong ví dụ “MKT1029”, số đầu tiên (số 1) nằm ở vị trí thứ 4. Vậy MIN trả về 4.

  4. LEFT(A2, ... -1):
    Bây giờ ta đã biết số bắt đầu ở vị trí thứ 4. Nghĩa là phần chữ kết thúc ở vị trí 4 - 1 = 3. Hàm LEFT sẽ lấy 3 ký tự đầu tiên từ bên trái. Kết quả là “MKT”.

Trường hợp ngược lại (Số trước, Chữ sau):
Nếu dữ liệu của bạn có dạng “1029MKT” (số đứng trước), bạn chỉ cần thay đổi logic một chút: Dùng hàm MAX để tìm vị trí con số cuối cùng, hoặc dùng logic tìm ký tự chữ cái đầu tiên.

3. Sử dụng VBA để tạo hàm tùy chỉnh (Dành cho người dùng chuyên sâu)

Nếu bạn thường xuyên phải thực hiện tác vụ này trên nhiều file khác nhau, việc viết lại công thức dài dòng ở trên rất mất thời gian. Giải pháp tối ưu là tạo ra một Hàm Người Dùng Tự Định Nghĩa (User Defined Function – UDF) bằng VBA.

Cách này cho phép bạn dùng một hàm đơn giản như =GetText(A2) hoặc =GetNumber(A2) ngay trong bảng tính.

Mã VBA tách số (GetNumber)

Function GetNumber(CellRef As String)
    Dim StringLength As Integer
    StringLength = Len(CellRef)
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then
            Result = Result & Mid(CellRef, i, 1)
        End If
    Next i
    GetNumber = Result
End Function

Mã VBA tách chữ (GetText)

Function GetText(CellRef As String)
    Dim StringLength As Integer
    StringLength = Len(CellRef)
    For i = 1 To StringLength
        If Not (IsNumeric(Mid(CellRef, i, 1))) Then
            Result = Result & Mid(CellRef, i, 1)
        End If
    Next i
    GetText = Result
End Function

Cách cài đặt và sử dụng

  1. Mở trình soạn thảo VBA: Trên thanh Ribbon, chọn thẻ Developer (Nhà phát triển) và nhấn vào nút Visual Basic (hoặc dùng phím tắt Alt + F11).

Nút Visual Basic trên thẻ DeveloperNút Visual Basic trên thẻ Developer

  1. Chèn Module mới: Trong cửa sổ Project Explorer bên trái, chuột phải vào tên Workbook của bạn, chọn Insert > Module.

Chọn Insert Module trong trình soạn thảo VBAChọn Insert Module trong trình soạn thảo VBA

  1. Dán mã: Sao chép hai đoạn mã trên và dán vào cửa sổ soạn thảo vừa hiện ra.

Dán mã VBA vào cửa sổ ModuleDán mã VBA vào cửa sổ Module

  1. Sử dụng hàm: Quay lại bảng tính Excel, bây giờ bạn có thể gõ công thức =GetText(A2) để lấy chữ và =GetNumber(A2) để lấy số.

Sử dụng hàm tự tạo GETTEXT trong bảng tínhSử dụng hàm tự tạo GETTEXT trong bảng tính

Lưu ý về định dạng file: Để lưu giữ các hàm VBA này, bạn bắt buộc phải lưu file Excel dưới định dạng Excel Macro-Enabled Workbook (.xlsm).

4. Sử dụng Power Query (Giải pháp tự động hóa hiện đại)

Power Query là công cụ xử lý dữ liệu mạnh mẽ nhất hiện nay trong hệ sinh thái Excel. Phương pháp này đặc biệt hữu ích khi bạn làm việc với tập dữ liệu lớn (hàng nghìn dòng) và cần quy trình làm sạch dữ liệu có thể tái sử dụng.

Ưu điểm vượt trội

  • Không dùng công thức phức tạp làm nặng file.
  • Quy trình được ghi lại: Lần sau khi có dữ liệu mới, bạn chỉ cần nhấn “Refresh”.
  • Giao diện trực quan, không cần biết code.

Các bước thực hiện

Trước tiên, hãy đảm bảo dữ liệu của bạn đã được chuyển thành dạng Excel Table (Chọn vùng dữ liệu và nhấn Ctrl + T).

  1. Nhập dữ liệu vào Power Query:
    • Chọn một ô bất kỳ trong bảng dữ liệu.
    • Vào thẻ Data trên thanh Ribbon.
    • Chọn From Table/Range trong nhóm Get & Transform Data.

Chọn From Table Range trong thẻ DataChọn From Table Range trong thẻ Data

  1. Tách cột trong trình biên tập:
    • Cửa sổ Power Query Editor sẽ mở ra. Chọn cột chứa dữ liệu hỗn hợp.
    • Vào thẻ Transform (hoặc Home).
    • Chọn Split Column (Tách cột).

Chọn chức năng Split Column trong Power QueryChọn chức năng Split Column trong Power Query

  1. Chọn tiêu chí tách:
    • Chọn By Non-Digit to Digit (Từ Không phải số sang Số). Tùy chọn này sẽ cắt chuỗi ngay tại điểm chuyển giao từ chữ sang số (ví dụ: giữa ‘T’ và ‘1’ trong MKT1029).
    • Nếu dữ liệu của bạn là số trước chữ sau (ví dụ: 1029MKT), bạn chọn ngược lại là By Digit to Non-Digit.

Chọn tùy chọn By Non Digit to DigitChọn tùy chọn By Non Digit to Digit

  1. Xuất kết quả:
    • Bạn sẽ thấy cột gốc được tách thành hai cột riêng biệt ngay lập tức.
    • Nhấn Close & Load ở thẻ Home để đưa dữ liệu đã xử lý ra một sheet mới trong Excel.

Kết quả sau khi tách cột trong Power QueryKết quả sau khi tách cột trong Power Query

Dữ liệu kết quả sẽ là một bảng mới màu xanh lá cây.

Bảng kết quả cuối cùng từ Power QueryBảng kết quả cuối cùng từ Power Query

Điểm tuyệt vời của Power Query là sự liên kết. Nếu bạn thêm 100 nhân viên mới vào bảng gốc, bạn chỉ cần sang bảng kết quả, chuột phải và chọn Refresh, toàn bộ dữ liệu mới sẽ được tách tự động theo quy tắc bạn vừa thiết lập.

Bảng so sánh 4 phương pháp

Phương pháp Độ khó Tính cập nhật (Dynamic) Phù hợp cho
Flash Fill Rất Dễ Không (Tĩnh) Dữ liệu ít, làm nhanh một lần
Hàm (Formula) Trung bình Có Dữ liệu trung bình, cần cập nhật liên tục
VBA Cao Có Người dùng chuyên nghiệp, xử lý lặp lại nhiều file
Power Query Dễ Có Dữ liệu lớn, quy trình chuẩn hóa, tự động hóa

Mỗi phương pháp đều có thế mạnh riêng. Nếu bạn cần báo cáo gấp, Flash Fill là bạn đồng hành tốt nhất. Tuy nhiên, để xây dựng một dashboard ổn định và chuyên nghiệp, Power Query hoặc Hàm Excel là lựa chọn của các chuyên gia phân tích dữ liệu.

Hãy chọn phương pháp phù hợp nhất với bối cảnh công việc của bạn để tiết kiệm thời gian và nâng cao hiệu suất xử lý dữ liệu.

Câu hỏi thường gặp (FAQ)

1. Flash Fill của tôi không hoạt động, tại sao?
Flash Fill có thể chưa được bật trong cài đặt Excel. Bạn hãy vào File > Options > Advanced, tìm mục Editing Options và đảm bảo ô Automatically Flash Fill đã được tích chọn. Ngoài ra, Flash Fill cần mẫu dữ liệu rõ ràng; nếu dữ liệu quá lộn xộn (ví dụ: dòng 1 là “MKT123”, dòng 2 là “123MKT”), nó có thể không nhận diện được quy luật.

2. Hàm FIND báo lỗi #VALUE! nghĩa là gì?
Lỗi #VALUE! trong hàm FIND xuất hiện khi Excel không tìm thấy ký tự bạn yêu cầu trong chuỗi văn bản. Trong công thức mảng của chúng ta, điều này là bình thường vì một ô không thể chứa đủ tất cả các số từ 0 đến 9. Đó là lý do chúng ta dùng hàm IFERROR để xử lý các lỗi này trước khi tính toán tiếp.

3. Tôi có thể dùng cách này để tách số thập phân (có dấu chấm) không?
Các phương pháp trên tập trung vào việc tách chữ số nguyên (0-9). Nếu số của bạn là số thập phân (ví dụ: “Price12.5”), logic VBA và Power Query cần được điều chỉnh để chấp nhận dấu chấm (.) là một phần của số. Với công thức, logic vẫn hoạt động tốt để tìm vị trí bắt đầu của số, nhưng độ dài chuỗi lấy ra cần được kiểm soát kỹ hơn.

4. Power Query có làm mất dữ liệu gốc không?
Không. Power Query hoạt động theo nguyên tắc trích xuất và chuyển đổi (Extract – Transform – Load). Dữ liệu gốc của bạn vẫn nguyên vẹn. Kết quả xử lý sẽ được xuất ra một sheet mới hoặc một vị trí mới mà bạn chỉ định.

5. Làm sao để lưu file chứa mã VBA?
Bạn không được lưu dưới dạng .xlsx thông thường vì mã VBA sẽ bị xóa. Bắt buộc phải chọn Save As và chọn định dạng Excel Macro-Enabled Workbook (.xlsm) hoặc Excel Binary Workbook (.xlsb).

Nguồn tham khảo

  1. Microsoft Support: Sử dụng Flash Fill trong Excel – Hướng dẫn chính thức từ Microsoft về cách kích hoạt và sử dụng Flash Fill.
  2. ExcelJet: Split text and numbers – Phân tích chi tiết về các công thức mảng để tách dữ liệu.
  3. Ablebits: How to separate text and numbers in Excel – Các ví dụ mở rộng về việc sử dụng biểu thức chính quy (Regex) trong việc tách chuỗi.
  4. GcfGlobal: Excel: Power Query – Tài liệu học tập cơ bản về cách sử dụng Power Query để biến đổi dữ liệu.
Edupedia

Related Posts

Leave a Comment

Offcanvas
Offcanvas