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.
Questo tutorial copre principalmente: (Fare clic su qualsiasi intestazione nel sommario in basso oa destra per accedere al contenuto corrispondente.)
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 Entra 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 Entra 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 il primo o l'ultimo n caratteri da un lungo elenco di stringhe di testo, è comunque necessario trascinare la maniglia di riempimento automatico dall'alto verso il basso, il che potrebbe richiedere un po' di tempo. Qui consiglia 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.
Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.
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 Entra 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 consiglia 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.
Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.
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 Entra 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 Entra 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 Entra 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 Entra 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
Funzione ExtractTheNthWord(Source As String, Position As Integer) 'Aggiorna entro 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 = "" Altrimenti FindWord = arr( Posizione - 1) Termina se termina la funzione
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 Entra 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 vuoi applicare manualmente una formula o una funzione definita dall'utente fornita sopra, qui 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.
Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.
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 Entra 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 Entra 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 Entra 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 Entra 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 Entra 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 Entra 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, qui ti consiglio vivamente il 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.
pulsante per selezionare l'intervallo di celle in cui si desidera estrarre i testi;
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.
Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.
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 caratteri uguali o diversi. Questa sezione fornisce diversi metodi e puoi sceglierne uno in base 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 Entra chiave per ottenere il risultato. Seleziona la cella del risultato e trascinala verso il basso Gestione di riempimento automatico per ottenere i risultati di 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 Entra 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 consiglia 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.
Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.
3.1.4 Estrarre il testo tra due caratteri (inclusi i caratteri) per regola
Se vuoi mantenere i due caratteri dopo l'estrazione, prova ad applicare una regola nella funzione Estrai testo di Kutools for Excel.
1. Clic Kutools > Testo > Estrai testo.
2. Nel Estrai testo finestra di dialogo, effettuare le seguenti impostazioni.
pulsante per selezionare l'intervallo di celle in cui si desidera estrarre il testo tra i caratteri;
3. Un altro Estrai testo viene visualizzata la finestra di dialogo, selezionare una cella per l'output del risultato, quindi fare clic su OK pulsante.
Quindi il testo tra i caratteri specificati (inclusi i caratteri) viene estratto in blocco da ciascuna cella nell'intervallo selezionato.
Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.
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 Entra 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.
Puoi vedere che tutte le stringhe di testo tra le due parole specificate vengono estratte da ogni cella nella 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" 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.
Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.
Estrai i numeri per posizione
Per un elenco di stringhe alfanumeriche, possono esserci tre casi:
- Il numero è all'inizio del testo;
- Il numero è alla fine del testo;
- 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 + spostamento + Entra or Entra 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 + spostamento + Entra or Entra 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 Entra 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() 'Aggiornato da 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("Seleziona stringhe di testo :", xTitleId, "", Digita:=8) Se TypeName(xDRg) = "Nothing" Quindi esci dal sottoinsieme xRRg = Application.InputBox("Seleziona la cella di output:", xTitleId, "", Digita:=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 Sub3. premi il F5 chiave per eseguire il codice. In apertura KutoolsforExcel 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 KutoolsforExcel 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 Entra 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
Funzione GetNumberAfterTheChar(Rng As Range, Char As String) 'Aggiornato da 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 = "" Esci dalla funzione End If xStart = xStart - 1 + Len(Char) If xStart < 1 Then GetNumberAfterTheChar = "" Esci dalla funzione End If xValue = Mid(xValue, xStart + 1) xRntString = "" For xI = 1 To Len(xValue) xC = Mid(xValue, xI, 1) Seleziona maiuscolo Asc(xC) Caso da 48 a 57 xRntString = xRntString & xC Caso Altro Esci per fine Seleziona successivo GetNumberAfterTheChar = xRntString Funzione finale
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 Entra 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:
Esercitazione su Excel: dividere celle di testo, numeri e date (separate in più colonne)
Questo tutorial è diviso in tre parti: dividere le celle di testo, dividere le celle dei numeri e dividere le celle della data. Ogni parte fornisce esempi diversi per aiutarti a sapere come gestire il lavoro di divisione quando si verifica lo stesso problema.
Clicca per saperne di più ...
Excel aggiunge testo e numero nella posizione specificata della cella
In Excel, l'aggiunta di testi o numeri alle celle è un lavoro molto comune. Come aggiungere spazio tra i nomi, aggiungere prefisso o suffisso alle celle, aggiungere trattini ai numeri sociali. Qui in questo tutorial, elenca quasi tutti gli scenari di aggiunta in Excel e fornisce i metodi corrispondenti per te.
Clicca per saperne di più ...
Excel Rimuovi caratteri, parole, numeri da stringhe di testo
Supponiamo che tu abbia un lungo elenco di stringhe di testo che contengono caratteri, numeri o altri simboli specifici. In alcuni casi, potrebbe essere necessario rimuovere alcuni caratteri in base alla posizione, ad esempio da destra, sinistra o centro dalle stringhe di testo, o eliminare alcuni caratteri indesiderati, numeri dall'elenco delle stringhe. Trovare le soluzioni una per una ti farà venire il mal di testa, questo tutorial raccoglie tutti i tipi di metodi per rimuovere caratteri, parole o numeri in Excel.
Clicca per saperne di più ...
I migliori strumenti per la produttività in ufficio
Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%
- Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
- Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
- Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
- Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
- Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
- Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
- Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
- Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
- Più di 300 potenti funzionalità. Supporta Office / Excel 2007-2019 e 365. Supporta tutte le lingue. Facile distribuzione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.
Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
You are guest
or post as a guest, but your post won't be published automatically.
Be the first to comment.
