Come estrarre un numero da una stringa in Excel

Come

DiAdam Davidson

Pubblicato7 giugno 2023

Excel è un potente foglio di calcolo, ma non può fare miracoli. Se stai importando dati e quei dati sono un disastro, Excel non sarà in grado di farci molto. Ad esempio, se disponi di valori associati ad altri caratteri come parte di una stringa, non sarai in grado di eseguire calcoli su di essi a meno che non li estrai prima.

Per fortuna, è possibile fare esattamente questo, anche se può essere più complicato di quanto si possa pensare. Ecco come estrarre un numero da una stringa in Excel.

Come estrarre un numero dalla fine di una stringa usando Flash Fill

Un modo rapido e semplice per estrarre un numero dalla fine di una stringa è utilizzare Excel Riempimento Flash caratteristica. Questa funzione consente di riempire rapidamente un intervallo di celle in base al modello rilevato in una cella specifica. Uno svantaggio di questo metodo è che richiede di eseguire da soli la prima estrazione e se si desidera estrarre solo un numero da una singola stringa, non sarà di alcuna utilità.

Nella maggior parte dei casi, Flash Fill può estrarre correttamente il numero dalla fine di una stringa, sebbene non sia infallibile, quindi dovresti sempre controllare attentamente i risultati. Se non funziona correttamente, prova a riempire manualmente le prime due o tre celle anziché solo la prima.

Per estrarre un numero dalla fine di una stringa utilizzando Flash Fill:

1. Seleziona la cella accanto alla prima cella contenente le stringhe.
   
2. Immettere il numero visualizzato alla fine della stringa.
   
3. Se tutti i tuoi dati sono in una colonna continua senza spazi vuoti, seleziona la cella direttamente sotto quella in cui hai appena inserito il numero.
   
4. Se i tuoi dati presentano spazi vuoti in alcune righe, seleziona la prima cella e tutte le celle sottostanti da cui desideri estrarre i numeri, comprese eventuali righe vuote.
   
5. Premere Ctrl+E su Windows o Cmd+E su Mac per eseguire la funzione Flash Fill.
6. Le celle rimanenti verranno riempite automaticamente in base al modello individuato da Excel. Nella maggior parte dei casi, i numeri dalla fine della stringa verranno estratti correttamente.
   

Come estrarre un numero dalla fine di una stringa usando una formula

Uno svantaggio dell'utilizzo del metodo Flash Fill è che richiede di estrarre manualmente i numeri dalla stringa per la prima riga. È possibile estrarre un numero dalla fine di una stringa utilizzando una formula, sebbene la formula sia piuttosto complessa.

Per estrarre un numero dalla fine di una stringa utilizzando una formula:

1. Fare clic nella cella in cui si desidera visualizzare il numero estratto.
2. Inserisci la seguente formula, sostituendo "B2" con la cella contenente la tua stringa:

   =VALORE(DESTRA(B2;LUNGHEZZA(B2)-MAX(SE(VAL.NUMERO(MEZZO(B2;RIGA(INDIRETTO("1:"&LUNGHEZZA(B2))),1)*1)=FALSO, RIGA(INDIRETTO(" 1:"&LUNGHEZZA(B2))),0))))

3. Premere accedere.
4. Il numero dovrebbe essere estratto dalla stringa.
5. Per applicare la formula a tutte le celle rimanenti, fai clic sulla maniglia di trascinamento nell'angolo in basso a destra della cella.
   
6. Trascina verso il basso sulle celle in cui desideri replicare la formula.
   
7. Rilascia il mouse e la formula verrà copiata nelle celle selezionate.
8. Queste celle ora dovrebbero contenere i numeri estratti.
   
9. Qualsiasi cella senza una stringa da cui estrarre restituirà un errore. Se vuoi invece restituire una cella vuota, puoi invece utilizzare la formula:

   =SE.ERRORE(VALORE(DESTRA(B2;LUNGHEZZA(B2)-MAX(SE(VAL.NUMERO(MEZZO(B2;RIGA(INDIRETTO("1:"&LUNGHEZZA(B2))),1)*1)=FALSO, RIGA(INDIRETTO ("1:"&LUNGHEZZA(B2))),0)))),"")

10. Tutte le celle vuote ora restituiranno uno spazio vuoto.
    

Forse ti starai chiedendo cosa fa questa formula. In breve, genera un array della stessa lunghezza della stringa. Mette in ordine ciascuno dei caratteri della stringa in questo array. Quindi valuta ciascuno di quei caratteri per vedere se sono un numero o meno. Tutti i caratteri che sono numeri vengono convertiti in zero. Tutti i caratteri che non sono numeri vengono convertiti in un numero che rappresenta la sua posizione nella stringa.

Ad esempio, AB5HG23 verrebbe convertito in 1, 2, 0, 4, 5, 0, 0. Quindi cerca il valore più alto in questo elenco, che è la posizione dell'ultimo carattere che non è un numero. Infine, restituisce tutti i caratteri dopo questo punto, che sono i numeri alla fine della stringa. La funzione VALUE lo riconverte quindi in un numero. Semplice, vero?

Come estrarre un numero dall'inizio di una stringa utilizzando Flash Fill

Puoi anche utilizzare Flash Fill per estrarre un numero dall'inizio di una stringa compilando tu stesso il primo esempio e consentendo a Excel di interrompere il modello.

