Tutorial Excel: estrai testo o numero in base a una posizione specifica

In molti casi, potrebbe essere necessario solo estrarre il contenuto utile da una frase o da una stringa di testo in una cella, come estrarre la provincia dall'indirizzo, estrarre l'indirizzo email da una frase, estrarre il numero della fattura del corriere da una conversazione, e così via. Questo tutorial restringe l'estrazione a una posizione specifica in una cella e raccoglie diversi metodi per estrarre testo o numeri da una cella in base a una posizione specifica in Excel.

Estrai il testo per posizione

Questa sezione raccoglie le posizioni comuni in cui un testo può essere estratto da una cella e fornisce i metodi corrispondenti per gestirle passo dopo passo. Puoi navigare per maggiori dettagli.

1. Estrarre il numero di caratteri da sinistra o da destra

Per estrarre il numero di caratteri dal lato sinistro o destro di una stringa, puoi provare uno dei metodi seguenti.

1.1 Estrarre il primo o l'ultimo N caratteri con le formule

Supponendo di avere un elenco di stringhe di testo nella colonna B come mostrato nello screenshot seguente, per estrarre i primi 2 caratteri e gli ultimi 2 caratteri da ciascuna stringa, puoi applicare le seguenti formule.

Estrarre i primi N caratteri da una stringa di testo

La funzione SINISTRA può aiutare a estrarre facilmente i primi N caratteri da una stringa di testo in Excel.

Formula generica

=LEFT(text_string,[num_chars])

argomenti

Ora puoi applicare questa formula per estrarre i primi 2 caratteri dalle celle nella colonna B.

1. Seleziona una cella vuota, copia o inserisci la formula qui sotto e premi il tasto entrare chiave per ottenere il primo risultato. Seleziona la cella del risultato e trascina la maniglia di riempimento automatico verso il basso per applicare la formula ad altre celle.

=LEFT(B5,2)

Ora sono stati estratti i primi 2 caratteri in ciascuna cella dell'intervallo B5:B10.

Estrarre gli ultimi N caratteri da una stringa di testo

Qui applichiamo la funzione DESTRA per estrarre gli ultimi N caratteri da una stringa di testo in Excel.

Formula generica

=RIGHT(text_string,[num_chars])

argomenti

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Quindi seleziona questa cella dei risultati e trascina la maniglia di riempimento automatico verso il basso per ottenere altri risultati.

=RIGHT(B5,2)

1.2 Estrai il primo o l'ultimo N caratteri con uno strumento straordinario

Sebbene le formule sopra siano semplici, per estrarre i primi o gli ultimi n caratteri da un lungo elenco di stringhe di testo, devi comunque trascinare la maniglia di riempimento automatico dall'alto verso il basso, il che potrebbe richiedere un po' di tempo. Qui, ti consigliamo Kutools for Excel'S Estrai testo utilità per estrarre i primi o gli ultimi N caratteri da un elenco di stringhe di testo in blocco.

1. Selezionare in anticipo l'elenco di stringhe di testo da cui si desidera estrarre il testo e fare clic Kutools > Testo > Estrai testo.

2. Nel spuntare Estrai testo finestra di dialogo, è necessario configurare come segue.

Note:: Per rendere dinamico il risultato quando la stringa di testo cambia, puoi controllare il Inserisci come formula scatola.

3. Nella prossima comparsa Estrai testo finestra di dialogo, scegliere una cella per l'output dei caratteri estratti e quindi fare clic OK.

Quindi il primo o l'ultimo N caratteri specificato vengono estratti in blocco dalle celle selezionate.

Fare clic per saperne di più su questa funzione.

2. Estrarre il testo prima o dopo un determinato carattere/parola

Per estrarre il testo prima o dopo un determinato carattere o parola, i diversi scenari in questa sezione soddisferanno le tue esigenze.

2.1 Estrarre il testo prima o dopo il primo delimitatore (un carattere)

Come mostrato nello screenshot qui sotto, per estrarre il testo prima o dopo il primo delimitatore da ciascuna cella nell'intervallo B4: B10, puoi applicare uno dei metodi seguenti.

2.1.1 Estrarre il testo prima del primo delimitatore con la formula

Applicare una formula basata sulle funzioni SINISTRA e TROVA può aiutare a estrarre il testo prima del primo delimitatore da una cella. Puoi seguire i passaggi seguenti per farlo.

Formula generica

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

