Come rimuovere i numeri da alfanumerica stringa in Excel
Abbiamo imparato come link: / excel-text-formas-split-numbers-and-text-from-string-in-excel [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, intendo le funzioni. Excel 2019 e 365 introducono alcune nuove funzioni (TEXTJOIN e SEQUENCE)
che può facilitare il compito di rimuovere i caratteri numerici e recuperare solo i valori non numerici in una nuova cella. Useremo formule che possono aiutarci a farlo, in modo più conveniente. Generic Formula
|
= |
Jumbled_text: questo è il testo di origine da cui si desidera estrarre tutti i valori numerici.
NumChars: questo è il numero totale di caratteri che desideri elaborare.
Il jumbled_text non dovrebbe contenere più caratteri di questo numero (caratteri e numerici 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 sbarazzarmi dei 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.
Applica qui la formula generica sopra per eliminare i caratteri numerici.
|
= |
Quando si preme il pulsante Invio, vengono rimossi tutti i caratteri numerici 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 questa formula viene risolta passo dopo passo.
|
1→
|
|
2→
is…site”,\{1,2,3,…100},1)+0), |
|
3→ |
|
4→
|
|
5→
|
|
6→
|
|
7→ “This is number one website” |
Per facilità di lettura non ho scritto l’intero array. Ho usato i punti (…) per indicare array lunghi.
Come puoi vedere, la formula inizia a risolvere dall’interno. All’inizio il link: / excel-365-functions-the-sequence-function-in-excel [SEQUENCE function] è risolto. Poiché abbiamo superato 100 come numero di caratteri. Restituisce un array di numeri che vanno da 1 a 100.
Questo array viene servito alla funzione MID come numero iniziale. La funzione mid va a ogni indice in stringa e divide ogni carattere in un array. Puoi vederlo nel passaggio 3. Non ho mostrato l’intero array poiché occuperà troppo spazio. \ {“T”; “h”; “i”; “s”; “”, “1” … “”; “”} * Successivamente aggiungiamo 0 a ogni carattere. In Excel, se provi ad aggiungere un numero a caratteri non numerici, il risultato sarà `link: / tips-value-error-and-how-to-fix-it-in-excel [#VALUE!
Errore] `. Quindi otteniamo un array di numeri e #VALORE! Errori.
\ {# VALUE!; # VALUE!; # VALUE!; # VALUE!; 1…; #VALUE!}
Questo array è servito al link: / excel-formula-and-function-iserror-function [ISERROR]
funzione. Come sapete, la funzione ISERROR restituisce TRUE per gli errori e FALSE per i valori non di errore. Quindi otteniamo un array di TRUE e FALSE.
VERO per i caratteri non numerici e FALSO per i numeri.
\ {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
La stessa cosa accade alle istruzioni di sezione TRUE di IF (link: / excel-text-formas-the-mid-function-in-excel [MID] (B3, `link: / excel-365-functions-the -funzione-sequenza-in-Excel [SEQUENCE] ` (100), 1) ).
Restituisce un array di tutti i caratteri di una stringa alfanumerica in B3.
Ora per ogni istruzione TRUE, la funzione IF restituisce il carattere corrispondente dall’array della sezione true. Per FALSO, SE restituisce vuoto (“”). Ora avrai un array che non contiene alcun carattere numerico. \{“Questo”;””….;””}. Infine, questo array è servito da link: / excel-365-functions-the-excel-textjoin-function [TEXTJOIN *]
funzione. Questa funzione unisce i testi dati tra loro, ignorando i valori vuoti, con un dato delimitatore. Quindi otteniamo una stringa che non contiene caratteri numerici. Questo è il sito web numero uno *.
Migliorare la formula
La formula sopra utilizza un numero hardcoded per l’elaborazione del 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à.
|
= |
Qui, la funzione LEN rileverà automaticamente i caratteri numerici esatti nella stringa alfanumerica. Questo alleggerirà l’onere di determinare il numero massimo di personaggi.
Alternativa alla funzione SEQUENCE
Se non si desidera utilizzare la funzione SEQUENCE, è possibile utilizzare una combinazione di ROW e INDIRECT per `link: / information-formas-how-to-get-sequential-row-number-in-excel [generazione di numeri sequenziali] “.
|
= |
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 questo articolo ti ha aiutato. Se hai domande su questo argomento o su qualsiasi altro argomento Excel / VBA. Fino ad allora continua a eccellere.
Articoli correlati:
Come rimuovere i caratteri non numerici dalle celle in Excel 2019: Per rimuovere i caratteri non numerici da una stringa alfanumerica in Excel, usiamo 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 Split Numbers and Text from String in Excel 2016 and Older: When we didn ‘ Per avere la funzione TEXTJOIN abbiamo usato le funzioni LEFT e RIGHT con alcune altre funzioni per dividere i caratteri numerici e non numerici da una stringa. Estrai testo da una stringa in Excel utilizzando la funzione SINISTRA e DESTRA di Excel: Per rimuovere il testo in Excel dalla stringa possiamo utilizzare le funzioni SINISTRA e DESTRA di Excel. Queste funzioni ci aiutano a tagliare le stringhe dinamicamente. \ Rimuovi spazi iniziali e finali dal testo in Excel: * Gli spazi iniziali e finali sono difficili da riconoscere visivamente e possono rovinare i tuoi dati. La rimozione di questi caratteri dalla stringa è un’attività fondamentale e più importante nella pulizia dei dati. Ecco come puoi farlo facilmente in Excel.
link: / excel-text-formas-remove-characters-from-right [Remove Characters From Right]: * Per rimuovere i caratteri dalla destra di una stringa in Excel, usiamo la funzione LEFT. Sì, la funzione SINISTRA. La funzione LEFT mantiene il numero di caratteri dato da LEFT e rimuove tutto dalla sua destra.
link: / excel-text-formas-remove-indesiderated-characters-in-excel [Rimuovi caratteri indesiderati in Excel]: * Per rimuovere caratteri indesiderati da una stringa in Excel, usiamo la funzione SOSTITUISCI. La funzione SOSTITUISCI sostituisce i caratteri dati con un altro carattere e produce una nuova stringa modificata.
link: / excel-text-formas-how-to-remove-text-in-excel-starting-from-a-position [Come rimuovere il testo in Excel partendo da una posizione in Excel]: * Per rimuovere il testo da una posizione iniziale in una stringa, usiamo la funzione REPLACE di Excel. Questa funzione ci aiuta a determinare la posizione iniziale e il numero di caratteri da eliminare.
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività] | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT di Excel] | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da diversi intervalli e fogli. link: / tips-countif-in-microsoft-excel [Come usare]
link: / formule-e-funzioni-introduzione-della-funzione-vlookup [Excel]
link: / tips-countif-in-microsoft-excel [Funzione CONTA.SE] | Contare i valori con le condizioni utilizzando questa straordinaria funzione.
Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel] | Questa è un’altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.