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

ROW[INDIRECT["1:100"]]

Đoạn mã này tạo ra một mảng chứa 100 số như thế này

{1,2,3,4,5,6,7,8...99,100}

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

{"1";"0";"0";" ";"a";"p";"p";"l";"e";"s";"";"";"";""...}

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

{1;0;0;"";"";"";"";"";....}

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

Excel cho phép chúng tôi tách các ký tự không phải là số khỏi chuỗi bằng cách sử dụng một số hàm Excel. TEXTJOIN, IFERROR, MID, ROW và GIÁN TIẾP. Hướng dẫn từng bước này sẽ hỗ trợ người dùng Excel ở mọi cấp độ trong việc trích xuất các ký tự số từ văn bản

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

=TEXTJOIN[delimiter, ignore_empty, text1, [text2], ...]

Các tham số của hàm TEXTJOIN là

• dấu phân cách – một chuỗi sẽ được sử dụng làm dấu phân cách giữa các mục văn bản
• ignore_empty – TRUE để bỏ qua các ô trống
• text1, [text2] – các chuỗi văn bản sẽ được nối

Cú pháp của Công thức IFERROR

=IFERROR[value, value_if_error]

Các tham số của hàm IFERROR là

• value – một ô được đánh giá
• value_if_error – giá trị sẽ được chèn nếu có lỗi trong ô

Cú pháp của Công thức MID

=MID[text, start_num, num_chars]

Các tham số của hàm MID là

• text – một chuỗi mà chúng tôi muốn trích xuất các ký tự
• start_num – vị trí của ký tự đầu tiên mà chúng tôi muốn trích xuất từ ​​chuỗi
• num_chars – một số ký tự mà chúng tôi muốn trích xuất từ ​​ký tự đầu tiên

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ỗi

Chú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ư

={TEXTJOIN["",TRUE,IFERROR[MID[B3,ROW[INDIRECT["1:30"]],1]+0,""]]}

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

• Chọn ô C3 và nhấp vào nó
• Chèn công thức. ________số 8
• Nhấn ctrl, shift và enter đồng thời để tạo mảng
• Kéo công thức xuống các ô khác trong cột bằng cách nhấp và kéo biểu tượng “+” nhỏ ở dưới cùng bên phải của ô

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

{“M”;”i”;”k”;”e”;” “;”h”;”a”;”s”;” “;”$”;”3”;”0”;”0”;”0”;” “;”d”;”o”;”l”;”l”;”a”;”r”;”s”;””;””;””;””;””;””;””;””;}

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

{1,2,3,4,5,6,7,8...99,100}

0

Hàm IFERROR thay thế các giá trị lỗi bằng một chuỗi rỗng

{1,2,3,4,5,6,7,8...99,100}

1

Cuố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.