argomenti

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il primo risultato. Seleziona la prima cella del risultato e trascina la maniglia di riempimento automatico verso il basso per ottenere i testi prima del primo delimitatore di altre celle.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Estrarre il testo dopo il primo delimitatore con la formula

La formula seguente aiuta a estrarre il testo dopo il primo delimitatore da una cella in Excel.

Formula generica

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

argomenti

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 Estrai il testo prima o dopo il primo delimitatore con uno strumento straordinario

Qui, consigliamo vivamente il Estrai testo utilità di Kutools per Excel. Con questa funzione, puoi estrarre facilmente i testi prima o dopo il primo delimitatore da un intervallo di celle in blocco.

1. Seleziona l'intervallo di celle in cui desideri estrarre il testo, quindi fai clic Kutools > Testo > Estrai testo.

2. Nel Estrai testo finestra di dialogo, è necessario configurare come segue.

Nota: Per rendere dinamico il risultato quando la stringa di testo cambia, puoi selezionare la casella Inserisci come formula.

3. Poi un altro Estrai testo viene visualizzata la finestra di dialogo, scegliere una cella per l'output dei risultati e fare clic OK.

Quindi i testi prima o dopo il primo delimitatore vengono estratti contemporaneamente dalle celle selezionate.

Per saperne di più su questa funzione, visita: Estrai rapidamente un determinato testo dalle celle in Excel.

2.2 Estrarre il testo prima o dopo l'ultimo delimitatore (un carattere)

Nei passaggi precedenti abbiamo appreso i metodi per estrarre il testo prima o dopo il primo delimitatore da una cella. Come mostrato nello screenshot qui sotto, questa sezione ti mostrerà due formule per estrarre il testo prima o dopo l'ultimo delimitatore da una cella. Puoi seguire i passaggi seguenti per farlo.

2.2.1 Estrarre il testo prima dell'ultimo delimitatore con la formula

Per estrarre il testo prima dell'ultimo delimitatore da una cella, puoi utilizzare le funzioni RICERCA, LENTE e SOSTITUZIONE all'interno della funzione SINISTRA.

Formula generica

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

argomenti

Seleziona una cella, inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e trascina la maniglia di riempimento automatico verso il basso per estrarre i testi da altre stringhe di testo nella stessa colonna.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Estrarre il testo dopo l'ultimo delimitatore con la formula

Dopo aver estratto il testo prima dell'ultimo delimitatore da una cella, puoi applicare la formula seguente per estrarre il testo dopo l'ultimo delimitatore di cui hai bisogno.

Formula generica

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

argomenti

Seleziona una cella, inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e trascina la maniglia di riempimento automatico verso il basso per estrarre i testi da altre stringhe di testo nella stessa colonna.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 Estrarre il testo dopo l'ennesimo carattere

Guarda l'esempio seguente, c'è un elenco di stringhe di testo nell'intervallo B4:B10, per estrarre il testo dopo il terzo carattere da ciascuna cella, puoi applicare una formula basata sulla funzione MID e sulla funzione LEN.

Formula generica

=MID(text_string,nth_char+1,LEN(text_string))

argomenti

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella dei risultati e trascina la maniglia di riempimento automatico verso il basso per ottenere altri risultati.

=MID(B5,3+1,LEN(B5))

2.4 Estrarre l'ennesima parola da una stringa di testo

Supponendo che tu abbia un elenco di stringhe di testo come mostrato nello screenshot qui sotto e desideri estrarre solo l'ennesima parola dalla stringa di testo, questa sezione fornisce tre metodi per farlo.

2.4.1 Estrarre l'ennesima parola con la formula

È possibile combinare le funzioni TRIM, MID, SUBSTITUTE, REPT e LEN per estrarre l'ennesima parola da una stringa di testo in una cella.

Formula generica

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

argomenti

In questo caso l'intervallo B5:B10 contiene le stringhe di testo, D5:D10 contiene i numeri che rappresentano l'ennesima parola, applichiamo questa formula per estrarre l'ennesima parola dalla stringa di testo.

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il primo risultato. Seleziona questa cella del risultato e trascina la maniglia di riempimento automatico verso il basso per ottenere l'ennesima parola di altre celle.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Nota: Puoi digitare direttamente l'ennesimo numero nella formula come segue.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Estrarre l'ennesima parola con la funzione definita dall'utente

Oltre alla formula sopra, puoi anche applicare una funzione definita dall'utente per estrarre l'ennesima parola da una cella in Excel.

1. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo, e quindi copia il VBA di seguito nella finestra del codice.

Codice VBA: estrai l'ennesima parola da una stringa di testo in una cella

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

4. Torna al foglio di lavoro contenente le stringhe di testo da cui desideri estrarre l'ennesima parola. Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere l'ennesima parola.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Nota: nella formula, D5 è la cella che contiene un numero che rappresenta l'ennesima parola. In alternativa, puoi sostituire direttamente il riferimento di cella con un numero.

5. Seleziona la cella del risultato e trascina la maniglia di riempimento automatico verso il basso per estrarre l'ennesima parola dalle stringhe di testo di altre celle.

2.4.3 Estrai l'ennesima parola con uno strumento straordinario

Se non si desidera applicare manualmente una formula o una funzione definita dall'utente fornita sopra, qui si consiglia Kutools per Excel Estrai l'ennesima parola nella cella utilità. Con questa funzione, puoi facilmente estrarre l'ennesima parola da una stringa di testo in una cella con pochi clic.

1. Selezionare una cella per posizionare il risultato e fare clic Kutools > formula Helper > Testo > Estratto l'ennesima parola nella cella. Vedi screenshot:

2. Nel Formule Helper finestra di dialogo, è necessario configurare come segue.

3. Quindi l'ennesima (seconda) parola viene estratta dalla stringa di testo nella cella B5 e puoi vedere che viene creata una formula contemporaneamente. Seleziona questa cella del risultato e trascina la maniglia di riempimento automatico verso il basso per ottenere l'ennesima parola da altre stringhe di testo.

Fare clic per saperne di più su questa funzione.

2.5 Estrarre il testo prima o dopo l'ennesima occorrenza di un delimitatore

Supponendo di avere un elenco di stringhe di testo come mostrato nello screenshot qui sotto. Per estrarre il testo prima o dopo la seconda occorrenza di uno spazio, questa sezione fornisce due formule per aiutarti a farlo.

2.5.1 Estrarre il testo prima dell'ennesima occorrenza di un delimitatore

È possibile utilizzare la funzione SINISTRA insieme alle funzioni SOSTITUZIONE e TROVA per estrarre il testo prima dell'ennesima occorrenza di un delimitatore da una cella in Excel.

Formula generica

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

argomenti

Seleziona una cella, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella dei risultati e trascinala Gestione di riempimento automatico verso il basso per ottenere altri risultati nell'elenco.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Nota: Nella formula, B5 è la cella contenente la stringa di testo da cui si vuole estrarre il testo; “ ” qui rappresenta uno spazio e il numero 2 rappresenta la seconda occorrenza di uno spazio. Puoi cambiarli in base alle tue esigenze.

2.5.2 Estrarre il testo dopo l'ennesima occorrenza di un delimitatore

Per estrarre il testo dopo l'ennesima occorrenza di un delimitatore, è possibile applicare la funzione DESTRA con le funzioni SOSTITUZIONE, LENTE e TROVA.

Formula generica

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

argomenti

Ora puoi applicare questa formula per estrarre il testo dopo la seconda occorrenza dello spazio da ciascuna cella nell'intervallo B5: B10 come segue.

Seleziona una cella, inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella dei risultati e trascinala Gestione di riempimento automatico verso il basso per ottenere altri risultati.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 Estrarre il testo prima o dopo l'interruzione di riga

Supponiamo di avere un elenco ordini nella colonna B e di voler estrarre solo la parte data e la parte Nr. prodotto da ciascuna cella. Puoi utilizzare le seguenti formule di Excel per farlo.

2.6.1 Estrarre il testo prima della prima interruzione di riga con la formula

Come puoi vedere nello screenshot sopra, la parte della data si trova prima della prima interruzione di riga all'interno della cella. Questa sezione mostrerà una funzione SINISTRA insieme alla funzione RICERCA per aiutarti a estrarre il testo prima della prima interruzione di riga all'interno di una cella.

Formula generica

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

argomenti

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Quindi puoi vedere il testo prima che la prima interruzione di riga in ogni cella nell'intervallo B5: B8 venga estratta come mostrato nello screenshot seguente.

Nota: Nella formula, CHAR(10) rappresenta un'interruzione di riga su Windows.

2.6.2 Estrarre il testo dopo l'ultima interruzione di riga con la formula

