Trova qualsiasi cifra - Suggerimenti per Excel

Excel: come mantenere solo le cifre di una cella, non le lettere.

Guarda un video

Controlla una colonna per vedere se ci sono cifre nel codice
Metodo di fatturazione 1:
Flash Fill
Metodo Mike:
Utilizzare la funzione SOSTITUISCI con una costante di matrice.
Non sarà necessario utilizzare Ctrl + Maiusc + Invio perché è una costante di matrice
Questo rimuoverà una cifra alla volta
Utilizzare la funzione AND per vedere se ogni elemento nella matrice risultante è uguale all'elemento originale
Metodo di fatturazione 3:
Usa una funzione VBA per controllare le cifre

Trascrizione del video

Bill: Hey. Ben tornato. È il momento per un altro Podcast Dueling Excel. Sono Bill Jelen di. Sarò raggiunto da Mike Girvin di ExcelIsFun. Questo è il nostro episodio 186: sì, se ci sono cifre nella cella. La domanda di oggi viene inviata da Jen, ha 13.000 righe di dati, deve guardare attraverso una cella, se un carattere è una cifra, contrassegnala come Sì, altrimenti No. Va bene. Bene, Mike, spero che tu abbia un modo fantastico per farlo perché io no.

Userò il riempimento flash e, nel riempimento flash, gli darò uno schema qui con alcune lettere e numeri. Voglio assicurarmi di includere tutti i numeri possibili solo in modo che capisca cosa sto facendo, e 0 così. Quindi, ci sono i dati originali, e poi farò correggere il riempimento flash per me e, nella versione fissa, ci sbarazzeremo di tutte le cifre. Quindi, cercherò di vedere se c'è una cifra. Se c'è, sbarazzarsene in questo modo, quindi premere CONTROL + E per riempire a flash, e quello che dovremmo avere ora sono tutte solo le lettere, solo le lettere.

E poi la domanda è: è cambiato? Quindi = SE questo è = a quello che stiamo cercando, significa che non ci sono stati cambiamenti, nessuna cifra, quindi stiamo dicendo No, altrimenti Sì, così, e fai doppio clic, copialo, va bene, e quindi qualsiasi cosa con un No non ha cifre lì. Ci sbarazziamo della nostra riga originale e, una volta copiata, CONTROL + C, ALT + E, S, V, e possiamo eliminare il riempimento flash. Va bene, Mike. Vediamo cosa hai. (= SE (A2 = B2, "No", "Sì"))

Mike: Wow ,. Questo deve essere l'uso creativo unico più sorprendente del riempimento flash che penso di aver mai visto. Hai inventato un testo, hai estratto i numeri effettivi, CONTROL + E, e ha immediatamente estratto tutti i numeri e ha consegnato una nuova stringa di testo senza i numeri, quindi hai fatto il tuo IF. Assolutamente bello.

Tutto a posto. Verrò a questo foglio proprio qui e userò la funzione SOSTITUISCI. Ora, SOSTITUISCI, gli dirò di guardare quel testo proprio lì, e il VECCHIO_TEXT che voglio trovare e rimuovere, beh, sono tutte cifre. Quindi, creerò una costante di matrice (1, 2, 3, 4, 5 tutte le cifre e). Questa è una costante di matrice e si trova in OLD_TEXT perché non sto inserendo un singolo elemento ma invece metto un gruppo di elementi. Questa è un'operazione su array di argomenti di una funzione. Ci sono 10 diversi articoli qui che istruiranno SUBSTITUTE a consegnare 10 articoli separati, correttamente, e se trova uno di questi articoli, cosa voglio? "". Questo dirà alla funzione di non inserire nulla,). (= SOSTITUISCE (A2, (1,2,3,4,5,6,7,8,9,0), “”))

