Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách xóa số khỏi văn bản trong một ô trong Excel và Google Trang tính.
Chúng ta sẽ thảo luận về hai công thức khác nhau để xóa số khỏi văn bản trong Excel.
Công thức hàm SUBSTITUTE
Chúng ta có thể sử dụng một công thức dựa trên Hàm SUBSTITUTE. Đó là một công thức dài nhưng là một trong những cách dễ nhất để xóa số khỏi chuỗi chữ và số.
Trong công thức này, chúng ta đã lồng các hàm SUBSTITUTE 10 lần, như sau:
| 1 | = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (B3,1, ""), 2, ""), 3, ""), 4, ""), 5, " "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Công thức TEXTJOIN của mảng
Để xóa số khỏi chuỗi chữ và số, chúng ta cũng có thể sử dụng công thức mảng phức hợp bao gồm các hàm TEXTJOIN, MID, ROW và INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1), ""))} |
Lưu ý: TEXTJOIN là một Hàm Excel mới có sẵn trong Excel 2022+ và Office 365.
Đây là một công thức phức tạp, vì vậy chúng tôi sẽ chia nó thành các bước để hiểu rõ hơn.
Bước 1
Hàm MID được sử dụng để trích xuất chuỗi chữ và số dựa trên các đối số start_num và num_chars.
Đối với đối số start_num trong hàm MID, chúng tôi sẽ sử dụng danh sách mảng kết quả từ các hàm ROW và INDIRECT.
| 1 | = ROW (INDIRECT ("1:" & LEN (B3))) |
Và đối với đối số num-chars, chúng ta sẽ đặt 1. Sau khi đặt các đối số vào hàm MID, nó sẽ trả về một mảng.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)} |
Bước 2
Chúng tôi sẽ thêm 0 vào mỗi giá trị trong mảng kết quả (mà chúng tôi nhận được từ hàm MID ở trên). Trong excel, nếu thêm số vào các ký tự không phải số, chúng tôi sẽ nhận được lỗi #VALUE! Lỗi. Vì vậy, sau khi thêm 0 vào mảng ở trên, chúng ta sẽ nhận được một mảng các số và #Value! Các lỗi.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0} |
Bước 3
Sau khi thêm 0, mảng kết quả được đưa vào hàm ISERR. Như chúng ta biết rằng hàm ISERR trả về TRUE cho các lỗi và FALSE cho các giá trị không lỗi.
Vì vậy, nó sẽ cung cấp một mảng TRUE và FALSE, TRUE cho các ký tự không phải số và FALSE cho các số.
| 1 | = ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0) |
Bước 4
Bây giờ, chúng ta sẽ thêm hàm IF.
Hàm IF sẽ kiểm tra kết quả của hàm ISERR (Bước 3). Nếu giá trị của nó là TRUE, nó sẽ trả về một mảng gồm tất cả các ký tự của một chuỗi chữ và số. Đối với điều này, chúng tôi đã thêm một hàm MID khác mà không thêm số 0 vào cuối. Nếu giá trị của hàm IF là FALSE, nó sẽ trả về giá trị trống (“”).
Bằng cách này, chúng ta sẽ có một mảng chỉ chứa các ký tự không phải số của chuỗi.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1 ), "") |
Bước 5
Cuối cùng, mảng trên được đưa vào hàm TEXTJOIN. Hàm TEXTJOIN sẽ nối tất cả các ký tự của mảng trên và bỏ qua chuỗi trống.
Dấu phân tách cho hàm này được đặt là một chuỗi trống (“”) và giá trị của đối số ignore_empty được nhập TRUE.
Điều này sẽ cung cấp cho chúng tôi kết quả mong muốn, tức là chỉ các ký tự không phải số của chuỗi chữ và số.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1), ""))} |
Lưu ý: Đây là một công thức mảng. Khi nhập công thức mảng trong Excel 2022 trở xuống, bạn phải sử dụng CTRL + SHIFT + ENTER nhập công thức thay vì nhập thông thường ĐI VÀO.
Bạn sẽ biết mình đã nhập đúng công thức bằng dấu ngoặc nhọn xuất hiện. KHÔNG gõ dấu ngoặc nhọn theo cách thủ công, công thức sẽ không hoạt động.
Với Office 365 (và có lẽ là các phiên bản Excel sau năm 2022), bạn có thể chỉ cần nhập công thức như bình thường.
Hàm TRIM
Khi các số bị xóa khỏi chuỗi, chúng ta có thể còn thừa khoảng trắng. Để loại bỏ tất cả các khoảng trắng ở cuối và ở đầu, và các khoảng trắng thừa giữa các từ, chúng ta có thể sử dụng hàm TRIM trước công thức chính, như sau:
| 1 | = TRIM (C3) |
Xóa số khỏi văn bản trong Google Trang tính
Công thức xóa số khỏi văn bản hoạt động hoàn toàn giống trong Google Trang tính như trong Excel:
