Come estrarre un numero da una stringa in Excel: scegliere l’approccio giusto

Quando pulisci i dati in Excel, potresti dover estrarre numeri da stringhe alfanumeriche. Questo problema è comune quando i numeri sono incorporati nelle stringhe, ad esempio in codici prodotto o risposte a sondaggi.

In questo articolo ti mostro diversi metodi per estrarre numeri dalle stringhe usando formule come RIGHT(), LEN() e FIND() in vari casi d’uso. Se non conosci alcune basi, ti consiglio di seguire il nostro corso Introduction to Excel per costruire solide fondamenta in Excel. Dai anche un’occhiata a Data Preparation in Excel per imparare a trasformare dati grezzi.

Formule Excel per estrarre numeri da una stringa

Excel offre diversi metodi basati su formule per estrarre numeri dalle stringhe. Ogni metodo è adatto a esigenze diverse, a seconda di dove si trova il numero nella stringa. Se hai poco tempo, vai pure alla sezione che risolve il tuo bisogno più immediato.

Estrarre numeri dall’inizio di una stringa in Excel

A volte i numeri si trovano all’inizio della stringa. In questi casi, userai LEFT() e FIND() per estrarre i numeri dalla stringa. Combinando le funzioni in una formula, i numeri vengono estratti con la seguente logica:

• FIND(): trova la posizione della prima lettera nella stringa.

• LEFT(): estrae i caratteri dall’inizio della stringa fino alla posizione della prima lettera meno uno.

La prima parte della formula qui sotto trova la prima lettera nella stringa. Poi, la seconda parte estrae tutto ciò che la precede, lasciando solo i numeri iniziali. Se non ci sono lettere, restituisce l’intera stringa.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Estrazione di numeri dall’inizio di una stringa in Excel. Immagine dell’autore.

Questo metodo è adatto quando lavori con dataset che usano comunemente prefissi numerici, come numeri d’ordine, ID membri e numeri di lotto.

Estrarre numeri dalla fine di una stringa in Excel

Se vuoi estrarre numeri dalla fine di una stringa in Excel, ti serviranno le funzioni RIGHT(), LEN() e FIND(). Supponiamo di avere i seguenti dati nel foglio Excel. Combinerai le funzioni per estrarre il numero dove:

• FIND(): trova la posizione della prima cifra nella stringa.

• LEN(): calcola il numero di caratteri dalla prima cifra alla fine della stringa.

• RIGHT(): estrae i caratteri dalla prima cifra fino alla fine della stringa.

La formula seguente rimuove tutto il testo iniziale e restituisce il primo numero insieme a tutto ciò che lo segue nella stringa.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Estrazione di numeri dalla fine di una stringa in Excel. Immagine dell’autore.

Questo metodo è particolarmente utile in dataset con caratteri alfanumerici coerenti, come SKU di prodotti, ID d’ordine e numeri di serie.

Estrarre numeri in qualsiasi posizione di una stringa in Excel

Se hai un dataset in cui i numeri possono comparire ovunque nella stringa, ti serviranno formule avanzate con TEXTJOIN(), MID() e ROW() per estrarli.

Combinando queste funzioni in una formula, estrarrai un numero da una stringa seguendo questa logica: 

• ROW() e MID(): scorrono ciascun carattere della stringa per verificare se è numerico.

• IFERROR(): test logico per isolare i numeri individuati dagli altri caratteri.

• TEXTJOIN(): combina tutti i caratteri numerici estratti in un’unica stringa di numeri.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Estrarre numeri in qualsiasi posizione in una stringa. Immagine dell’autore.

Questo metodo è ideale quando il dataset ha caratteri alfanumerici con numeri che possono apparire ovunque nella stringa. Nota: non tutte queste funzioni sono disponibili nelle versioni più vecchie di Excel. Dai un’occhiata al nostro percorso di abilità Excel Fundamentals per saperne di più su funzioni e formule disponibili nelle versioni più recenti.

Estrarre numeri filtrando i caratteri indesiderati

A volte i dati sono disordinati. Per fortuna puoi combinare le funzioni SUBSTITUTE() e TEXTJOIN(). Segui questi passaggi:

