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

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

1. 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}
2. 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)
3. 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"}
4. 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!}
5. 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;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}
6. 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;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""})