Guarda il video - Come estrarre i numeri dalla stringa di testo in Excel (usando la formula e VBA)
Non esiste una funzione integrata in Excel per estrarre i numeri da una stringa in una cella (o viceversa: rimuovi la parte numerica ed estrai la parte di testo da una stringa alfanumerica).
Tuttavia, questo può essere fatto utilizzando un cocktail di funzioni di Excel o un semplice codice VBA.
Lascia che ti mostri prima di cosa sto parlando.
Supponiamo di avere un set di dati come mostrato di seguito e di voler estrarre i numeri dalla stringa (come mostrato di seguito):
Il metodo che scegli dipenderà anche dalla versione di Excel che stai utilizzando:
- Per le versioni precedenti a Excel 2016, è necessario utilizzare formule leggermente più lunghe
- Per Excel 2016, puoi utilizzare la nuova funzione TEXTJOIN
- Il metodo VBA può essere utilizzato in tutte le versioni di Excel
Clicca qui per scaricare il file di esempio
Estrai numeri da stringa in Excel (formula per Excel 2016)
Questa formula funzionerà solo in Excel 2016 poiché utilizza la funzione TEXTJOIN appena introdotta.
Inoltre, questa formula può estrarre i numeri che si trovano all'inizio, alla fine o al centro della stringa di testo.
Nota che la formula TEXTJOIN trattata in questa sezione ti darebbe tutti i caratteri numerici insieme. Ad esempio, se il testo è "Il prezzo di 10 biglietti è di 200 USD", il risultato sarà 10200.Supponiamo di avere il set di dati come mostrato di seguito e di voler estrarre i numeri dalle stringhe in ogni cella:
Di seguito è riportata la formula che ti darà una parte numerica da una stringa in Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDDIRECT("1:"&LEN(A2))),1)*1),""))
Questa è una formula di matrice, quindi è necessario utilizzare "Ctrl + Maiusc + Invio' invece di usare Invio.
Nel caso in cui non ci siano numeri nella stringa di testo, questa formula restituirà uno spazio vuoto (stringa vuota).
Come funziona questa formula?
Lasciami rompere questa formula e cercare di spiegare come funziona:
- ROW(INDIRECT(“1:”&LEN(A2))) - questa parte della formula darebbe una serie di numeri a partire da uno. La funzione LUNGHEZZA nella formula restituisce il numero totale di caratteri nella stringa. Nel caso di "The cost is USD 100", restituirà 19. Le formule diventerebbero quindi ROW(INDIRECT("1:19"). La funzione ROW restituirà quindi una serie di numeri - {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) - Questa parte della formula restituirebbe una matrice di #VALUE! errori o numeri in base alla stringa. Tutti i caratteri di testo nella stringa diventano #VALUE! errori e tutti i valori numerici rimangono così come sono. Ciò accade poiché abbiamo moltiplicato la funzione MID per 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) - Quando viene utilizzata la funzione IFERROR, rimuoverà tutti i #VALUE! errori e rimarrebbero solo i numeri. L'output di questa parte sarebbe simile a questo - {“”;””;””;””;””;””;””;””;””;””;””;””;””; ””;””;””;1;0;0}
- =TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) - La funzione TEXTJOIN ora combina semplicemente i caratteri della stringa quello rimane (che sono solo i numeri) e ignora la stringa vuota.
Scarica il file di esempio
Puoi anche usare la stessa logica per estrarre la parte di testo da una stringa alfanumerica. Di seguito è riportata la formula che otterrebbe la parte di testo dalla stringa:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDDIRECT("1:"&LEN (A2))),1),""))
Una piccola modifica in questa formula è che la funzione IF viene utilizzata per verificare se l'array che otteniamo dalla funzione MID è un errore o meno. Se è un errore, mantiene il valore altrimenti lo sostituisce con uno spazio vuoto.
Quindi TEXTJOIN viene utilizzato per combinare tutti i caratteri di testo.
Attenzione: Sebbene questa formula funzioni alla grande, utilizza una funzione volatile (la funzione INDIRETTO). Ciò significa che nel caso in cui lo utilizzi con un set di dati enorme, potrebbe essere necessario del tempo per ottenere i risultati. È meglio creare un backup prima di utilizzare questa formula in Excel.Estrai numeri da stringa in Excel (per Excel 2013/2010/2007)
Se hai Excel 2013. 2010. o 2007, non puoi usare la formula TEXTJOIN, quindi dovrai usare una formula complicata per farlo.
Supponiamo di avere un set di dati come mostrato di seguito e di voler estrarre tutti i numeri nella stringa in ogni cella.
La formula seguente otterrà questo risultato:
=SE(SOMMA(LEN(A2)-LEN(SOSTITUTO(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDDIRECT("$1:$"&LEN(A2))), 1))* RIGA(INDIRETTO("$1:$"&LUNGHEZZA(A2))),0), RIGA(INDIRETTO("$1:$"&LUNGHEZZA(A2))))+1,1) * 10^RIGA(INDIRETTO ("$1:$"&LEN(A2)))/10),"")
Nel caso in cui non sia presente alcun numero nella stringa di testo, questa formula restituirà vuoto (stringa vuota).
Sebbene questa sia una formula di matrice, tu non ho bisogno per usare 'Control-Shift-Enter' per usarlo. Un semplice invio funziona per questa formula.
Il merito di questa formula va al fantastico forum di Mr. Excel.
Di nuovo, questa formula estrarrà tutti i numeri nella stringa, indipendentemente dalla posizione. Ad esempio, se il testo è "Il prezzo di 10 biglietti è di 200 USD", il risultato sarà 10200.
Attenzione: Sebbene questa formula funzioni alla grande, utilizza una funzione volatile (la funzione INDIRETTO). Ciò significa che nel caso in cui lo utilizzi con un set di dati enorme, potrebbe essere necessario del tempo per ottenere i risultati. È meglio creare un backup prima di utilizzare questa formula in Excel.Separare testo e numeri in Excel utilizzando VBA
Se separare testo e numeri (o estrarre numeri dal testo) è qualcosa che devi fare spesso, puoi anche usare il metodo VBA.
Tutto ciò che devi fare è utilizzare un semplice codice VBA per creare una funzione definita dall'utente (UDF) personalizzata in Excel, quindi invece di utilizzare formule lunghe e complicate, usa quella formula VBA.
Lascia che ti mostri come creare due formule in VBA: una per estrarre i numeri e l'altra per estrarre il testo da una stringa.
Estrai numeri da stringa in Excel (usando VBA)
In questa parte, ti mostrerò come creare la funzione personalizzata per ottenere solo la parte numerica da una stringa.
Di seguito è riportato il codice VBA che utilizzeremo per creare questa funzione personalizzata:
Funzione GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Funzione di fine risultato
Ecco i passaggi per creare questa funzione e quindi utilizzarla nel foglio di lavoro:
- Vai alla scheda Sviluppatore.
- Fai clic su Visual Basic (puoi anche utilizzare la scorciatoia da tastiera ALT + F11)
- Nel backend di VB Editor che si apre, fai clic con il pulsante destro del mouse su uno qualsiasi degli oggetti della cartella di lavoro.
- Vai su Inserisci e fai clic su Modulo. Questo inserirà l'oggetto modulo per la cartella di lavoro.
- Nella finestra Codice modulo, copia e incolla il codice VBA sopra menzionato.
- Chiudi l'editor VB.
Ora sarai in grado di utilizzare la funzione GetText nel foglio di lavoro. Poiché abbiamo svolto tutto il lavoro pesante nel codice stesso, tutto ciò che devi fare è utilizzare la formula =GetNumeric(A2).
Questo ti darà immediatamente solo la parte numerica della stringa.
Si noti che poiché la cartella di lavoro ora contiene codice VBA, è necessario salvarla con l'estensione .xls o .xlsm.
Scarica il file di esempio
Se devi usare spesso questa formula, puoi anche salvarla nella tua cartella di lavoro macro personale. Ciò ti consentirà di utilizzare questa formula personalizzata in qualsiasi cartella di lavoro di Excel con cui lavori.
Estrai il testo da una stringa in Excel (usando VBA)
In questa parte, ti mostrerò come creare la funzione personalizzata per ottenere solo la parte di testo da una stringa.
Di seguito è riportato il codice VBA che utilizzeremo per creare questa funzione personalizzata:
Funzione GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Avanti i GetText = Funzione di fine risultato
Ecco i passaggi per creare questa funzione e quindi utilizzarla nel foglio di lavoro:
- Vai alla scheda Sviluppatore.
- Fai clic su Visual Basic (puoi anche utilizzare la scorciatoia da tastiera ALT + F11)
- Nel backend di VB Editor che si apre, fai clic con il pulsante destro del mouse su uno qualsiasi degli oggetti della cartella di lavoro.
- Vai su Inserisci e fai clic su Modulo. Questo inserirà l'oggetto modulo per la cartella di lavoro.
- Se hai già un modulo, fai doppio clic su di esso (non è necessario inserirne uno nuovo se lo possiedi già).
- Se hai già un modulo, fai doppio clic su di esso (non è necessario inserirne uno nuovo se lo possiedi già).
- Nella finestra Codice modulo, copia e incolla il codice VBA sopra menzionato.
- Chiudi l'editor VB.
Ora sarai in grado di utilizzare la funzione GetNumeric nel foglio di lavoro. Poiché abbiamo svolto tutto il lavoro pesante nel codice stesso, tutto ciò che devi fare è utilizzare la formula =GetText(A2).
Questo ti darà immediatamente solo la parte numerica della stringa.
Si noti che poiché la cartella di lavoro ora contiene codice VBA, è necessario salvarla con l'estensione .xls o .xlsm.
Se devi usare spesso questa formula, puoi anche salvarla nella tua cartella di lavoro macro personale. Ciò ti consentirà di utilizzare questa formula personalizzata in qualsiasi cartella di lavoro di Excel con cui lavori.
Nel caso in cui utilizzi Excel 2013 o versioni precedenti e non lo hai