Nel passaggio precedente, abbiamo parlato di come estrarre il testo prima della prima interruzione di riga all'interno di una cella. E questa parte ti guiderà come estrarre il testo dopo l'ultima interruzione di riga all'interno di una cella con una formula diversa.

Formula generica

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

argomenti

Seleziona una cella vuota, inserisci la formula qui sotto e premi il entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare la formula ad altre celle.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Quindi il prodotto n. parte di ogni cella nell'elenco viene estratta come mostrato nella schermata sopra.

Nota: Nella formula, CHAR(10) rappresenta un'interruzione di riga su Windows.

2.7 Estrarre il testo prima o dopo una parola

Nelle parti precedenti, abbiamo imparato come estrarre il testo prima o dopo un carattere o un delimitatore. Cosa dovresti fare per estrarre il testo prima o dopo un'intera parola? Questa sezione introdurrà tre metodi per aiutarti a portare a termine questo compito.

2.7.1 Estrarre il testo prima di una determinata parola con la formula

La formula seguente ti aiuta a estrarre il testo prima di una determinata parola all'interno di una cella in Excel.

Formula generica

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

argomenti

Seleziona una cella vuota, inserisci la formula qui sotto e premi il entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle.

In questo esempio, estrarremo tutti i testi prima della parola "Excel", quindi digitiamo direttamente la parola nella formula e la racchiudiamo tra virgolette. Oppure puoi fare riferimento a una cella che contiene la parola "Excel".

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Note:

2.7.2 Estrarre il testo dopo una determinata parola con la formula

Per estrarre il testo dopo una determinata parola, puoi applicare la seguente formula per farlo.

Formula generica

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

argomenti

Seleziona una cella, inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona la cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Quindi puoi vedere tutti i testi dopo che la parola "Excel" in ogni cella è stata estratta come mostrato nello screenshot qui sotto.

Note:

2.7.3 Estrai il testo prima o dopo una determinata parola con uno strumento straordinario

Se ritieni che l'uso della formula possa causare molti inconvenienti, ti consigliamo vivamente di Estrai testo utilità di Kutools for Excel. Questa funzione aiuta ad automatizzare l'attività di estrazione in Excel con pochi clic.

1. Clic Kutools > Testo > Estrai testo per attivare questa funzionalità.

2. Nel Estrai testo finestra di dialogo, effettuare le seguenti impostazioni.

2.1) Assicurati di essere nel Estrai per posizione scheda;

2.2) nel Escursione fare clic su pulsante per selezionare l'intervallo di celle in cui si desidera estrarre i testi;

2.3) nel Opzioni sezione:

Per estrarre tutti i testi prima di una parola, scegli il prima del testo pulsante di opzione e quindi inserire la parola nella casella di testo;

Per estrarre tutti i testi dopo una parola, scegli il dopo il testo pulsante di opzione e quindi immettere la parola nella casella di testo.

2.4) Fare clic su OK pulsante. Vedi screenshot:

Note: Se vuoi creare risultati dinamici, controlla il Inserisci come formula scatola. Quindi i risultati si aggiorneranno automaticamente quando i dati nell'intervallo cambiano.

3. Quindi un Estrai testo viene visualizzata la finestra di dialogo, è necessario selezionare una cella per l'output del risultato e quindi fare clic su OK pulsante.

Quindi i testi prima o dopo una determinata parola all'interno di ciascuna cella nell'intervallo selezionato vengono estratti immediatamente.

Nota: Questa funzione fa distinzione tra maiuscole e minuscole.

Fare clic per saperne di più su questa funzione.

3. Estrai tra caratteri/parole

Se desideri estrarre il testo tra determinati caratteri o parole, prova i seguenti metodi.

3.1 Estrarre il testo tra due caratteri

Per estrarre il testo tra due caratteri, che possono essere uguali o diversi. Questa sezione fornisce diversi metodi e puoi scegliere quello più adatto alle tue esigenze.

3.1.1 Estrarre il testo tra due stessi caratteri con formula

Come mostrato nello screenshot qui sotto, c'è un elenco di stringhe di testo nella colonna B e vuoi estrarre la parte numerica tra i caratteri "/" da ciascuna cella nell'intervallo, la seguente formula può farti un favore.

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare tasto per ottenere il risultato. Seleziona la cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per ottenere risultati per altre celle nell'elenco.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Quindi il testo tra due stessi caratteri "/" viene estratto da ciascuna cella dell'intervallo. Vedi screenshot:

