Come estrarre numeri da una cella Microsoft Excel

In questa guida vediamo vari metodi per estrarre i numeri da una cella in Microsoft Excel. Lavoreremo con diverse celle in una tabella e sfrutteremo sia funzioni di Excel sia Power Query. La tabella si chiama TBL e l'unica colonna che abbiamo si chiama STRINGHE.

Lo spunto mi era venuto creando la guida per la creazione degli OKR, dove c'era una delle soluzioni proposte - questo perchè è utile poter prendere, da una stringa tipo "Chiudere 124 contratti" solamente il valore 124 direttamente in automatico. Il valore può essere poi usato per fare grafici o altre statistiche, ad esempio!

Il fatto è che Excel si evolve e noi con lui, con Excel 365 abbiamo delle opzioni che non possiamo sottovalutare quali, ad esempio, le REGEX. Abbiamo infatti tre diverse funzioni dedicate (REGEX.ESTRAI, REGEX.TEST, REGEX.SOSTITUISCI) e vedremo in questa guida come sfruttare REGEX.ESTRAI con un caso pratico.

Per darti un minimo contesto, le REGEX in italiano si traducono come espressioni regolari e rappresentano una sequenza di simboli (quindi una stringa) che identifica un insieme di stringhe. La loro scrittura è un po' complessa, ma se vuoi approfondire ci sono risorse dedicate come Regex Learn e Regex101. Consiglio anche di sfruttare al massimo tool di AI, una volta individuato un pattern possibile e spiegato alla AI di turno, per avere almeno una base concreta dalla quale partire - perchè partire da zero è davvero impegnativo.

Come estrarre numeri da una cella Excel con le formule

Estrarre numeri da una cella Excel con le formule può essere un processo complesso, perchè le funzioni native che permettono di farlo in un colpo solo sono relativamente recenti, come REGEX.ESTRAI. Prima bisognava utilizzare combinazioni complesse usando SEQUENZA, CONCAT... che, in questa guida, ignoreremo.

Vediamo REGEX.ESTRAI nel dettaglio:

=REGEX.ESTRAI(text;pattern;[return_mode];[case_sensitivity])

REGEX.ESTRAI:

• Text: inseriremo il testo, o la cella, dalla quale estrarre i caratteri che ci interessano
• Pattern: il pattern, cioè la sequenza / combinazione relativa ai caratteri che vogliamo ottenere
• Return_mode (facoltativa): quanti risultati vogliamo ottenere. 0 = prima corrispondenza, 1 = tutte, 2 = acquisisci gruppi della prima corrispondenza
• Case_sensitivity (facoltativa): 0 indica di mantenere la distinzione fra maiuscole e minuscole, 1 la rimuove

Il punto cruciale è il pattern, che può risultare davvero ostico se non hai mai visto le REGEX. Vediamolo nel nostro caso, dove vogliamo estrarre i numeri e solo i numeri da una stringa dedicata:

=REGEX.ESTRAI([@STRINGHE];"[0-9]+")

Stiamo dicendo che vogliamo, per ognuno dei valori della colonna STRINGHE, recuperare le cifre da 0-9 con [0-9] e che possono essere anche più di una (+). In questo caso non fa differenza inserire alcun valore nel Pattern_mode perchè tanto abbiamo solo un numero possibile, stesso tema per la parte finale relativa alla Case sensitivity. Nota anche che l'intera condizione del pattern è fra virgolette, quindi la scrittura non è esattamente agevole se non ne hai mai sentito parlare.

Il valore restituito è però un testo e ci sono molti modi per renderlo un numero, fra i quali:

• Inserire -- prima della formula, che rende i dati testuali dei numeri
• Utilizzare la formula VALORE oppure NUMERO.VALORE
• Moltiplicare il risultato per 1, o comunque effettuare un qualsivoglia calcolo matematico, ad esempio +0

In questo caso, andiamo sulla soluzione con il double unary (--):

=--REGEX.ESTRAI([@STRINGHE];"[0-9]+")

Il risultato, in questo caso, sarà un numero!

Questa è decisamente la soluzione più semplice e possiamo quindi creare una LAMBDA personalizzata chiamata FN.ESTRAI_NUMERI che permette di estrarre il primo numero completo che trovi in una stringa:

=LAMBDA(cella;--REGEX.ESTRAI(cella;"[0-9]+"))

Cliccando su Formule > Nomi definiti > Definisci nome, possiamo inserire FN.ESTRAI_NUMERI come nome della funzione, mentre la formula precedente può essere inserita in "Riferito a". Clicchiamo su OK.

Potremo quindi poi riutilizzarla per ottenere tutte le cifre consecutive in un colpo solo:

=FN.ESTRAI_NUMERI([@STRINGHE])

Chiaramente, in questo caso, ci aspettiamo di avere solo un numero da recuperare in una singola stringa - non inserendo il pattern, avremo come default 0, cioè di restituire l'intera sequenza di cifre che appare per prima. Questo aspetto ha senso, poichè non mi vengono in mente casi dove vorremmo "agganciare" fra di loro diversi elementi, del tipo "Abbiamo raggiunto un margine di 30000 euro nel 2026", che darebbe come risultato 300002026.

Come estrarre numeri e percentuali da una cella Excel con le formule

Vediamo un caso più complesso, dove vogliamo restituire numeri e percentuali da una cella Excel. Variamo ora il database di partenza, inserendo una stringa più complessa tipo "200 prodotti generano il 43% del fatturato".

Se volessimo prendere tutte le cifre inserite nella stringa, ci basterebbe modificare così la formula:

=--CONCAT(REGEX.ESTRAI([@STRINGHE];"[0-9]+";1))

Inserendo 1, prendiamo tutte le cifre che troviamo nella cella. Con CONCAT, evitiamo di avere questi risultati in due o più celle, creando una sola stringa di valori numerici, grazie al double unary --.

Nel caso indicato abbiamo sia un numero sia una percentuale. Per ottenere solo i numeri interi dovremo cambiare la nostra regola:

=--REGEX.ESTRAI([@STRINGHE]; "\d+(?!\s*%)")

Vediamo le singole componenti del pattern:

• \d+ significa una o più cifre (d significa digit, cifra). In questo caso vuol dire prendere un numero intero, senza decimali o percentuali
• (?!…) indica un negative lookahead. Gli elementi inseriti dopo questa combinazione di simboli non devono seguire ciò che troviamo prima, ovvero il numero indicato da \d+
• \s*% vuol dire che non vogliamo casi dove sia seguito da uno o più spazi (* con s, che sarebbe spazio) e da una percentuale

Vediamo come fare se volessimo invece ottenere le percentuali:

=--REGEX.ESTRAI([@STRINGHE]; "\d+(?=\s*%)")

Qui la situazione è invertita rispetto a prima. Il punto cruciale è rappresentato da (?=), che in questo caso indica un positive lookahead. Vogliamo quindi restituire esclusivamente i casi nei quali abbiamo, dopo le cifre, il simbolo della percentuale con un numero di spazi che può essere variabile.

Fin qui, tutto bene - ma se avessimo percentuali con una virgola in mezzo, come "40,25%"?

Come faremmo, però, se volessimo ottenere anche le percentuali con la virgola? Qui il pattern va modificato, altrimenti non riusciremo a considerare i valori corretti:

=--REGEX.ESTRAI([@STRINGHE]; "\d+(,\d+)?(?=\s*%)"; 1)

Vediamo, anche in questo caso, i singoli pezzi separatamente:

• \d+, come nel caso precedente, restituisce più cifre, se presenti
• (,\d+) cerca una virgola, seguita da una o più cifre (vedi sopra). Il punto di domanda finale, ?, segnala che questo campo è opzionale, potrebbe anche non esserci
• (?=\s*%) è come sopra, per il caso delle percentuali. Dobbiamo assicurarci che, separato o no da uno o più spazi, ci sia il simbolo della percentuale %
• Il valore 1 finale considera tutte le corrispondenze

Volessimo poi trovare il valore reale, potremmo sempre dividere per 100, essendo percentuali.

Se poi dovessimo seguire altri casi, del tipo "Prendi le cifre solo se c'è il simbolo del $", basterebbe modificare il pattern di cui sopra.

Come estrarre numeri da una cella con Power Query in Excel