• SUBSTITUTE(): la funzione SUBSTITUTE() può essere usata per sostituire tutti i caratteri non numerici in una stringa con un delimitatore, come uno spazio o una stringa vuota. Per esempio, puoi sostituire lettere e caratteri speciali con spazi.

• TEXTJOIN(): la funzione TEXTJOIN() può poi concatenare i caratteri rimanenti (che ora dovrebbero essere solo numeri) in un’unica stringa.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Estrazione di numeri da una stringa filtrando i caratteri. Immagine dell’autore.

Quando potresti aver bisogno di estrarre numeri da una stringa in Excel

Vediamo ora due piccoli casi di studio per fare pratica con l’estrazione di numeri dalle stringhe.

Esempio 1: estrarre numeri da codici prodotto

Supponiamo che tu gestisca un inventario prodotti in cui ogni articolo è identificato da un codice univoco. Questi codici sono stringhe alfanumeriche come "PROD1234", "ITEM5678" o "SKU91011".

Per estrarre l’ID prodotto numerico dai codici alfanumerici, usiamo funzioni basate sulla posizione del numero. Ricorda: se i numeri sono alla fine, applica RIGHT(), LEN() e FIND(). Se compaiono all’inizio, usa LEFT() e FIND().

L’esempio seguente estrae la parte numerica dei codici prodotto dal lato destro del codice.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Estrarre numeri dai codici prodotto in Excel. Immagine dell’autore.

Esempio 2: dati di sondaggi con numeri incorporati

I dati dei sondaggi a volte contengono sia testo sia numeri, come "Voto 5 su 10", "Punteggio: 8" o "3 stelle". Dovremo fare un po’ di lavoro prima di calcolare medie o individuare trend.

Poiché i numeri possono comparire ovunque nel testo, serve una formula dinamica con TEXTJOIN(), MID() e ROW().

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Estrarre numeri in qualsiasi posizione in Excel. Immagine dell’autore.

Esempi di codice da copiare e incollare per casi speciali

Per concludere, ti propongo alcune formule aggiuntive per gestire scenari specifici. Copia e incolla liberamente: spero risolvano qualsiasi problema particolare tu stia affrontando. 

Estrarre il primo blocco contiguo di numeri

Se la tua stringa contiene più sequenze numeriche ma ti serve solo il primo gruppo, prova questa soluzione usando le funzioni LET() e SEQUENCE():

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Estrazione del primo blocco contiguo da una stringa in Excel. Immagine dell’autore.

Estrarre numeri racchiusi tra parentesi

Quando i numeri compaiono tra parentesi, usa:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Estrarre numeri racchiusi tra parentesi in Excel. Immagine dell’autore.

Estrarre numeri dopo un carattere specifico

Quando i tuoi dati usano i due punti come delimitatore (per esempio, "Punteggio: 8"), acquisisci i numeri con:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Estrarre numeri dopo un carattere speciale in Excel. Immagine dell’autore.

Questo estrae tutto ciò che segue i due punti. Incapsula il risultato con VALUE() se ti serve un valore numerico. Sostituisci i due punti se hai un altro delimitatore.

Estrarre tutti i numeri e combinarli in un unico valore

Per rimuovere tutti i caratteri non numerici e combinare ogni cifra in una stringa, usa questo:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Estrazione di tutti i numeri e combinazione in un unico valore in Excel. Immagine dell’autore.

Suggerimento: nelle versioni più vecchie di Excel, inserisci questa formula come formula matrice (Ctrl+Shift+Invio).

Estrarre numeri inclusi i decimali

Per stringhe numeriche che possono includere un decimale (ad es., "Prezzo: 45.99"), usa questa formula per mantenere sia le cifre sia il punto decimale:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Estrarre numeri inclusi i punti decimali in Excel. Immagine dell’autore.

Nota: questa formula presume la presenza di un solo punto decimale.

Conclusione

Estrarre numeri dalle stringhe è una tecnica importante per la pulizia dei dati. Come data analyst, ti incoraggio a imparare ad applicare le diverse formule discusse in questo articolo.

Se vuoi far crescere le tue competenze in Excel, ti consiglio i nostri corsi Data Analysis in Excel e Data Visualization in Excel per padroneggiare analisi e presentazione dei dati. Prova anche il corso  Financial Modeling in Excel, che può aprire molte porte.