Nota:

3.1.2 Estrarre il testo tra due caratteri diversi con formula

Dopo aver appreso come estrarre il testo tra due stessi caratteri all'interno di una cella, qui dimostreremo una formula per estrarre il testo tra due caratteri diversi. Come mostrato nello screenshot qui sotto, per estrarre solo l'indirizzo email compreso tra "<" e ">" da ciascuna cella nella colonna B, puoi procedere come segue.

Formula generica

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

argomenti

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Puoi vedere che solo il testo tra i caratteri specificati viene estratto come mostrato nello screenshot sopra.

3.1.3 Estrai il testo tra due caratteri con uno strumento straordinario

Qui, consigliamo vivamente il Estrai le stringhe tra il testo specificato caratteristica di Kutools for Excel per aiutarti a estrarre facilmente il testo tra due caratteri uguali o diversi all'interno di una cella in Excel.

1. Selezionare una cella vuota per produrre il risultato, quindi fare clic su Kutools > formula Helper > formula Helper.

2. Nel Formule Helper finestra di dialogo, effettuare le seguenti impostazioni.

3. Quindi viene estratto solo il testo tra "<" e ">" all'interno della cella B5. Nel frattempo, è stata creata una formula, puoi selezionare questa cella del risultato e quindi trascinare la maniglia di riempimento automatico verso il basso per estrarre i testi da altre celle nello stesso elenco.

Fare clic per saperne di più su questa funzione.

3.1.4 Estrarre il testo tra due caratteri (inclusi i caratteri) per regola

Se vuoi conservare i due caratteri dopo l'estrazione, prova ad applicare una regola nella funzionalità Estrai testo di Kutools per Excel.

1. Clic Kutools > Testo > Estrai testo.

2. Nel Estrai testo finestra di dialogo, effettuare le seguenti impostazioni.

2.1) Fare clic su Estrai per regola scheda;

2.2) nel Escursione sezione, fare clic su pulsante per selezionare l'intervallo di celle in cui si desidera estrarre il testo tra i caratteri;

2.3) nel Testo casella, immettere <*>;

Suggerimenti: "<" e ">” sono i caratteri di cui vuoi estrarre il testo, * è un carattere jolly che rappresenta un numero qualsiasi di caratteri. Puoi inserire la condizione in base alle tue esigenze.

2.4) Fare clic su Aggiungi pulsante per aggiungere la condizione a Descrizione della regola casella di riepilogo;

2.5) Fare clic su Ok pulsante. Vedi screenshot:

3. Un altro Estrai testo si apre la finestra di dialogo. Seleziona una cella per visualizzare il risultato, quindi fai clic su OK pulsante.

Quindi, il testo compreso tra i caratteri specificati (inclusi i caratteri) viene estratto in blocco da ciascuna cella nell'intervallo selezionato.

3.2 Estrarre il testo tra due parole

Oltre a estrarre il testo tra due caratteri, potrebbe essere necessario estrarre anche il testo tra due parole. Ad esempio, estrai tutte le stringhe di testo tra due parole "KTE" e "feature" da ciascuna cella nella colonna B come mostrato nello screenshot seguente. Puoi provare uno dei seguenti metodi per farlo.

3.2.1 Estrarre il testo tra due parole con formula

È possibile utilizzare una formula basata sulla funzione MID e sulla funzione RICERCA per estrarre tutte le stringhe di testo tra due parole all'interno di una cella.

Formula generica

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

argomenti

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Note:: Nella formula, il numero 3 rappresenta la lunghezza del carattere della parola "KTE"; il numero 4 rappresenta la lunghezza del carattere della parola "KTE" più 1.

Come puoi vedere, tutte le stringhe di testo comprese tra le due parole specificate vengono estratte da ogni cella della colonna B.

3.2.2 Estrai il testo tra due parole con uno strumento straordinario

Per molti utenti di Excel, le formule possono essere difficili da ricordare e gestire. Qui, con il Estrai le stringhe tra il testo specificato caratteristica di Kutools for Excel, puoi estrarre facilmente il testo tra due parole con pochi clic.

1. Selezionare una cella per l'output del risultato, quindi fare clic Kutools > formula Helper > Assistente alle formule.

2. Nel formula Helper finestra di dialogo, è necessario configurare come segue.

