Abbiamo imparato come rimuovere i valori numerici da una cella in Excel 2016 e versioni precedenti. Le formule che abbiamo usato erano un po' complesse, ma ora Excel 2019 e 365 sono in gioco.
Excel 2019 e 365 introducono alcune nuove funzioni che semplificano il compito di rimuovere caratteri non numerici e recuperare solo valori numerici in una nuova cella. Utilizzeremo formule che possono aiutarci a farlo, in modo più conveniente.
Formula generica
| =TEXTJOIN("",VERO,SE ERRORE(MID(testo_confuso,SEQUENZA(NumCaratteri),1)+0,"")) |
Testo_confuso: Questo è il testo di origine da cui si desidera estrarre tutti i valori numerici.
NumCaratteri: Questo è il numero totale di caratteri che vuoi elaborare. Il jumbled_text non deve contenere più caratteri di questo numero (caratteri e numeri combinati).
Vediamo un esempio per chiarire le cose.
Esempio: rimuovi i caratteri non numerici ed estrai tutti i numeri
Quindi qui abbiamo del testo confuso. Questo testo contiene alcuni numeri e alcuni caratteri non numerici. Devo eliminare i caratteri non numerici e ottenere valori numerici solo nella colonna D.
Non mi aspetto che il numero totale di caratteri nel testo confuso sia superiore a 20. Quindi il valore di NumChars è 20 qui. Puoi aumentare questo numero se necessario.
Applicare qui la formula generica sopra per eliminare i caratteri non numerici.
| =TEXTJOIN("",VERO,SE ERRORE(MID(C3,SEQUENZA(20),1)+0,"")) |
E quando premi il pulsante Invio. Vengono rimossi tutti i caratteri non numerici. Trascina verso il basso questa formula per rimuovere i caratteri dalla stringa da tutte le celle nella colonna C3.
Come funziona?
Per prima cosa vediamo come viene risolta questa formula passo dopo passo.
| 1-> TEXTJOIN("",VERO,SE ERRORE(MID(C3,SEQUENZA(20),1)+0,"")) |
| 2-> TEXTJOIN("",VERO,SE ERRORE(MID("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",VERO,SE ERRORE({"1";"2";"a";"s";"w";"1";"2";"w";"1";"2";"3";"";"";"";"";"";"";"";"";""}+0,"")) |
| 4-> TEXTJOIN("",VERO,SE ERRORE({1;2;#VALORE!;#VALORE!;#VALORE!;1;2;#VALORE!;1;2;3;#VALORE!;#VALORE!;… ;#VALORE!}+0,"")) |
| 5-> TEXTJOIN("",VERO,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Come puoi vedere, la formula inizia a risolversi dall'interno. Dapprima viene risolta la funzione SEQUENZA. Poiché abbiamo superato 20. Restituisce un array di numeri che iniziano da 1 a 20.
Questo array viene servito alla funzione MID come numero iniziale. La funzione mid va a ciascun indice nella stringa e divide ogni carattere. Puoi vederlo nel passaggio 3.
Quindi aggiungiamo 0 a ciascun carattere. In Excel se provi ad aggiungere un numero a caratteri non numerici, risulta in #VALUE! Errore. Quindi otteniamo un array di numeri e #VALUE! Errori. I caratteri non numerici sono spariti ora.
La successiva funzione IFERROR sostituisce tutti gli errori #VALUE con "" (vuoto). Ora ci restano valori numerici e spazi.
Infine, questo array viene servito alla funzione TEXTJOIN. La funzione TEXTJOIN li concatena e otteniamo una stringa che contiene solo numeri.
Migliorare la Formula
La formula sopra ha utilizzato un numero codificato per elaborare il numero di caratteri (ne abbiamo presi venti). Ma potresti volere che sia dinamico. In tal caso, hai indovinato, utilizzeremo la funzione LEN. Ci vorrà un numero esatto di caratteri per l'elaborazione. Quindi la formula sarà.
| =TEXTJOIN("",TRUE,IFERROR(MID(testo_confuso,SEQUENCE(LEN(testo_confuso)),1)+0,"")) |
Qui, la funzione LEN rileverà automaticamente i caratteri numerici esatti nella stringa alfanumerica. Ciò alleggerirà l'onere di determinare il numero massimo di caratteri.
Alternativa alla funzione SEQUENZA
Se non si desidera utilizzare la funzione SEQUENCE, è possibile utilizzare una combinazione della funzione ROW e INDIRETTO per generare numeri sequenziali.
| =TEXTJOIN("",TRUE,IFERROR(MID(testo_confuso,RIGA(INDIRETTO("1:"&LEN(testo_confuso))),1)+0,"")) |
INDIRETTO convertirà il testo ("1:20") nell'intervallo effettivo e quindi la funzione RIGA elencherà tutti i numeri di riga da 1 a 20. (20 è solo un esempio. Può essere qualsiasi numero).
Quindi sì ragazzi, è così che puoi strappare i caratteri non numerici da una stringa alfanumerica in Excel. Spero di essere stato abbastanza esplicativo e che questo articolo ti abbia aiutato. In caso di domande su questo argomento o su qualsiasi altro argomento excel/VBA. Fino ad allora continua ad eccellere.
Dividi numeri e testo da stringa in Excel 2016 e versioni precedenti: Quando non avevamo la funzione TEXTJOIN, usavamo le funzioni LEFT e RIGHT con alcune altre funzioni per dividere i caratteri numerici e non numerici da una stringa.
Estrai il testo da una stringa in Excel utilizzando la funzione SINISTRA e DESTRA di Excel: Per rimuovere il testo in excel dalla stringa, possiamo usare le funzioni LEFT e RIGHT di excel. Queste funzioni ci aiutano a tagliare le stringhe in modo dinamico.
Rimuovi gli spazi iniziali e finali dal testo in Excel: Gli spazi iniziali e finali sono difficili da riconoscere visivamente e possono rovinare i dati. L'eliminazione di questi caratteri dalla stringa è un'attività fondamentale e più importante nella pulizia dei dati. Ecco come puoi farlo facilmente in Excel.
Rimuovi caratteri da destra: Per rimuovere i caratteri dalla destra di una stringa in Excel, usiamo la funzione SINISTRA. Sì, la funzione SINISTRA. La funzione SINISTRA mantiene il numero di caratteri specificato da SINISTRA e rimuove tutto dalla sua destra.
Rimuovi i caratteri indesiderati in Excel: Per rimuovere i caratteri indesiderati da una stringa in Excel, utilizziamo la funzione SOSTITUISCI. La funzione SOSTITUISCI sostituisce i caratteri dati con un altro carattere dato e produce una nuova stringa modificata.
Come rimuovere il testo in Excel a partire da una posizione in Excel: Per rimuovere il testo da una posizione iniziale in una stringa, utilizziamo la funzione SOSTITUISCI di Excel. Questa funzione ci aiuta a determinare la posizione iniziale e il numero di caratteri da rimuovere.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come utilizzare la funzione CERCA.VERT di Excel| Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come usare Excel Funzione CONTA.SE| Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.