Công thức chung
(=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,"")))
Tóm lược
Để xóa các ký tự không phải số khỏi chuỗi văn bản, bạn có thể thử công thức thử nghiệm này dựa trên hàm TEXTJOIN, một hàm mới trong Excel 2019. Trong ví dụ được hiển thị, công thức trong C5 là:
(=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,"")))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Làm việc từ trong ra ngoài, công thức MID được sử dụng để trích xuất văn bản trong B5, mỗi lần một ký tự. Chìa khóa là mảnh ROW / INDIRECT:
ROW(INDIRECT("1:100"))
sẽ xoay lên một mảng chứa 100 số như thế này:
(1,2,3,4,5,6,7,8… .99,100)
Lưu ý: 100 đại diện cho số ký tự tối đa để xử lý. Thay đổi để phù hợp với dữ liệu của bạn.
Mảng này đi vào hàm MID dưới dạng đối số start_num . Đối với num_chars , chúng tôi sử dụng 1.
Hàm MID trả về một mảng như sau:
("1"; "0"; "0"; ""; "a"; "p"; "p"; "l"; "e"; "s"; ""; ""; ""; " “…)
(các mục bổ sung trong mảng bị xóa để dễ đọc)
Đối với mảng này, chúng tôi thêm số không. Đây là một thủ thuật đơn giản buộc Excel phải thử và ép buộc văn bản thành một số. Các giá trị văn bản dạng số như "1", "2", "3", "4", v.v. được chuyển đổi, trong khi các giá trị không phải dạng số không thành công và gây ra lỗi #VALUE. Chúng tôi sử dụng hàm IFERROR để bắt những lỗi này và trả về một chuỗi rỗng (""), trong khi các giá trị số được chuyển vào mảng. Kết quả là một mảng chỉ chứa các số và chuỗi rỗng:
(1; 0; 0; ""; ""; ""; ""; "";….)
Cuối cùng, kết quả mảng này đi vào hàm TEXTJOIN dưới dạng đối số text1 . Đối với dấu phân tách , chúng tôi sử dụng một chuỗi rỗng ("") và đối với ignore_empty, chúng tôi cung cấp TRUE. Sau đó, TEXTJOIN sẽ nối tất cả các giá trị không trống trong mảng và trả về kết quả.
Lưu ý: TEXTJOIN sẽ trả về các số dưới dạng văn bản, ví dụ: "100," 500 ", v.v. Nếu bạn muốn có một kết quả số thực, hãy thêm số 0 hoặc bọc toàn bộ công thức trong hàm VALUE.
