Excel - cara menyimpan hanya angka dari sel, bukan huruf.
Menonton video
- Periksa kolom untuk melihat apakah ada digit dalam kode
- Metode Tagihan 1:
- Isi Cepat
- Metode Mike:
- Gunakan fungsi SUBSTITUTE dengan Konstanta Array.
- Anda tidak perlu menggunakan Ctrl + Shift + Enter karena ini adalah konstanta array
- Ini akan menghapus satu digit pada satu waktu
- Gunakan fungsi AND untuk melihat apakah setiap item dalam larik resultan sama dengan item asli
- Metode Tagihan 3:
- Gunakan Fungsi VBA untuk memeriksa digit
Transkrip Video
Bill: Hei. Selamat datang kembali. Saatnya untuk Podcast Excel Duel lainnya. Saya Bill Jelen dari. Saya akan bergabung dengan Mike Girvin dari ExcelIsFun. Ini adalah episode 186 kami: ya jika ada angka di dalam sel. Pertanyaan hari ini dikirim oleh Jen, memiliki 13.000 baris data, perlu melihat melalui sel, jika ada karakter yang berupa digit, tandai sebagai Ya, jika tidak Tidak. Baiklah. Baiklah, Mike, saya harap Anda memiliki cara yang luar biasa untuk melakukan ini karena saya tidak.
Saya akan menggunakan flash fill dan, dalam flash fill, saya hanya akan memberikan pola di sini dengan beberapa huruf dan angka. Saya ingin memastikan bahwa saya menyertakan semua angka yang memungkinkan agar dapat memahami apa yang saya lakukan, dan 0 seperti itu. Jadi, ada data asli, dan kemudian saya akan membuat flash fill memperbaikinya untuk saya, dan, dalam versi tetap, kita akan menghilangkan semua digitnya. Jadi, saya akan melihat dan melihat apakah ada angka. Jika ada, singkirkan seperti itu, lalu tekan CONTROL + E untuk mengisi flash, dan yang seharusnya kita miliki sekarang hanyalah huruf-hurufnya, hanya huruf-hurufnya.
Dan pertanyaannya adalah, apakah itu berubah? Jadi = JIKA ini = untuk apa yang kita cari, maka itu berarti tidak ada perubahan, tidak ada digit, maka kita mengatakan Tidak, jika tidak Ya, seperti itu, dan klik dua kali, salin ke bawah, baiklah, dan maka apa pun yang tidak memiliki angka di sana. Kami menyingkirkan baris asli kami, dan setelah kami menyalin ini, CONTROL + C, ALT + E, S, V, dan kami dapat menghilangkan isi flash. Baiklah, Mike. Mari kita lihat apa yang Anda miliki. (= IF (A2 = B2, “No”, “Yes”))
Mike: Wow ,. Itu pasti penggunaan flash fill kreatif yang paling menakjubkan yang pernah saya lihat. Anda menemukan teks, mengeluarkan angka sebenarnya, CONTROL + E, dan langsung mengekstrak semua angka dan mengirimkan string teks baru tanpa angka, lalu Anda melakukan IF. Sangat cantik.
Baik. Saya akan menuju ke lembar ini di sini dan saya akan menggunakan fungsi PENGGANTI. Sekarang, SUBSTITUTE, saya akan memberitahukannya untuk melihat teks itu di sana, dan OLD_TEXT yang ingin saya temukan dan hapus, semuanya adalah digitnya. Jadi, saya akan membuat konstanta array (1, 2, 3, 4, 5 semua digit, dan). Sekarang itu adalah konstanta array dan itu ada di OLD_TEXT karena saya tidak meletakkan satu item di sana tetapi saya meletakkan banyak item. Ini adalah operasi array argumen fungsi. Ada 10 item berbeda di sini yang akan menginstruksikan SUBSTITUTE untuk mengirimkan 10 item terpisah, benar, dan jika menemukan salah satu item tersebut, apa yang saya inginkan? "". Itu akan memberi tahu fungsi untuk tidak meletakkan apa pun di sana,). (= SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), “”))
Sekarang, kursor saya ada di ujung. Ketika saya menekan tombol F9, cukup yakin karena hanya ada 0, semua ini sama persis kecuali yang terakhir. Untuk yang terakhir, PENGGANTI menemukan 0 dan tidak meletakkan apa pun di tempatnya. Sekarang, CONTROL-Z, CONTROL-ENTER dan saya akan menyalinnya langsung ke sini, F2 dan F9. Jadi, jika kita pergi ke 6, ada 5 versi, menghapus 5 di sana, menghapus 6 di sana, dan menghapus 8 di sana, jadi akan ada 1, 2, 3 item berbeda yang berbeda. Hanya jika semua item sama persis dengan item asli, maka ia akan memberi tahu kita bahwa tidak ada digit. MELARIKAN DIRI.
Saya akan kembali ke puncak. Kedengarannya seperti, F2, ini adalah tes logika AND. Fungsi AND. Saya ingin memeriksa apakah setiap item dalam larik resultan itu = ke item asli. Jika semuanya benar, maka saya akan diberi tahu bahwa tidak ada nomor di cincin teks itu. ), CONTROL + ENTER, Saya mendapatkan FALSE karena salah satu dari mereka secara internal tidak memiliki 0. Saya akan menyalinnya di sini. Yang ini tentu saja akan mendapatkan TRUE - sama dengan ini - karena semua item yang dibuat secara internal, jika saya F2 di sini, semua ini, F9, persis sama dengan aslinya. MELARIKAN DIRI. Sekarang, saya naik ke atas. Ngomong-ngomong, saya tidak harus menggunakan CONTROL + SHIFT + ENTER karena saat Anda menggunakan konstanta array ini dalam rumus array Anda, maka Anda tidak perlu menggunakan CONTROL + SHIFT + ENTER. (= AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2))
Baik. Saya akan ke awal. Itu tes logis saya. Jika semua itu menjadi benar,, nilai jika benar, di "TIDAK,", jika tidak tolong beri tanda YA, "). CONTORL + ENTER. Klik dua kali itu. Saya harus mengklik dua kali yang ini dan mengirimkannya. Baik. Itu sedikit menyenangkan dengan SUBSTITUTE, konstanta array, tes logika AND, dan IF, tapi, saya beri tahu Anda, saya masih tidak percaya flash fill itu, bagaimana Anda menggunakannya, pada dasarnya, mengekstrak semua nomor dari itu. Baiklah, saya akan mengembalikannya kepada Anda, TuanExcel. (= IF (AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2), “No”, “Yes”))
Bill: Nah, rumus dengan SUBSTITUTE dan konstanta array dan AND, itu luar biasa. Saya harus menggunakan flash fill karena saya tidak bisa menemukan yang ini. Itu brilian. Sekarang, saya punya cara ketiga. Mari kita lihat itu.
Sekarang, inilah cara saya benar-benar menyelesaikan ini, hanya sedikit VBA. Jadi, saya melakukan ALT + F11 untuk beralih ke VBA, INSERT, MODULE, dan kemudian ketik kode ini. Kami akan membuat fungsi baru yang disebut HASNUMBERS dan kami akan meneruskannya ke nilai sel, dan kami akan memulai dengan kata ALPHA. Kami melihat setiap karakter, dan jika kode itu, jika (kode ASC - 06:35) dari karakter itu antara 48 hingga 57, maka kami mengatakan bahwa itu ANGKA, KELUAR FUNGSI, dan terus berjalan. Benar, jadi, ini akan mencari sampai menemukan digit. Ketika itu terjadi, itu mengembalikan HASNUMBERS. Jadi, di sini, kita akan mengatakan = HASNUMBERS, arahkan ke sel itu, dan klik dua kali untuk menyalinnya. Setiap kali melihat angka di sana, akan mendapatkan ANGKA, ALPHA, mudah untuk mengurutkannya. (= HasNumbers (A2))
Baiklah, rekap episode singkat. Sasaran: periksa kolom untuk melihat apakah ada digit dalam kode karakter, kode di sel. Saya menggunakan flash fill untuk menghapus digit, lalu fungsi panjang untuk melihat apakah berubah atau tidak. Mike memiliki rumus yang brilian, fungsi SUBSTITUTE dengan konstanta array. Anda tidak perlu CONTROL + SHIFT + ENTER. Anda harus menghapus satu digit sekaligus dan kemudian menggunakan fungsi DAN untuk melihat ke 10 hasil untuk melihat apakah masing-masing sama dengan item aslinya. Cara brilian untuk pergi, dan kemudian, fallback saya, gunakan fungsi VBA untuk memeriksa digit.
Baiklah. Saya ingin berterima kasih kepada semua orang karena telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari dan ExcelIsFun.
Unduh berkas
Unduh file sampel di sini: Duel186.xlsm
