Cách lọc lấy số trong ô lẫn cả số và chữ hoặc ngược lại

Trong bài viết này, mình sẽ chia sẻ với các bạn cách chúng ta có thể lọc lấy số hoặc chữ trong một ô Excel có lẫn hai loại dữ liệu này bằng cách sử dụng hàm Excel hoặc hàm tự tạo trong VBA.

Trong một số trường hợp, khi bạn nhận lại được dữ liệu mà cùng trong một ô có lẫn cả số và chữ, và nhiệm vụ của bạn là cần tách 2 loại dữ liệu này ra thành các cột riêng biệt để phục vụ cho công việc của mình.

Nếu dữ liệu của bạn có cấu trúc, có quy luật rõ ràng thì bạn có thể dễ dàng giải quyết việc này bằng các hàm Excel có sẵn như hàm LEFT, hàm RIGHT, hàm MID và một số hàm xử lý chuỗi khác trong Excel dựa vào vị trí xuất hiện của chuỗi trong ô dữ liệu đó. Đây là những trường hợp lý tưởng, bạn có thể dễ dàng giải quyết, trong bài viết này, chúng ta hãy cùng nhau đi giải quyết những trường hợp khó hơn trong thực tế công việc: dữ liệu số và chữ lẫn lộn không theo một quy luật nào.

Nếu các bạn muốn vừa làm vừa thực hành, hãy theo dõi video sau đây:

Xem nhanh

Cách lọc bỏ chữ và giữ lại số trong ô Excel

Các công thức sau đây hoạt động trong phiên bản Excel 365 và Excel 2019. Nếu bạn cần một giải pháp hoạt động cho các phiên bản Excel, hãy theo dõi tiếp bài viết ở dưới.

Kể từ phiên bản Excel 365, sau đó là phiên bản Excel 2019, Microsoft đã nâng cấp thêm cho Excel một số hàm hết sức có ích, trong trường hợp này, đó là hàm TEXTJOIN sẽ giúp chúng ta xử lý vấn đề đang gặp phải.

Một công thức chung cho các trường hợp chúng ta cần xử lý là

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2))), 1) *1, ""))

Sau khi nhập công thức trên, bạn cần bấm tổ hợp phím CTRL + SHIFT + Enter để có kết quả

Đối với phiên bản Excel 365, chúng ta con có thể sử dụng công thức sau đây

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))

Hãy cùng theo dõi ví dụ sau đây

Cách công thức này hoạt động như sau

Đối với bất kì công thức phức tạp nào, để hiểu được công thức, các bạn hãy bắt đầu từ lớp trong cùng của công thức. Với 2 công thức ở trên, điều này cũng không phải ngoại lệ. Giả sử trong ô A2 chúng ta có nội dung là 234 Summerset Avenue

