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, tôi sẽ chia sẻ với bạn cách chúng ta có thể lọc ra số hoặc chữ cái trong một ô Excel có hai kiểu dữ liệu này trộn lẫn bằng cách sử dụng các hàm Excel hoặc các hàm tùy chỉnh trong VBA.

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

Nếu dữ liệu của bạn có cấu trúc và có quy tắc rõ ràng, bạn có thể dễ dàng giải quyết vấn đề này với các hàm tích hợp sẵn trong Excel 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 trên vị trí của chuỗi trong dữ liệu đó tủ. Đâ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 công việc thực tế: hỗn hợp dữ liệu số và chữ số không theo cùng một mẫu. quy tắc nào.

Nếu bạn muốn làm và thực hành cùng một lúc, hãy xem video sau:

Video trên YouTube

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

Các công thức sau hoạt động trong các 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 bài viết dưới đây.

Tải game crack việt hoá tại: https://daominhha.com

Kể từ phiên bản Excel 365, sau đó là phiên bản Excel 2019, Microsoft đã nâng cấp cho Excel một số hàm rất hữu ích, trong trường hợp này, chính 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 giải quyết 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 nhấn CTRL + SHIFT + Enter để nhận được kết quả

>>> Xem thêm:  Cải thiện kỹ năng Excel Ngay – Siêu ưu đãi mùa dịch Covid

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

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

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

Cách lọc văn bản khỏi ô dữ liệu trong Excel

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

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

  1. Chúng tôi sử dụng cụm từ ROW(INDIRECT("1:"&LEN(A2))) hoặc SEQUENCE(LEN(A2)) để tạo một chuỗi số 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}
  2. Sau đó, kết quả của cụm công thức này, chúng ta sẽ 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)
  3. Kết quả của việc áp dụng hàm MID: tách 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"}
  4. Sau đó, chúng tôi sẽ lấy mảng dữ liệu này và nhân nó với 1. Các giá trị số trong mảng sẽ vẫn là chính nó và các giá trị không phải số sẽ trả về kết quả. #VALUE!
    {2;3;4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
  5. Khi kết hợp với hàm IFERROR, chúng ta sẽ chuyển các giá trị lỗi #VALUE! Xét về giá trị ""
    {2;3;4;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}
  6. Sau đó, chúng ta có thể sử dụng hàm TEXTJOIN để nối các phần tử của mảng trên lại với nhau bằng cách "" và bỏ qua các giá trị trống
    TEXTJOIN("",true,{2;3;4;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""})

Cách sử dụng một hàm tùy chỉnh trong VBA để xóa các chữ cái khỏi số

Điều này sẽ hoạt động trên tất cả các phiên bản Excel

Nếu bạn đang sử dụng phiên bản Excel cũ hơn hoặc các công thức ở trên quá khó nhớ, 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ữ cái 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

Bạn có thể copy đoạn mã trên vào file Excel của mình như thế nào theo hướng dẫn trong bài viết sau của Thanh:

>>> Xem thêm:  Đặt Mật Khẩu Khác Nhau Cho Từng Vùng Ô Trong Excel

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

Ngoài đoạn mã trên sử dụng vòng lặp trong VBA, chúng ta cũng có thể sử dụng Biểu thức chính quy trong VBA để giải quyết vấn đề này như sau. Logic là: chúng tôi sẽ xóa tất cả các ký tự không phải số từ 0 đến 9.

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

  • Trên sổ làm việc Excel có ít dữ liệu, không có nhiều sự khác biệt
  • Trên Sổ làm việc Excel lớn hơn, mã VBA thứ hai sử dụng Regular Express sẽ thực thi nhanh hơn

Cho dù bạn chọn cách nào, cách sử dụng trong tệp Excel cũng đơn giản như sau:

=RemoveText(A2)

trong đó A2 là ô chứa dữ liệu được xử lý

Ghi chú vừa sử dụng hàm Excel vừa sử 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 các cách sau:

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

Cách lọc ra số, giữ văn bản trong ô dữ liệu của Excel

Các công thức sau hoạt động trong các 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 bài viết dưới đây.

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

>>> Xem thêm:  Xác định ngày bất kỳ là thứ mấy trong tuần

Giả sử dữ liệu được xử lý nằm trong ô 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 Ctrl + Shift + Enter để nhập công thức mảng.

Trong Excel 365, công thức sẽ giống như sau:

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

Cách xóa số khỏi văn bản trong ô Excel

Cách sử dụng một hàm tùy chỉnh trong VBA để xóa số khỏi các chữ cái

Điều này sẽ hoạt động trên tất cả các phiên bản Excel

Đây là mã mà bạn có thể thực hiện xử lý dữ liệu: xóa số khỏi văn bản:

Chúng tôi cũng có phiên bản thứ hai, sử dụng Biểu thức chính quy trong Excel VBA

Trong trường hợp bạn cần xử lý nhiều dữ liệu, mã sử dụng Biểu thức chính quy sẽ hoạt động nhanh hơn. Cách sử dụng trong Trang tính Excel như sau:

=TRIM(RemoveNumbers(A2))

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

VBA101 – Tự động hóa 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 Thành đã 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 với tự động hóa hoàn toàn:

VBA là gì, tôi sử dụng nó để làm gì?

Nguồn : Blog hocexcel