Excel loại bỏ các ký tự không phải chữ và số
Làm việc từ trong ra ngoài, chức năng MID được sử dụng để trích xuất văn bản trong B5, từng ký tự một. Mấu chốt của bước này là việc sử dụng hàm ROW với hàm INDIRECT ở đây Show
Đoạn mã này tạo ra một mảng chứa 100 số như thế này
Ghi chú. 100 đại diện cho các ký tự tối đa để xử lý; 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. Với những đầu vào này, hàm MID trả về một mảng như thế này
Ghi chú. các mục bổ sung trong mảng được loại bỏ để 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 một giá trị thành một số. Các giá trị văn bản số như "1","2","3","4", v.v. được chuyển đổi, trong khi các giá trị không phải là số không thành công và gây ra lỗi #VALUE. Chúng tôi sử dụng hàm IFERROR để phát hiện những lỗi này và trả về một chuỗi trống (""), trong khi các giá trị số chuyển qua một mảng đầu ra. Kết quả là một mảng chỉ chứa số và chuỗi rỗng
Cuối cùng, kết quả mảng này được chuyển đến hàm TEXTJOIN dưới dạng đối số text1. Đối với dấu phân cách, chúng tôi sử dụng một chuỗi trống ("") và đối với ign_empty, chúng tôi cung cấp TRUE, vì chúng tôi chỉ muốn làm việc với các giá trị thực tế còn lại. TEXTJOIN sau đó nối tất cả các giá trị không trống trong mảng và trả về kết quả cuối cùng Ghi chú. 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, bạn có thể thêm số không (+0) hoặc bọc toàn bộ công thức trong hàm VALUE Hình 1. Xóa các ký tự không phải số khỏi văn bản Cú pháp của Công thức TEXTJOIN
Các tham số của hàm TEXTJOIN là
Cú pháp của Công thức IFERROR
Các tham số của hàm IFERROR là
Cú pháp của Công thức MID
Các tham số của hàm MID là
ROW và INDIRECT là các hàm trợ giúp trong cú pháp mảng công thức MID và giải thích cho phần công thức này sẽ được cung cấp thông qua ví dụ Thiết lập dữ liệu của chúng tôi để tước các ký tự không phải là sốBảng của chúng tôi bao gồm 2 cột. “Văn bản” (cột B) và “Số từ Văn bản” (cột C). Ý tưởng là tách các ký tự không phải số khỏi chuỗi và đặt các ký tự số vào cột C Hình 2. Cấu trúc bảng với các ô chứa văn bản và số Lấy các ký tự số từ chuỗiChúng tôi muốn chỉ trích xuất các ký tự số từ chuỗi trong cột “Văn bản”. Điều này có thể được thực hiện với sự kết hợp của một số chức năng. TEXTJOIN, IFERROR, MID, ROW và GIÁN TIẾP Công thức trông giống như
Một công thức phải là một hàm mảng để hoạt động bình thường. Để tạo một hàm mảng, chúng ta phải bấm đồng thời ctrl, shift và nhập vào công thức Để áp dụng công thức chúng ta cần làm theo các bước sau
Hình 3. Một công thức để loại bỏ các ký tự không phải là số khỏi chuỗi Công thức MID trích xuất từng ký tự của chuỗi với sự trợ giúp của phần công thức. HÀNG( GIÁN TIẾP(“1. 30”)). Số 1. 30” trong công thức GIÁN TIẾP có nghĩa là hàm MID đánh giá 30 ký tự của chuỗi. Chúng ta có thể nhập số cao hơn nếu chuỗi chứa nhiều ký tự hơn Hàm MID trả về một mảng gồm 30 ký tự trong một chuỗi
Chúng tôi đã thêm giá trị 0 sau mảng vì công thức sẽ hiển thị các giá trị lỗi bất cứ khi nào ký tự là văn bản 0Hàm IFERROR thay thế các giá trị lỗi bằng một chuỗi rỗng 1Cuối cùng, hàm TEXTJOIN loại bỏ các chuỗi rỗng và kết quả là chỉ còn lại các ký tự số. 3000 Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn một ứng dụng đơn giản của công thức hoặc hàm. Nếu bạn muốn tiết kiệm hàng giờ nghiên cứu và sự thất vọng, hãy dùng thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút. |