Vediamo ora come estrarre i numeri da una cella con Power Query in Excel, senza utilizzare funzioni specifiche. I casi sono vari, ma partono tutti, in questi esempi, dalla tabella chiamata TBL.

Vediamo il primo, la Query#1:

let
Source = Excel.CurrentWorkbook(){[Name="TBL"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"STRINGHE"}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Removed Other Columns", "NUMERI_PQ", each Text.Select([STRINGHE],{"0","1","2","3","4","5","6","7","8","9"})),
#"Modificato tipo" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"NUMERI_PQ", Int64.Type}})
in
#"Modificato tipo"

In questa soluzione:

• Prendiamo la tabella TBL
• Selezioniamo esclusivamente la colonna chiamata STRINGHE
• Aggiungiamo una colonna personalizzata dove indichiamo che vogliamo selezionare il testo (Text.Select) solo se corrisponde a una cifra da 0 a 9
• Cambiamo poi il tipo di dato della nuova colonna, chiamata NUMERI_PQ, in modo che sia un valore numerico intero

Vediamo come questa soluzione non sia particolarmente leggibile, quindi potremmo pensare di creare esternamente la lista di cifre da 0 a 9 per poi ripescarla all'interno della nostra query. Potremmo anche pensare di trasformare, in automatico, i valori testuali in valori numerici.

In questo caso possiamo creare una lista di valori chiamata CIFRE:

= {"0".."9"}

{0..9} crea una lista di caratteri da 0 a 9, ma sono numeri. Per renderli dei testi, dobbiamo, all'interno della lista {0..9}, far sì che tutti diventino dei testi. Inserendo le virgolette "0" e "9", questi diventano testi.

L'alternativa, Query#2, è simile ma decisamente più comprensibile:

let
Source = Excel.CurrentWorkbook(){[Name="TBL"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"STRINGHE"}),
= Table.AddColumn(#"Removed Other Columns", "NUMERI_PQ", each Number.FromText(Text.Select([STRINGHE], CIFRE)), Int64.Type)
in
#"Aggiunta colonna personalizzata"

Non solo abbiamo preso i nostri valori di cifre usando CIFRE, ma abbiamo anche aggiunto Number.FromText che ci permette di creare dei numeri a partire di testi in Power Query. Abbiamo quindi ottimizzato la nostra query, facendo tutto in un solo passaggio e definendo meglio i singoli passaggi.

Un'ulteriore alternativa, Query#3, sarebbe stata, nella parte di colonna personalizzata, diversa perchè riassume i passaggi della Query#2 senza un riferimento esterno:

let
Source = Excel.CurrentWorkbook(){[Name="TBL"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"STRINGHE"}),
= Table.AddColumn(#"Removed Other Columns", "NUMERI_PQ", each Number.FromText(Text.Select([STRINGHE], {"0".."9"})),Int64.Type)
in
Custom1

A un certo punto, sempre che non siano file enormi, sono preferenze personali, il risultato sarà lo stesso per le tre query.

La versione ancora più elegante, Query#4, richiede la creazione di una funzione personalizzata in Power Query:

let
EstraiCifre = (testo as text) as number =>
Number.FromText(Text.Select(testo, {"0".."9"}))
in
EstraiCifre

Possiamo riutilizzare EstraiCifre dove vogliamo nelle nostre query, permettendoci di estrarre sempre tutte le cifre inserite all'interno. In questo caso, semplifichiamo la query ulteriormente:

let
Source = Excel.CurrentWorkbook(){[Name="TBL"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"STRINGHE"}),
= Table.AddColumn(#"Removed Other Columns", "NUMERI_PQ", each Number.FromText(Text.Select([STRINGHE], CIFRE)), Int64.Type)
in
#"Added Custom"

Come detto, tutte, anche quest'ultima, restituiscono lo stesso risultato. Trovo però sia utile illustrare i vari metodi, in modo che tu possa riutilizzare le stesse tecniche anche in altri database.

Conclusione

Come abbiamo visto, ci sono numerosi modi per estrarre i numeri da una cella in Excel. A seconda dei casi, potresti voler sperimentare con le REGEX oppure approfondire Power Query, ma una cosa è certa - usando Excel 365, possiamo semplificare enormemente processi che prima richiedevano calcoli decisamente più complessi!