3. Quindi vengono estratte tutte le stringhe di testo tra due parole "KTE" e "feature" nella cella B5. Nel frattempo, è stata creata una formula, puoi selezionare questa cella di risultato e quindi trascinare la sua maniglia di riempimento automatico verso il basso per estrarre testi da altre celle nello stesso elenco.

Estrai i numeri per posizione

Per un elenco di stringhe alfanumeriche, possono esserci tre casi:

1. Il numero è all'inizio del testo;
2. Il numero è alla fine del testo;
3. Il numero può essere ovunque nel testo.

In questa sezione, forniremo diversi metodi che possono essere utilizzati per estrarre i numeri in ciascun caso menzionato sopra.

1 Estrarre il numero dalla sinistra di una stringa

Questa parte introdurrà una formula per aiutarti a estrarre solo i numeri che appaiono prima del testo in una cella.

Formula generica

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

argomenti

Nota:

Seleziona una cella vuota, inserisci la formula qui sotto e premi Ctrl + Shift + entrare or entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per ottenere il numero di altre celle.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Note:

2 Estrarre il numero dalla destra di una stringa

Come mostrato nello screenshot qui sotto, per estrarre solo i numeri che appaiono dopo il testo in una cella, prova la seguente formula.

Formula generica

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

argomenti

Nota:

Seleziona una cella vuota, inserisci la formula qui sotto e premi Ctrl + Shift + entrare or entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per ottenere il numero di altre celle.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

Note:

3. Estrarre tutti i numeri da qualsiasi posizione in una stringa di testo

I metodi sopra aiutano a estrarre il numero solo dalla sinistra o dalla destra di una stringa di testo. Se vuoi estrarre tutti i numeri da qualsiasi punto in una stringa di testo, qui forniamo tre metodi per farlo.

3.1 Estrarre tutti i numeri da qualsiasi punto in una stringa con la formula

Puoi applicare la seguente formula per estrarre tutti i numeri da qualsiasi punto in una stringa di testo in Excel.

1. Seleziona una cella vuota, copia o inserisci la formula qui sotto e premi il tasto entrare chiave per ottenere tutti i numeri dalla cella B5.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. Seleziona la cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per ottenere tutti i numeri di altre celle.

3.2 Estrarre tutti i numeri da qualsiasi punto in una stringa con VBA

La formula sopra è troppo lunga e complicata per molti utenti di Excel. In realtà, puoi eseguire uno script VBA per automatizzare l'attività in Excel. Puoi fare come segue.

1. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. In apertura Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo. Quindi copia il VBA di seguito nella finestra del codice del modulo.

Codice VBA: estrai tutti i numeri da qualsiasi punto in una stringa di testo

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. premi il F5 chiave per eseguire il codice. In apertura Kutools per Excel finestra di dialogo, selezionare l'intervallo di celle in cui si desidera estrarre tutti i numeri da ciascuna cella, quindi fare clic su OK pulsante.

4. Poi un altro Kutools per Excel si apre la finestra di dialogo. In questa finestra di dialogo, selezionare una cella di destinazione e fare clic OK.

Quindi tutti i numeri vengono estratti in blocco da ogni cella nell'intervallo selezionato.

4. Estrarre i numeri dopo un testo specifico

Come mostrato nello screenshot qui sotto, per estrarre qualsiasi numero dopo il testo specifico "No.", questa sezione fornisce due metodi per aiutarti a farlo.

4.1 Estrarre i numeri dopo un testo specifico con la formula

Puoi applicare la seguente formula per estrarre i numeri dopo un testo specifico in una cella in Excel.

Formula generica:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

argomenti

Seleziona una cella vuota, copia o inserisci la formula sottostante e premi il tasto entrare chiave per ottenere il risultato. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

Note:

4.2 Estrarre i numeri dopo un testo specifico con la funzione definita dall'utente

La seguente funzione definita dall'utente può anche aiutare a estrarre i numeri dopo un testo specifico in una cella. Si prega di fare come segue.

1. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo, e quindi copia il codice VBA di seguito nella finestra del codice del modulo.

Codice VBA: estrae i numeri dopo un testo specifico in una cella

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

4. Seleziona una cella, inserisci la formula qui sotto e premi il entrare chiave. Seleziona questa cella del risultato e quindi trascina la maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle.

=GetNumberAfterTheChar(B5,"No. ")

Note:

Articoli correlati: