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 con nuovi giocattoli, voglio dire funzioni.
Excel 2019 e 365 introducono alcune nuove funzioni (TEXTJOIN e SEQUENCE) che possono facilitare il compito di rimuovere caratteri numerici e recuperare solo valori non numerici in una nuova cella. Utilizzeremo formule che possono aiutarci a farlo, in modo più conveniente.
Formula generica
| =TEXTJOIN("",VERO,SE(ISERROR(MID(testo_confuso,SEQUENZA(NumCaratteri),1)+0),MID(testo_confuso,SEQUENZA(NumCaratteri),1),""))) |
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: rimuovere i caratteri numerici ed estrarre tutti gli alfabeti e i caratteri non numerici.
Quindi qui abbiamo del testo alfanumerico. Questo testo contiene alcuni numeri e alcuni caratteri non numerici. Devo eliminare i caratteri numerici e ottenere solo alfabeti e altri valori di caratteri, nella colonna D.
Non mi aspetto che il numero totale di caratteri nel testo confuso sia superiore a 100. Quindi il valore di NumChars è 100 qui. Puoi aumentare o diminuire questo numero se necessario.
Applicare qui la formula generica sopra per eliminare i caratteri numerici.
| =TEXTJOIN("",VERO,SE(ISERROR(MID(B3,SEQUENZA(100),1)+0),MID(B3,SEQUENZA(100),1),""))) |
Quando premi il pulsante Invio, tutti i caratteri numerici vengono rimossi e rimangono solo i valori alfabetici. Trascina verso il basso questa formula per rimuovere i numeri dalla stringa da tutte le celle nella colonna B.
Come funziona?
Per prima cosa vediamo come viene risolta questa formula passo dopo passo.
| 1-> TEXTJOIN("",VERO,SE(ISERROR(MID(B3,SEQUENZA(100),1)+0),MID(B3,SEQUENZA(100),1),""))) |
| 2-> TEXTJOIN("",VERO,SE(ISERROR(MID("Questo1 è… sito",{1,2,3,… 100},1)+0),MID(B3,SEQUENZA(100),1),""))) |
| 3-> TEXTJOIN("",VERO,SE(ISERROR({"T";"h";"i";"s";" ","1"… "";""}+0),MID(B3,SEQUENZA(100),1),""))) |
| 4-> TEXTJOIN("",VERO,SE(ISERROR({#VALORE!;#VALORE!;#VALORE!;#VALORE!;1…;#VALORE!}),MID(B3,SEQUENZA(100),1),""))) |
| 5-> TEXTJOIN("",VERO,SE({VERO;VERO;VERO;VERO;FALSO… ;VERO},MID(B3,SEQUENZA(100),1),""))) |
| 6-> TEXTJOIN("",VERO,{"Questo";""… .;""}) |
| 7-> "Questo è il sito numero uno" |
Per facilità di lettura non ho scritto l'intero array. Ho usato i punti (… ) per indicare gli array lunghi.
Come puoi vedere, la formula inizia a risolversi dall'interno. Dapprima viene risolta la funzione SEQUENZA. Dal momento che abbiamo superato 100 come numero di caratteri. Restituisce un array di numeri a partire da 1 a 100.
Questo array viene servito alla funzione MID come numero iniziale. La funzione mid va a ciascun indice nella stringa e divide ogni carattere in un array. Puoi vederlo nel passaggio 3. Non ho mostrato l'intero array in quanto richiederà troppo spazio. {"T";"h";"i";"s";" ","1"… "";""}
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. {#VALORE!;#VALORE!;#VALORE!;#VALORE!;1…;#VALORE!}
Questo array è servito al ISERROR funzione. Come sai, la funzione VAL.ERRORE restituisce VERO per gli errori e FALSO per i valori non di errore. Quindi otteniamo un array di VERO e FALSO. VERO per i caratteri non numerici e FALSO per i numeri. {VERO;VERO;VERO;VERO;FALSO…;VERO}.
La stessa cosa accade alle istruzioni della sezione TRUE di IF (MID(B3,SEQUENZA(100),1)). Restituisce un array di tutti i caratteri della stringa alfanumerica in B3.
Ora per ogni istruzione VERO, la funzione SE restituisce il carattere corrispondente dall'array della sezione vera. Per FALSE, SE restituisce uno spazio vuoto (""). Ora avrai un array che non contiene alcun carattere numerico. {"Questo";""… .;""}.
Infine, questo array è servito da TEXTJOIN funzione. Questa funzione unisce tra loro testi dati, ignorando i valori degli spazi vuoti, con un determinato delimitatore. Quindi otteniamo una stringa che non contiene caratteri numerici. Questo è il sito numero uno.
Migliorare la Formula
La formula sopra utilizza un numero codificato per elaborare il numero di caratteri (abbiamo preso 100). Ma potresti volere che sia dinamico. In tal caso, hai indovinato, puoi usare la funzione LEN. Ci vorrà il numero esatto di caratteri per l'elaborazione. Quindi la formula sarà.
| =TEXTJOIN("",TRUE,IF(ISERROR(MID(testo_confuso,SEQUENCE(LEN(testo_confuso),1)+0),MID(testo_confuso,SEQUENCE(LEN(testo_confuso),1),""))) |
Qui, la funzione LEN rileverà automaticamente i caratteri numerici esatti nella stringa alfanumerica. Questo alleggerirà dall'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,IF(ISERROR(MID(testo_confuso,RIGA(INDIRETTO("1:"&LEN(NumCaratteri))),1)+0),MID(testo_confuso,RIGA(INDIRETTO("1:"&LEN(NumCaratteri))),1),""))) |
INDIRETTO convertirà il testo ("1:100") nell'intervallo effettivo e quindi la funzione RIGA elencherà tutti i numeri di riga da 1 a 100. (100 è 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.
Come rimuovere i caratteri non numerici dalle celle in Excel 2019: Per rimuovere caratteri non numerici da una stringa alfanumerica in Excel, utilizziamo la nuova funzione TEXTJOIN. La striscia di caratteri non numerici da una stringa può aiutarci a ripulire i nostri dati per una migliore analisi dei dati. Quindi ecco come si fa
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.