Scarica la cartella di lavoro di esempio
Questo tutorial sarà dimostrarti come trovare ed estrarre un numero all'interno di una stringa di testo in Excel e Fogli Google.
Trova ed estrai il numero da una stringa
A volte i tuoi dati possono contenere numeri e testo e desideri estrarre i dati numerici.
Se la parte del numero si trova sul lato destro o sinistro della stringa, è relativamente facile dividere il numero e il testo . Tuttavia, se i numeri sono all'interno della stringa, ovvero tra due stringhe di testo, sarà necessario utilizzare una formula molto più complicata (mostrata di seguito).
TEXTJOIN - Estrai numeri in Excel 2016+
In Excel 2016 è stata introdotta la funzione TEXTJOIN, che può estrarre i numeri da qualsiasi punto della stringa di testo. Ecco la formula:
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3,ROW(INDDIRECT("1:"&LEN(B3))),1)*1),"")) |
Vediamo come funziona questa formula.
Le funzioni RIGA, INDIRETTO e LUNGHEZZA restituiscono una matrice di numeri corrispondenti a ciascun carattere nella stringa alfanumerica. Nel nostro caso, "Monday01Day" ha 11 caratteri, quindi la funzione ROW restituirà l'array {1;2;3;4;5;6;7;8;9;10;11}.
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3, {1;2;3;4;5;6;7;8;9;10;11},1)*1);"")) |
Successivamente, la funzione MID estrae ogni carattere dalla stringa di testo, creando un array contenente la stringa originale:
| 1 | =TEXTJOIN("",TRUE,IFERROR(({"M";"o";"n";"d";"a";"y";"0";"1";"D";"a ";"y"}*1),"")) |
Moltiplicando ogni valore nell'array per 1 creerà un array contenente errori Se il carattere dell'array era testo:
| 1 | =TEXTJOIN("",TRUE,IFERROR(({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0;1;#VALUE!;#VALUE!;#VALUE! !}),"")) |
Successivamente, la funzione IFERROR rimuove i valori di errore:
| 1 | =TEXTJOIN("",TRUE,{"";"";"";"";"";"";0;1;"";"";""}) |
Lasciando solo la funzione TEXTJOIN che unisce i numeri rimanenti.
Nota che la formula ti darà tutti i caratteri numerici insieme dalla tua stringa. Ad esempio, se la tua stringa alfanumerica è Monday01Day01, ti darà 0101 come risultato.
Estrai numeri - Prima di Excel 2016
Prima di Excel 2016, potevi usare un metodo molto più complicato per estrarre i numeri dal testo. È possibile utilizzare la funzione TROVA insieme alle funzioni SEERRORE e MIN per determinare sia la prima posizione della parte numerica che la prima posizione della parte di testo dopo il numero. Quindi estraiamo semplicemente la parte del numero con la funzione MID.
| 1 | =MID(B3,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B3),999999999)),MIN(IFERROR(SEARCH({"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" },B3,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B3),999999999))),999999999))-MIN(IFERROR(FIND( {0,1,2,3,4,5,6,7,8,9},B3),999999999))) |
Nota: questa è una formula di matrice, è necessario immettere la formula premendo CTRL + MAIUSC + INVIO, anziché solo INVIO.
Vediamo passo passo come funziona questa formula.
Trova il primo numero
Possiamo usare la funzione TROVA per individuare la posizione iniziale del numero.
| 1 | = MIN(SEERRORE(TROVA({1,2,3,4,5,6,7,8,9,0},B3),999999999)) |
Per l'argomento trova_testo della funzione TROVA, usiamo la costante di matrice {0,1,2,3,4,5,6,7,8,9}, che fa sì che la funzione TROVA esegua una ricerca separata per ogni valore in la costante di matrice.
L'argomento inside_text della funzione FIND nel nostro caso è Monday01Day, in cui 1 può essere trovato nella posizione 8 e 0 nella posizione 7, quindi il nostro array di risultati sarà: {8,#VALUE,#VALUE,#VALUE, #VALORE,#VALORE,#VALORE, #VALORE, #VALORE,7}.
Usando la funzione IFERROR sostituiamo gli errori #VALUE con 999999999. Quindi cerchiamo semplicemente il minimo all'interno di questo array e otteniamo quindi il posto del primo numero (7).
Si prega di notare che la formula sopra è una formula di matrice, è necessario premere Ctrl + Maiusc + Invio per attivarla.
Trova il primo carattere di testo dopo il numero
Analogamente all'individuazione del primo numero all'interno della stringa, utilizziamo la funzione FIND per determinare dove ricomincia il testo dopo il numero facendo buon uso anche dell'argomento start_num della funzione.
| 1 | =MIN(IFERROR(TROVA({"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"},B3,C3),999999999)) |
Questa formula funziona in modo molto simile alla precedente utilizzata per individuare il primo numero, solo che utilizziamo le lettere nella nostra costante di matrice e quindi i numeri causano errori #VALUE. Nota che iniziamo la ricerca solo dopo la posizione determinata per il primo numero (questo sarà l'argomento start_num) e non dall'inizio della stringa.
Non dimenticare di premere Ctrl + Maiusc + Invio per utilizzare la formula sopra.
Non resta altro che mettere insieme tutto questo.
Estrai il numero da due testi
Una volta che abbiamo la posizione iniziale della parte del numero e l'inizio della parte del testo dopo, usiamo semplicemente la funzione MID per estrarre la parte del numero desiderata.
| 1 | =MID(LA3,DO3,RE3-DO3) |
Altro metodo
Senza spiegarlo in modo più dettagliato, puoi anche utilizzare la formula complessa di seguito per ottenere il numero all'interno di una stringa.
| 1 | =SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW(INDDIRECT("1:"&LEN(B3))),1))*ROW(INDDIRECT("1:"&LEN(B3) )),0)),ROW(INDIRETTO("1:"&LUNGHEZZA(B3))))+1,1)*10^RIGA(INDIRETTO("1:"&LUNGHEZZA(B3)))/10) |
Tieni presente che questa formula sopra ti darà 0 quando non viene trovato alcun numero nella stringa e che gli zeri iniziali verranno omessi.
Trova ed estrai il numero da stringa a testo in Fogli Google
Gli esempi mostrati sopra funzionano allo stesso modo in Fogli Google come in Excel.
