Excel - cách chỉ giữ lại các chữ số từ một ô chứ không phải các chữ cái.
Xem video
- Kiểm tra một cột để xem có bất kỳ chữ số nào trong mã không
- Phương thức thanh toán 1:
- Điền vào Flash
- Phương pháp Mike:
- Sử dụng hàm SUBSTITUTE với Hằng số mảng.
- Bạn sẽ không phải sử dụng Ctrl + Shift + Enter vì nó là một hằng số mảng
- Thao tác này sẽ xóa từng chữ số một
- Sử dụng hàm AND để xem mọi mục trong mảng kết quả có bằng với mục gốc hay không
- Phương thức hóa đơn 3:
- Sử dụng một hàm VBA để kiểm tra các chữ số
Bản ghi video
Bill: Này. Chào mừng trở lại. Đã đến lúc dành cho một Podcast Dueling Excel khác. Tôi là Bill Jelen đến từ. Tôi sẽ được tham gia bởi Mike Girvin từ ExcelIsFun. Đây là tập 186 của chúng ta: có nếu có bất kỳ chữ số nào trong ô. Câu hỏi hôm nay do Jen gửi đến, có 13.000 hàng dữ liệu, cần xem qua một ô, nếu ký tự nào là chữ số thì đánh dấu là Có, nếu không thì Không. Vâng, Mike, tôi hy vọng rằng bạn có một số cách tuyệt vời để làm điều này bởi vì tôi không.
Tôi sẽ sử dụng flash fill và trong flash fill, tôi sẽ tạo cho nó một mẫu ở đây với một số chữ cái và số. Tôi muốn đảm bảo rằng tôi bao gồm tất cả các số có thể để nó hiểu những gì tôi đang làm và 0 như vậy. Vì vậy, có dữ liệu gốc, và sau đó tôi sẽ sửa lỗi điền vào flash cho tôi, và, trong phiên bản cố định, chúng tôi sẽ loại bỏ tất cả các chữ số. Vì vậy, tôi sẽ nhìn và xem nếu có một chữ số. Nếu có, hãy loại bỏ nó như vậy, sau đó nhấn CONTROL + E để điền nhanh và những gì chúng ta nên có bây giờ là tất cả các chữ cái, chỉ các chữ cái.
Và câu hỏi đặt ra là nó có thay đổi không? Vì vậy, = NẾU đây là = với những gì chúng tôi đang tìm kiếm, thì điều đó có nghĩa là không có thay đổi, không có chữ số, thì chúng tôi đang nói Không, ngược lại Có, như vậy và nhấp đúp, sao chép nó xuống, được rồi, và thì bất cứ thứ gì có Không đều không có chữ số ở đó. Chúng tôi loại bỏ hàng gốc của mình và khi chúng tôi sao chép hàng này, CONTROL + C, ALT + E, S, V và chúng tôi có thể loại bỏ phần lấp đầy flash. Được rồi, Mike. Hãy xem những gì bạn có. (= IF (A2 = B2, “Không”, “Có”))
Mike: Chà, Đó phải là cách sử dụng flash fill sáng tạo tuyệt vời nhất mà tôi nghĩ rằng tôi từng thấy. Bạn đã phát minh ra một văn bản, lấy ra các số thực, CONTROL + E, và nó ngay lập tức trích xuất tất cả các số và phân phối một chuỗi văn bản mới không có số, sau đó bạn thực hiện IF của mình. Tuyệt đẹp.
Ổn thỏa. Tôi sẽ xem qua trang tính này ngay tại đây và tôi sẽ sử dụng hàm SUBSTITUTE. Bây giờ, SUBSTITUTE, tôi sẽ yêu cầu nó xem văn bản đó ngay tại đó, và OLD_TEXT tôi muốn tìm và xóa, à, chúng đều là các chữ số. Vì vậy, tôi sẽ tạo một hằng số mảng (1, 2, 3, 4, 5 tất cả các chữ số và). Bây giờ đó là một hằng số mảng và nó nằm trong OLD_TEXT vì tôi không đặt một mục duy nhất ở đó mà thay vào đó tôi đặt một loạt các mục. Đây là một hoạt động mảng đối số hàm. Có 10 mặt hàng khác nhau ở đây sẽ hướng dẫn SUBSTITUTE giao 10 mặt hàng riêng biệt, được rồi, và nếu nó tìm thấy một trong những mặt hàng đó, tôi muốn gì? "". Điều đó sẽ cho biết hàm không đặt gì ở đó,). (= SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), “”))
Bây giờ, con trỏ của tôi ở cuối. Khi tôi nhấn phím F9, chắc chắn là đủ vì chỉ có một số 0, tất cả những thứ này chính xác bằng nhau ngoại trừ phím cuối cùng. Đối với cái cuối cùng, SUBSTITUTE tìm thấy 0 và không đặt gì vào vị trí của nó. Bây giờ, CONTROL-Z, CONTROL-ENTER và tôi sẽ sao chép nó xuống ngay đây, F2 và F9. Vì vậy, nếu chúng ta đi đến phiên bản 6, có phiên bản 5, nó loại bỏ 5 ở đó, nó loại bỏ 6 ở đó và nó loại bỏ 8 ở đó, vì vậy sẽ có 1, 2, 3 mục khác nhau là khác nhau. Chỉ khi tất cả các mục chính xác bằng với mục gốc thì nó mới cho chúng ta biết là không có chữ số. BỎ TRỐN.
Tôi sẽ trở lại đầu trang. Nghe có vẻ như, F2, đây là một bài kiểm tra logic AND. Hàm AND. Tôi muốn kiểm tra xem mọi mục trong số các mục trong mảng kết quả đó có = với mục gốc hay không. Khi tất cả đều đúng, thì nó sẽ cho tôi biết không có số nào trong chuông văn bản đó. ), CONTROL + ENTER, tôi nhận được điểm FALSE vì một trong số chúng bên trong thiếu số 0. Tôi sẽ sao chép điều này xuống đây. Điều này tất nhiên sẽ nhận được TRUE - giống như các mục này - bởi vì tất cả các mục được tạo bên trong, nếu tôi F2 ngay tại đây, tất cả những mục này, F9, chính xác bằng với bản gốc. BỎ TRỐN. Bây giờ, tôi lên đến đỉnh. Nhân tiện, tôi không phải sử dụng CONTROL + SHIFT + ENTER vì khi bạn sử dụng hằng số mảng này trong công thức mảng, thì bạn không phải sử dụng CONTROL + SHIFT + ENTER. (= AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2))
Ổn thỏa. Tôi sẽ bắt đầu. Đó là bài kiểm tra logic của tôi. Nếu tất cả những điều đó là đúng, giá trị nếu đúng, ở "KHÔNG", nếu không, vui lòng đặt CÓ "). CONTORL + ENTER. Nhấp đúp vào đó. Tôi sẽ phải nhấp đúp vào cái này và gửi nó xuống. Ổn thỏa. Điều đó thật thú vị với SUBSTITUTE, một hằng số mảng, một phép kiểm tra logic AND và IF, nhưng, tôi nói với bạn điều đó, tôi vẫn không thể tin được rằng flash fill, về bản chất, bạn đã sử dụng nó như thế nào để giải nén tất cả số từ đó. Được rồi, tôi sẽ trả lại cho anh, MrExcel. (= IF (AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0)), “”) = A2), “Không”, “Có”))
Bill: Chà, công thức với SUBSTITUTE và hằng số mảng và AND, điều đó thật tuyệt vời. Tôi đã phải sử dụng lấp đầy flash vì tôi không thể tìm ra điều này. Đó là tuyệt vời. Bây giờ, tôi có một cách thứ ba. Chúng ta hãy nhìn vào đó.
Bây giờ, đây là cách mà tôi thực sự sẽ giải quyết vấn đề này, chỉ một chút về VBA. Vì vậy, tôi thực hiện ALT + F11 để chuyển sang VBA, INSERT, MODULE, sau đó nhập mã này. Chúng ta sẽ tạo một hàm mới có tên HASNUMBERS và chúng ta sẽ chuyển nó vào giá trị ô, và chúng ta sẽ bắt đầu với từ ALPHA. Chúng tôi xem xét từng ký tự đơn lẻ và nếu mã đó, nếu (mã ASC - 06:35) của ký tự đó nằm trong khoảng từ 48 đến 57, thì chúng tôi nói rằng đó là SỐ, CHỨC NĂNG EXIT và cứ tiếp tục. Đúng vậy, nó sẽ tìm cho đến khi nó tìm thấy một chữ số. Khi đó, nó trả về HASNUMBERS. Vì vậy, ở đây, chúng ta sẽ nói = HASNUMBERS, trỏ đến ô đó và nhấp đúp để sao chép ô đó xuống. Bất cứ khi nào nó nhìn thấy một chữ số ở đó, sẽ nhận được CÁC SỐ, ALPHA, dễ dàng sắp xếp chúng. (= Số HasNumbers (A2))
Được rồi, tóm tắt nhanh tập. Mục tiêu: kiểm tra cột xem có chữ số nào trong mã ký tự, mã trong ô không. Tôi đã sử dụng flash fill để loại bỏ các chữ số, sau đó là hàm độ dài để xem nó có thay đổi hay không. Mike có một công thức tuyệt vời, hàm SUBSTITUTE với một hằng số mảng. Bạn không cần CONTROL + SHIFT + ENTER. Bạn phải xóa từng chữ số một và sau đó sử dụng hàm AND để xem xét tất cả 10 kết quả để xem mỗi kết quả có bằng với mục gốc hay không. Một cách tuyệt vời để bắt đầu, và sau đó, dự phòng của tôi, sử dụng hàm VBA để kiểm tra các chữ số.
Chà, này. Tôi muốn cảm ơn mọi người đã ghé qua. Chúng tôi sẽ gặp lại bạn vào lần tới với một netcast khác từ và ExcelIsFun.
Tải tập tin
Tải file mẫu tại đây: Duel186.xlsm
