Non esiste una funzione incorporata in Excel per estrarre i numeri da una stringa in una cella (e viceversa: rimuovere la parte numerica ed estrarre la parte di testo da una stringa alfanumerica).
Tuttavia, questo può essere fatto utilizzando una funzione di Excel o un semplice codice VBA.
Lascia che ti dica prima di cosa sto parlando.
Supponiamo di avere un set di dati come di seguito e di voler estrarre i numeri dalle stringhe (come di seguito):
Il metodo scelto dipende anche dalla versione di Excel in uso:
- Per le versioni di Excel precedenti al 2016, è necessario utilizzare una formula leggermente più lunga
- Per Excel 2016 è possibile utilizzare la nuova funzione TEXTJOIN
- Metodo VBA disponibile per tutte le versioni di Excel
Contenuti
Estrai numeri dalle stringhe in Excel (formule di Excel 2016)
Questa formula funziona solo in Excel 2016 perché utilizza la funzione TEXTJOIN appena introdotta.
Inoltre, questa formula può estrarre numeri all'inizio, alla fine o al centro di una stringa di testo.
Si noti che la formula TEXTJOIN descritta in questa sezione mette insieme tutti i caratteri numerici.Ad esempio, se il testo è "10 biglietti sono $ 200", il risultato è 10200.
Supponiamo di avere un set di dati come il seguente e di voler estrarre i numeri dalle stringhe in ogni cella:
La formula seguente ti darà la parte numerica di una stringa in Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Questa è una formula di matrice, quindi è necessario utilizzare " Ctrl + Maiusc + Invio " invece di usare Invio.
Questa formula restituirà uno spazio vuoto (stringa vuota) se non ci sono numeri nella stringa di testo.
Come funziona questa formula?
Permettetemi di analizzare questa formula e provare a spiegare come funziona:
- ROW(INDIRECT(“1:”&LEN(A2))) – Questa parte della formula darà una serie di numeri a partire da uno.La funzione LEN nella formula restituisce il numero totale di caratteri nella stringa.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {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 restituirà una matrice di #VALORI!Errori o numeri basati su stringhe.Tutti i caratteri di testo nella stringa diventano #VALUE!Gli errori e tutti i valori rimangono così come sono.Questo accade quando moltiplichiamo la funzione MID per 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Quando si utilizza la funzione IFERROR, tutti i #VALUE vengono rimossi!Errore, rimarranno solo i numeri.L'output di questa parte è 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 stringa Ancora lì (solo numeri) e ignora le stringhe vuote.
Suggerimento per professionisti:Se si desidera controllare l'output di una formula parziale, selezionare la cella, premere F2 per accedere alla modalità di modifica, selezionare la parte della formula che si desidera emettere e premere F9.Vedrai immediatamente i risultati.Quindi ricorda di premere Control + Z o premere Esc.Non premere invio.
Puoi anche utilizzare la stessa logica per estrarre parti di testo da stringhe alfanumeriche.Ecco la formula per ottenere la parte di testo dalla stringa:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Una piccola modifica a questa formula è che la funzione SE viene utilizzata per verificare la presenza di errori nell'array che otteniamo dalla funzione MID.Se si tratta di un errore, mantenere il valore, altrimenti sostituirlo con uno spazio vuoto.
Quindi usa TEXTJOIN per combinare tutti i caratteri di testo.
注意: Sebbene questa formula sia valida, utilizza una funzione volatile (la funzione INDIRETTO).Ciò significa che se lo stai utilizzando con un enorme set di dati, potrebbe essere necessario del tempo per ottenere risultati.È meglio creare un backup prima di utilizzare questa formula in Excel.
Estrarre numeri da stringhe in Excel (per Excel 2013/2010/2007)
Se hai Excel 2013. 2010. o 2007, non puoi usare le formule TEXTJOIN, quindi devi usare formule complesse per farlo.
Supponiamo di avere un set di dati come di seguito e di voler estrarre tutti i numeri nelle stringhe in ogni cella.
La formula seguente farà questo:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Questa formula restituirà uno spazio vuoto (stringa vuota) se non ci sono numeri nella stringa di testo.
Sebbene questa sia una formula di matrice, tuNon serveUsa "Control-Shift-Enter" per usarlo.Un semplice input funziona per questa formula.
Il merito di questa formula va al fantasticoSignor Excel Forum.
Anche in questo caso, questa formula estrarrà tutti i numeri nella stringa indipendentemente dalla posizione.Ad esempio, se il testo è "10 biglietti sono $ 200", il risultato è 10200.
注意: Sebbene questa formula sia valida, utilizza una funzione volatile (la funzione INDIRETTO).Ciò significa che se lo stai utilizzando con un enorme set di dati, potrebbe essere necessario del tempo per ottenere risultati.È meglio creare un backup prima di utilizzare questa formula in Excel.
Separa testo e numeri in Excel con VBA
Se hai spesso bisogno di separare testo e numeri (o estrarre numeri dal testo), puoi anche utilizzare il metodo VBA.
Tutto quello che devi fare è creare una funzione definita dall'utente (UDF) personalizzata in Excel utilizzando un semplice codice VBA, quindi utilizzare quella formula VBA invece di una formula lunga e complessa.
Lascia che ti mostri come creare due formule in VBA: una per estrarre i numeri e una per estrarre il testo dalle stringhe.
Estrai i numeri dalle stringhe in Excel (usando VBA)
In questa parte, ti mostrerò come creare una funzione personalizzata per ottenere solo la parte numerica da una stringa.
Ecco il codice VBA che useremo 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 in un foglio di lavoro:
Ora sarai in grado di utilizzare la funzione GetText sul foglio di lavoro.Dal momento che abbiamo già svolto tutto il lavoro pesante nel codice stesso, tutto ciò che devi fare è usare la formula =GetNumeric(A2).
Questo ti darà immediatamente la parte numerica della stringa.
Si noti che poiché la cartella di lavoro ora contiene codice VBA, è necessario salvarlo con un'estensione .xls o .xlsm.
Se è necessario utilizzare frequentemente questa formula, è anche possibile salvarla nella cartella di lavoro macro personale.Ciò ti consentirà di utilizzare questa formula personalizzata in qualsiasi cartella di lavoro di Excel che utilizzi.
Estrai il testo dalla stringa in Excel (usando VBA)
In questa parte, ti mostrerò come creare una funzione personalizzata per ottenere solo la parte di testo da una stringa.
Ecco il codice VBA che useremo 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 = Risultato Funzione di fine
Ecco i passaggi per creare questa funzione e quindi utilizzarla in un foglio di lavoro:
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 è usare la formula =GetText(A2).
Questo ti darà immediatamente la parte numerica della stringa.
Si noti che poiché la cartella di lavoro ora contiene codice VBA, è necessario salvarlo con un'estensione .xls o .xlsm.
Se è necessario utilizzare frequentemente questa formula, è anche possibile salvarla nella cartella di lavoro macro personale.Ciò ti consentirà di utilizzare questa formula personalizzata in qualsiasi cartella di lavoro di Excel che utilizzi.