Per estrarre un numero dall'inizio di una stringa utilizzando Flash Fill:

1. Seleziona la cella a destra della prima stringa che contiene un numero che desideri estrarre.
2. Immettere il numero visualizzato all'inizio della stringa.
   
3. Se tutti i tuoi dati sono in una colonna continua senza spazi vuoti, seleziona la cella direttamente sotto.
   
4. Se i tuoi dati presentano spazi vuoti, seleziona la prima cella e tutte le celle sottostanti da cui desideri estrarre i numeri, comprese eventuali righe vuote.
   
5. Premere Ctrl+E su Windows o Cmd+E su Mac per avviare Flash Fill.
6. Le celle selezionate ora dovrebbero mostrare i numeri estratti.
   

Come estrarre un numero dall'inizio di una stringa usando una formula

Puoi anche utilizzare una formula per estrarre un numero dall'inizio di una stringa. Funziona in modo simile alla formula nella sezione precedente, ma trova la posizione del primo carattere non numerico e restituisce tutti i caratteri precedenti.

Per estrarre un numero dall'inizio di una stringa utilizzando una formula:

1. Seleziona la cella in cui vuoi che appaia il numero estratto.
2. Inserisci la seguente formula, sostituendo "B18" con la cella contenente la tua stringa:

   =VALORE(SINISTRA(B18,CONFRONTA(FALSO, ISNUMERO(MEZZO(B18,RIGA(INDIRETTO("1:"&LUNGHEZZA(B18)+1)),1)*1),0)-1))

3. Premere accedere e il numero verrà estratto.
   
4. Puoi utilizzare il riempimento automatico in Excel per copiare la formula in qualsiasi altra cella richiesta.
5. Se desideri che qualsiasi riga vuota restituisca un risultato vuoto, utilizza invece la seguente formula:

   =SE.ERRORE(VALORE(SINISTRA(B18;CONFRONTA(FALSO; VAL.NUMERO(MEZZO(B18;RIGA(INDIRETTO("1:"&LUNGHEZZA(B18)+1)),1)*1),0)-1))," ")

Come estrarre numeri da qualsiasi punto in una stringa utilizzando il riempimento flash

Come accennato in precedenza, Flash Fill potrebbe non funzionare se la prima cella non corrisponde al formato di alcune delle altre celle. In questo caso, potrebbe essere necessario calcolare manualmente due o tre celle prima che Flash Fill fornisca il risultato desiderato.

Per estrarre i numeri da qualsiasi punto di una stringa utilizzando Flash Fill:

1. Nella cella accanto alla prima stringa digitare manualmente i numeri presenti nella stringa.
   
2. Se tutti i tuoi dati sono in una colonna continua, seleziona la cella direttamente sotto.
   
3. Se i tuoi dati presentano delle lacune, seleziona la prima cella e tutte le celle sottostanti da cui desideri estrarre i numeri, comprese eventuali righe vuote.
   
4. Premere Ctrl+E su Windows o Cmd+E su Mac per avviare Flash Fill.
5. Le celle dovrebbero ora riempirsi con i numeri estratti.
   

Come estrarre numeri da qualsiasi punto in una stringa utilizzando una formula

Puoi utilizzare una formula per estrarre tutti i numeri da una stringa, indipendentemente da dove appaiono.

Per estrarre numeri da qualsiasi punto di una stringa utilizzando una Formula:

1. Seleziona la cella in cui vuoi che appaia il numero estratto.
2. Inserisci la seguente formula, sostituendo "B9" con la cella contenente la tua stringa:

   =SOMMAPRODOTTO(MEDIO(0&B9,LARGE(INDICE(VAL.NUMERO(--MEDIO(B9,RIGA(INDIRETTO("1:"&LUNGHEZZA(B9))),1))*RIGA(INDIRETTO("1:"&LUNGHEZZA(B9) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. La tua formula dovrebbe estrarre tutti i numeri dalla stringa.
   
4. Se desideri che qualsiasi cella vuota restituisca una cella vuota, anziché zero, utilizza invece questa formula:

   =SE.ERRORE(SOMMAPRODOTTO(MEZZO(0&B9;LARGE(INDICE(VAL.NUMERO(--MID(B9;RIGA(INDIRETTO("1:"&LUNGHEZZA(B9))),1))*RIGA(INDIRETTO("1:"&LUNGHEZZA( B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)," ")

5. Puoi usa Compilazione automatica per applicare la formula a qualsiasi altra cella del foglio di calcolo.
6. Tutte le stringhe che non contengono numeri restituiranno uno zero. Puoi imparare a farlo convertire uno zero in un trattino in Excel se vuoi cambiarlo.

Estendi le tue competenze in Excel

Imparare a estrarre un numero da una stringa in Excel può aiutarti a estrarre rapidamente i dati di cui hai bisogno da un elenco di stringhe. Sebbene le formule richieste possano essere complesse, non è necessario capire esattamente come funzionano; basta che lo facciano!

Ci sono trucchi Excel più utili che puoi imparare per portare le tue abilità al livello successivo. Si può imparare come contare le celle con il testo in loro, o come calcolare l'età di qualcuno dalla data di nascita. Puoi persino creare un generatore di numeri casuali in Excel.