Chúng ta sử dụng cụm ROW(INDIRECT("1:"&LEN(A2))) hoặc SEQUENCE(LEN(A2)) để tạo ra một chuỗi số thứ tự tăng dần bắt đầu từ 1 – tương ứng với độ dài của dữ liệu trong ô A2:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}
Sau đó kết quả của cụm công thức này, chúng ta sẽ dùng làm tham số start_num của hàm MID như sau
MID(A2, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}, 1)
Kết quả của việc áp dụng hàm MID: tách riêng từng ký tự trong ô dữ liệu A2 như sau
{"2";"3";"4";" ";"S";"u";"m";"m";"e";"r";"s";"e";"t";" ";"A";"v";"e";"n";"u";"e"}
Sau đó, chúng ta sẽ lấy mảng dữ liệu này và nhân với 1. Những giá trị là số ở trong mảng sẽ vẫn là chính nó, còn những giá trị không phải là số sẽ trả về kết quả là #VALUE!
{2;3;4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
Khi kết hợp với hàm IFERROR thì chúng ta sẽ chuyển được những giá trị lỗi #VALUE! về giá trị ""
{2;3;4;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}
Sau đó, chúng ta có thể sử dụng hàm TEXTJOIN để có thể nối những thành phần của mảng trên lại với nhau bằng "" và bỏ qua những giá trị rỗng
TEXTJOIN("",true,{2;3;4;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""})

Cách dùng hàm tự tạo trong VBA để bỏ chữ khỏi số

Cách làm này sẽ hoạt động được trên mọi phiên bản Excel

Nếu bạn đang sử dụng một phiên bản Excel cũ hơn hoặc các công thức bên trên quá khó nhớ để áp dụng, và bạn sẵn sàng sử dụng VBA trong Excel, thì chúng ta có thể sử dụng ngôn ngữ lập trình VBA trong Excel để có thể xử lý vấn đề tách chữ ra khỏi số này:

Function RemoveText(str As String)
    Dim sRes As String

    sRes = ""
    For i = 1 To Len(str)
        If True = IsNumeric(Mid(str, i, 1)) Then
            sRes = sRes & Mid(str, i, 1)
        End If
    Next i
    RemoveText = sRes
End Function

Cách để bạn có thể copy đoạn code trên vào file Excel của bạn theo hướng dẫn ở bài viết sau đây của Thanh:

Bắt đầu với Excel VBA và Macro

Ngoài đoạn code phía trên sử dụng vòng lặp trong VBA, chúng ta còn có thể sử dụng Regular Expression trong VBA để giải quyết vấn đề này như sau. Logic là: chúng ta sẽ loại bỏ toàn bộ các ký tự không phải là các ký tự số từ 0 tới 9.

Sự khác biệt về tốc độ giữa 2 đoạn code này:

Trên những Workbook Excel ít dữ liệu, không có nhiều sự khác biệt
Trên những Workbook Excel lớn hơn, đoạn code VBA thứ hai sử dụng Regular Express sẽ thực thi nhanh hơn

Dù bạn chọn cách nào đi nữa, thì cách sử dụng trong file Excel cũng chỉ đơn giản như sau:

=RemoveText(A2)

với A2 là ô chứa dữ liệu cần xử lý

Lưu ý cả cách dùng hàm Excel và cách dùng hàm tự tạo trong VBA đều cho kết quả là chuỗi “234”, để chuyển thành số, chúng ta có thể thực hiện một trong những cách sau đây:

=RemoveText(A2) + 0
=RemoveText(A2) * 1
=VALUE(RemoveText(A2))

Cách lọc bỏ số, giữ lại chữ trong ô dữ liệu của Excel

Công thức sau đây sẽ loại bỏ số trong một ô dữ liệu có lẫn cả chữ lẫn số trong Excel. Về logic để các bạn có thể hiểu công thức này thì cũng tương tự như cách giải thích ở phần trên.

Giả sử dữ liệu cần xử lý ở ô A2, công thức của chúng ta sẽ có dạng

=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), "")))

Trong Excel 2019, bạn sẽ cần nhấn tổ hợp phím Ctrl + Shift + Enter để nhập công thức mảng.

Trong Excel 365 thì công thức sẽ như sau:

=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))

Cách dùng hàm tự tạo trong VBA để bỏ số khỏi chữ

Cách làm này sẽ hoạt động được trên mọi phiên bản Excel

Sau đây là đoạn code để bạn có thể thực hiện xử lý dữ liệu: loại bỏ số ra khỏi chữ:

Chúng ta cũng có phiên bản thứ hai, sử dụng Regular Expression trong Excel VBA

Trong trường hợp bạn cần xử lý nhiều dữ liệu, thì đoạn code sử dụng Regular Expression sẽ hoạt động nhanh hơn. Cách sử dụng trong một Worksheet Excel như sau:

=TRIM(RemoveNumbers(A2))

Như vậy, hi vọng qua bài chia sẻ này, thì các bạn đã biết thêm một kỹ thuật rất hữu ích dùng để xử lý dữ liệu trong Excel VBA. Để bắt đầu tự viết những dòng code VBA đầu tiên của mình, hãy tham khảo khóa học VBA cùng Thanh:

VBA101 – Tự động hoá Excel với lập trình VBA cho người mới bắt đầu

và đừng quên tham khảo bài viết Thanh đã sử dụng VBA để chinh phục những khách hàng khó tính nhất bằng cách giải quyết những vấn đề tốn nhiều thời gian nhất bằng cách tự động hoàn toàn:

VBA là gì, tôi đã sử dụng VBA để làm những công việc gì?