Công thức chung
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Tóm lược
Để xóa các ký tự số khỏi chuỗi văn bản, bạn có thể sử dụng công thức dựa trên hàm TEXTJOIN. Trong ví dụ được hiển thị, công thức trong C5 là:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
Giải trình
Excel không có cách nào để truyền trực tiếp các ký tự trong chuỗi văn bản sang mảng trong công thức. Để giải quyết vấn đề, công thức này sử dụng hàm MID, với sự trợ giúp từ các hàm ROW và INDIRECT để đạt được kết quả tương tự. Công thức trong C5, được sao chép xuống, là:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Điều này trông khá phức tạp nhưng ý chính là chúng tôi tạo một mảng tất cả các ký tự trong B5 và kiểm tra từng ký tự để xem đó có phải là một số hay không. Nếu vậy, chúng tôi loại bỏ giá trị và thay thế nó bằng một chuỗi rỗng (""). Nếu không, chúng tôi thêm ký tự không phải số vào mảng "đã xử lý". Cuối cùng, chúng tôi sử dụng hàm TEXTJOIN (mới trong Excel 2019) để nối tất cả các ký tự với nhau, bỏ qua các giá trị trống.
Làm việc từ trong ra ngoài, hàm 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à đoạn mã ROW và INDIRECT ở đây:
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 hoặc sử dụng chức năng LEN như được giải thích bên dưới.
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:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Lưu ý: 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 é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 mà không có lỗi, nhưng các giá trị không phải dạng số sẽ không thành công và gây ra lỗi #VALUE. Chúng tôi sử dụng hàm IF với hàm ISERR để bắt những lỗi này. Khi gặp lỗi, chúng tôi biết mình có một ký tự không phải là số, vì vậy chúng tôi đưa ký tự đó vào mảng đã xử lý bằng một hàm MID khác:
MID(B5,ROW(INDIRECT("1:100")),1)
Nếu không gặp lỗi, chúng tôi biết chúng tôi có một số, vì vậy chúng tôi chèn một chuỗi rỗng ("") vào mảng thay cho số.
Kết quả mảng cuối cùng đ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ả.
Độ dài mảng chính xác
Thay vì mã hóa cứng một số như 100 thành INDIRECT, bạn có thể sử dụng hàm LEN để tạo một mảng với số ký tự thực tế trong ô như sau:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN trả về số ký tự trong ô dưới dạng số, được sử dụng thay vì 100. Điều này cho phép công thức tự động tăng tỷ lệ lên bất kỳ số ký tự nào.
Loại bỏ không gian thừa
Khi bạn tách các ký tự số, bạn có thể còn thừa các ký tự khoảng trắng. Để tách các khoảng trắng ở đầu và cuối và chuẩn hóa khoảng cách giữa các từ, bạn có thể bọc công thức hiển thị trên trang này bên trong hàm TRIM:
=TRIM(formula)
Với SEQUENCE
Trong Excel 365, hàm SEQUENCE mới có thể thay thế mã ROW + INDIRECT ở trên:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Ở đây, chúng tôi sử dụng SEQUENCE + LEN để xây dựng một mảng có độ dài chính xác trong một bước.
Với LET
Chúng ta có thể sắp xếp hợp lý hơn công thức này với hàm LET. Bởi vì mảng được tạo hai lần ở trên với SEQUENCE và LEN, chúng ta có thể xác định mảng như một biến và tạo nó chỉ một lần:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Ở đây giá trị của mảng chỉ được đặt một lần, sau đó được sử dụng hai lần bên trong hàm MID.