Ora, il mio cursore è alla fine. Quando premo il tasto F9, abbastanza sicuro perché c'è solo uno 0, tutti questi sono esattamente uguali tranne l'ultimo. Per l'ultimo, il SOSTITUTO ha trovato lo 0 e non ha messo niente al suo posto. Ora, CONTROL-Z, CONTROL-INVIO e lo copierò proprio qui, F2 e F9. Quindi, se andiamo al 6, c'è la versione 5, ha rimosso il 5 lì, ha rimosso il 6 lì e ha rimosso l'8 lì, quindi ci saranno 1, 2, 3 elementi diversi che sono diversi. Solo quando tutti gli articoli sono esattamente uguali all'articolo originale ci dirà che non ci sono cifre. FUGA.

Torno in cima. Suona come, F2, questo è un test logico AND. E funzione. Voglio verificare se ognuno di quegli elementi in quella matrice risultante è = all'elemento originale. Quando sono tutte vere, mi dirà che non ci sono numeri in quel testo. ), CTRL + INVIO, ottengo un FALSO perché uno di loro internamente manca quello 0. Lo copierò qui sotto. Questo ovviamente avrà un VERO - lo stesso con questi - perché tutti gli elementi generati internamente, se F2 proprio qui, tutti questi, F9, sono esattamente uguali all'originale. FUGA. Ora arrivo in cima. A proposito, non ho dovuto usare CONTROL + MAIUSC + INVIO perché quando si utilizza questa costante di matrice nella formula di matrice, non è necessario utilizzare CONTROL + MAIUSC + INVIO. (= AND (SOSTITUISCE (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2))

Tutto a posto. Vengo all'inizio. Questo è il mio test logico. Se tutti questi risultati risultano essere veri,, valore se vero, in "NO", altrimenti si prega di inserire un SI "). CONTORL + INVIO. Fare doppio clic su di esso. Dovrò fare doppio clic su questo e inviarlo. Tutto a posto. È stato un po 'divertente con SUBSTITUTE, una costante di matrice, un test logico AND e IF, ma, ti dico una cosa, non riesco ancora a credere a quel riempimento flash, come l'hai usato, in sostanza, per estrarre tutto il numeri da quello. Va bene, te lo restituisco, MrExcel. (= SE (E (SOSTITUISCE (A2, (1,2,3,4,5,6,7,8,9,0), "") = A2), "No", "Sì"))

Bill: Bene, quella formula con SOSTITUISCI e la costante di matrice e AND, è assolutamente incredibile. Ho dovuto usare il riempimento flash perché non avrei potuto capire questo. È geniale. Ora ho una terza via. Diamo un'occhiata a questo.

Ora, ecco il modo in cui lo risolverei davvero, solo un po 'di VBA. Quindi, faccio ALT + F11 per passare a VBA, INSERT, MODULE e quindi digito questo codice. Creeremo una nuova funzione chiamata HASNUMBERS e la passeremo al valore della cella, e inizieremo con la parola ALPHA. Guardiamo ogni singolo carattere, e se quel codice, se il (codice ASC - 06:35) di quel carattere è compreso tra 48 e 57, allora diciamo che è NUMERI, FUNZIONE DI USCITA e continuiamo. Bene, quindi, guarda finché non trova una cifra. Quando lo fa, restituisce HASNUMBERS. Quindi, qui, diremo = HASNUMBERS, puntare a quella cella e fare doppio clic per copiarla. Ogni volta che vede una cifra laggiù, otterrà i NUMERI, ALPHA, facili da sistemare. (= HasNumbers (A2))

Va bene, breve riepilogo dell'episodio. Obiettivo: controlla la colonna per vedere se ci sono cifre nel codice carattere, il codice nella cella. Ho usato il riempimento flash per rimuovere le cifre, quindi la funzione di lunghezza per vedere se è cambiata o meno. Mike aveva una formula brillante, la funzione SOSTITUISCI con una costante di matrice. Non hai bisogno di CONTROL + MAIUSC + INVIO. Devi rimuovere una cifra alla volta e quindi utilizzare la funzione AND per esaminare tutti i 10 risultati per vedere se ognuno è uguale all'elemento originale. Brillante modo di procedere, e poi, il mio fallback, usa una funzione VBA per controllare le cifre.

Beh ciao. Voglio ringraziare tutti per essere passati. Ci vediamo la prossima volta per un altro netcast di ed ExcelIsFun.

Download file

Scarica il file di esempio qui: Duel186.xlsm