Tecniche di substring in Excel: estrai e formatta il testo

Estrarre testo per posizione con le funzioni substring di Excel

Spesso vogliamo estrarre parti specifiche di una stringa di testo, come una parola o una sequenza di caratteri, in base alla loro posizione. Excel fornisce diverse funzioni integrate per aiutarti a estrarre porzioni specifiche di testo. Alcune delle più comuni sono LEFT(), RIGHT(), MID(), TEXTBEFORE() e TEXTAFTER(). Vediamole una per una. 

Usare la funzione substring LEFT() di Excel

Puoi usare la funzione LEFT() per estrarre i caratteri dall'inizio di una stringa di testo. Ecco la sintassi:

=LEFT(text, [num_chars])

Qui: 

• text si riferisce all'indirizzo della cella in cui si trova la stringa di testo originale.

• num_chars indica il numero di caratteri da estrarre. Il valore predefinito è 1.

Vediamo un esempio. Ho dei codici prodotto e voglio estrarre i primi tre caratteri.

=LEFT(A2,3)

Recupera i primi tre caratteri con la funzione LEFT(). Immagine dell’autore.

Usare la funzione substring RIGHT() di Excel

La funzione RIGHT() in Excel estrae i caratteri dalla fine di una stringa di testo. Ecco la sintassi:

=Right(text, [num_chars])

Qui: 

• text si riferisce all'indirizzo della cella in cui si trova la stringa di testo originale.

• num_chars indica il numero di caratteri da estrarre. Il valore predefinito è 1.

Vediamo un esempio. Qui uso la funzione RIGHT() per estrarre gli ultimi tre caratteri.

=RIGHT(A2, 3)

Recupera gli ultimi tre caratteri con la funzione RIGHT(). Immagine dell’autore.

Usare la funzione substring MID() di Excel

La funzione MID() estrae i caratteri dal centro di una stringa di testo. Ecco la sintassi:

=MID(text, start_num, num_chars)

Qui:

• text si riferisce all'indirizzo della cella in cui si trova la stringa di testo originale.

• num_chars indica il numero di caratteri da estrarre. Il valore predefinito è 1.

• start_num indica la posizione iniziale all'interno della stringa di testo.

In questo esempio uso la formula MID() per estrarre il valore centrale della stringa a partire dal quarto carattere.

=MID(A2,4,3)

Recupera i tre caratteri centrali con la funzione MID(). Immagine dell’autore. 

Ed ecco fatto. =MID(A2,4,3) estrae tre caratteri dal testo nella cella A2 a partire dal 4° carattere.

Usare la funzione substring FIND() di Excel

La funzione FIND() in Excel individua una sottostringa all'interno di una stringa di testo e restituisce la posizione del primo carattere della sottostringa. Ecco la sintassi:

FIND(find_text, within_text, [start_num]) 

Qui:

• find_text si riferisce al testo che vogliamo cercare.

• within_text è il testo che contiene il testo che vogliamo trovare.

• start_num specifica la posizione di inizio della ricerca, con valore predefinito 1.

Consideriamo un semplice dataset per mostrare come funziona la funzione FIND() di Excel. In questo esempio, ho il nome DataCamp nella colonna Text e voglio trovare la posizione della sottostringa Camp. Questo restituisce 5 perché Camp inizia al quinto carattere del testo.

=FIND("Camp", A2)

Uso della funzione FIND() per estrarre la posizione del testo. Immagine dell’autore.

Usare la funzione substring SUBSTITUTE() di Excel

La funzione SUBSTITUTE() sostituisce una specifica sottostringa con un'altra. Ecco la sintassi:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Qui:

• text contiene il testo che vuoi modificare.

• old_text si riferisce al testo che vuoi sostituire.

• new_text è il testo con cui vuoi sostituire quello vecchio.

• instance_num specifica quale occorrenza del testo vuoi cambiare. Se non specificato, vengono sostituite tutte le occorrenze. 

Per capirlo, vediamo un semplice dataset che contiene stringhe di testo con delimitatori specifici. La colonna Text elenca nomi ed età formattati con punto e virgola come delimitatori. Per ripulire, digito la seguente formula:

=SUBSTITUTE(A2, ";" , ",")

Qui:

• A2 si riferisce alla cella che contiene il testo originale.

• ";" specifica il testo da modificare.

• "," è il testo con cui vogliamo sostituire quello vecchio.

Uso della funzione SUBSTITUTE per sostituire il testo vecchio. Immagine dell’autore.

E questo è tutto. Applicando la nostra formula nella cella B2, Excel sostituisce il punto e virgola con una virgola.

Estrarre testo per delimitatore con le funzioni substring di Excel

I delimitatori sono caratteri specifici che separano due stringhe di testo, come punto e virgola o virgola. In Excel, puoi usare le funzioni TEXTBEFORE() e TEXTAFTER() per estrarre testo quando sono presenti dei delimitatori.  

Usare la funzione substring TEXTBEFORE() di Excel 

Come suggerisce il nome, la funzione TEXTBEFORE() estrae il testo che si trova prima di un carattere o di una sottostringa specificata. La sintassi è la seguente:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Qui: 

• text si riferisce al testo originale.

• delimiter è in genere una virgola o un punto e virgola.

• instance_num è l'occorrenza desiderata del delimitatore. Il valore predefinito è 1.

• match_mode indica se la ricerca del delimitatore debba distinguere tra maiuscole e minuscole (TRUE, predefinito) o no (FALSE).

• match_end determina se la fine della stringa di testo debba essere trattata come un delimitatore. Se TRUE, la funzione restituirà il testo originale se il delimitatore non viene trovato.

• if_not_found specifica un valore personalizzato da restituire se il delimitatore non viene trovato.

Ora vediamo un esempio pratico. Ho una colonna Text che contiene i dettagli dei dipendenti, inclusi nomi, reparti e sedi. Voglio estrarre solo i nomi e i reparti.

=TEXTBEFORE(A2, ",", 2, 1, 1)

Uso della funzione TEXTBEFORE() per estrarre i dati. Immagine dell’autore 

Ecco come funziona: 

• A2 contiene il testo da cui voglio estrarre. 

• "," è il delimitatore che separa il testo nei miei dati originali. 

• 2 significa che la funzione considererà la seconda occorrenza del delimitatore.

• 1 ignora la distinzione tra maiuscole e minuscole, se presente.

• 1 (alla fine) restituisce il testo originale se il delimitatore è assente.

Usare la funzione substring TEXTAFTER() di Excel

La funzione TEXTAFTER() è simile a TEXTBEFORE(): l’unica differenza è nel risultato. TEXTAFTER() estrae il testo che viene dopo un delimitatore. La sintassi è la seguente:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Qui: 

• text si riferisce al testo originale.

• delimiter è una virgola o un punto e virgola. 

• instance_num è l'occorrenza desiderata del delimitatore. Il valore predefinito è 1.

• match_mode indica se la ricerca del delimitatore debba distinguere tra maiuscole e minuscole (TRUE, predefinito) o no (FALSE).

• match_end determina se la fine della stringa di testo debba essere trattata come un delimitatore. Se TRUE, la funzione restituirà il testo originale se il delimitatore non viene trovato.

• if_not_found specifica un valore personalizzato da restituire se il delimitatore non viene trovato.

Qui ho una colonna Text con i dettagli dei dipendenti, inclusi nomi, reparti e sedi. Da questi, voglio estrarre le sedi e gli ID dei dipendenti. 

=TEXTAFTER(A2,",",2,1,1)

Uso della funzione TEXTAFTER() per estrarre i dati. Immagine dell’autore 

Ecco come funziona: 

• A2 contiene il testo da cui voglio estrarre. 

• ","  è il delimitatore che separa il testo nei miei dati originali. 

• 2 significa che la funzione considererà la seconda occorrenza del delimitatore. 

• 1 ignora la distinzione tra maiuscole e minuscole, se presente.

• 1 (alla fine) restituisce il testo originale se il delimitatore è assente.

Casi d’uso specifici delle substring in Excel

Ora che conosci le basi delle substring, è il momento di imparare i metodi avanzati. Questi metodi consentono manipolazioni del testo più complesse, rendendo le attività di elaborazione dei dati ancora più efficienti. 

Gestire lunghezze variabili dei nomi propri

Ho un dataset che contiene il nome completo, ma voglio estrarre solo il nome proprio.

Una tabella che contiene nomi completi. Immagine dell’autore.

Potresti pensare che sia semplice: usa la funzione LEFT() per ottenere il nome. Così ho provato con la formula:

=LEFT(A2,4)

Uso di LEFT() per ottenere il nome. Immagine dell’autore.

Ma invece del nome, ho ottenuto i primi quattro caratteri perché ho specificato num_char uguale a 4, visto che Jane ha quattro caratteri nel nome. Quando copio e incollo la formula, non restituisce l’output desiderato per gli altri nomi.

Serve una soluzione più flessibile. Per estrarre il nome a prescindere dalla lunghezza, puoi combinare LEFT() con FIND(). Uso FIND() per trovare la posizione del primo spazio nel nome completo, che separa nome e cognome. Poi uso LEFT() per estrarre i caratteri fino a quella posizione. 

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

Combina FIND() e LEFT() per estrarre il nome. Immagine dell’autore.

Ecco il dettaglio della formula per capire come funziona:

• La cella A2 contiene il nome completo.

• FIND(" ", A2, 1) trova la posizione del primo spazio nella stringa di testo nella cella A2 partendo dal primo carattere.

• -1 serve a rimuovere lo spazio.

• LEFT(A2, ...) estrae il numero di caratteri specificato dall'inizio della stringa.

Estrarre il dominio da un indirizzo email

Vediamo un altro esempio per capire se LEFT() e FIND() gestiscono uno scenario diverso. Ho un dataset di indirizzi email da cui devo estrarre il nome di dominio.

Dati contenenti indirizzi email. Immagine dell’autore.

Per farlo, creo un’altra colonna, Email Domain, e seleziono una cella diversa in cui digitare la seguente formula, che poi copio in altre tre celle:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Estrai il dominio con RIGHT(), LEN() e FIND(). Immagine dell’autore.

Ed ecco fatto: ho tutti i domini. Ma vediamo come Excel ottiene i risultati desiderati:

• FIND("@", A2) individua la posizione del simbolo @ nell’indirizzo email.

• LEN(A2) calcola la lunghezza totale dell’indirizzo email.

• LEN(A2) - FIND("@", A2) calcola il numero di caratteri dopo il simbolo @.

• Infine, RIGHT(A2, LEN(A2) - FIND("@", A2)) estrae quel numero di caratteri dalla fine della stringa.

Estrarre codici prodotto combinando LEFT(), MID() e RIGHT()

Finora ho spiegato come funzionano separatamente le funzioni LEFT(), MID() e RIGHT(). Ma puoi fare molto di più combinandole. Possono estrarre parti diverse di una stringa in base a schemi specifici.

Qui ho un elenco di codici prodotto e voglio estrarne una sezione specifica. 

• Dalla prima sezione, voglio un carattere.
• Dalla seconda sezione, voglio tutti e quattro i caratteri.
• Dall’ultima sezione, voglio estrarre gli ultimi tre caratteri.

Un elenco di codici prodotto. Immagine dell’autore.

Per riuscirci, combino le funzioni LEFT(), RIGHT() e MID() e inserisco la seguente formula:  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Combina MID(), LEFT() e RIGHT() per recuperare i dati. Immagine dell’autore.

Come vedi, questa formula combinata ha estratto i caratteri desiderati da ciascuna sezione dei codici. Ecco come:

• LEFT(A2, 1) estrae il primo carattere.

• MID(A2, FIND("-", A2) + 1, 4) trova il primo trattino, si sposta di un carattere a destra, quindi estrae quattro caratteri.

• "-" aggiunge un trattino.

• RIGHT(A2, 3) estrae gli ultimi tre caratteri.

• & concatena i caratteri. 

Usare SUBSTITUTE() con MID() per estrazioni dinamiche 

Ora vediamo come combinare la funzione MID() con SUBSTITUTE() per estrarre porzioni specifiche di una stringa e poi sostituire caratteri o sottostringhe all’interno della parte estratta.

Per esempio, ho i Dettagli Ordine dei clienti e voglio sostituire l’ID ordine con il testo Order confirmed.

Una tabella che contiene i dettagli degli ordini. Immagine dell’autore.

Uso la seguente formula:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

Funzioni SUBSTITUTE() e MID() per sostituire il testo. Immagine dell’autore.

Fatto! Questa formula estrae porzioni specifiche di testo, proprio come volevo.

• MID(A2, 1, 19) estrae i primi 19 caratteri dal testo nella cella A2.

• SUBSTITUTE(text, old_text, new_text) sostituisce le occorrenze di old_text con new_text.

Guardiamo un altro esempio: qui ho una colonna Product Detail e voglio estrarre la taglia del prodotto dalle parentesi quadre.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Estrazione della taglia del prodotto dalle parentesi quadre. Immagine dell’autore.

Fatto: ha estratto tutti i dettagli.

• FIND("[", A2) trova la posizione iniziale della parentesi quadra.

• FIND("]", A2) trova la posizione finale della parentesi quadra.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) estrae il testo tra le due parentesi.

• SUBSTITUTE(...,"[", "") rimuove la parentesi quadra di apertura dal testo estratto.

Ripulire le voci di dati

A volte i nostri dati sono disordinati, ma non per questo devi usarli così come sono. Qui ho un elenco di numeri di telefono in più formati, ma voglio standardizzarli in un formato coerente ripulendo eventuali incoerenze nelle voci.

Numeri di telefono non formattati. Immagine dell’autore.

Per standardizzarli, creo un’altra colonna chiamata Clean data. Digito la seguente formula nella seconda cella e la copio in tutte le celle in cui voglio vedere i risultati:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Qui: 

• SUBSTITUTE(A2, "-", "") rimuove i trattini.

• SUBSTITUTE(..., "(", "") rimuove le parentesi aperte.

• SUBSTITUTE(..., ")", "") rimuove le parentesi chiuse.

• SUBSTITUTE(..., ".", "") rimuove i punti.

Questo trasforma i vari formati in una stringa continua di cifre, come mostrato sotto. Ora i dati sono puliti, ben formattati e pronti all’uso. Puoi anche lasciarli così, se vuoi.

Ripulisci i dati con la funzione SUBSTITUTE(). Immagine dell’autore.

Formattare il testo per i report

Se vuoi fare un passo in più dopo la pulizia dei dati e formattarli in modo appropriato, puoi usare anche diverse funzioni substring.

Riprendendo l’esempio precedente, in cui ho ripulito i numeri di telefono, ora voglio cambiare la formattazione dei numeri in modo che sembrino numeri di telefono e non solo una stringa di cifre. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Formato standard dei numeri usando più substring. Immagine dell’autore.

Fatto. Ora ho tutti i numeri come volevo. Ecco come funziona questa formula:

• LEFT(B2, 3) estrae le prime tre cifre.

• MID(B2, 4, 3) estrae le tre cifre successive a partire dalla quarta posizione.

• RIGHT(B2, 4) estrae le ultime quattro cifre.

• TEXT(..., "000") and TEXT(..., "0000") formatta ciascuna sezione per garantire il numero corretto di cifre con zeri iniziali se necessario.

• & "-" & unisce le sezioni formattate con trattini.

Considerazioni finali

Hai imparato a usare le funzioni substring di Excel come LEFT(), RIGHT(), MID(), TEXTBEFORE() e TEXTAFTER() per manipolare dati testuali. Che si tratti di estrarre parti specifiche di una stringa o di ripulire dati disordinati, queste funzioni possono rendere il tuo lavoro molto più semplice ed efficiente.

Ma con Excel c’è sempre altro da imparare. Il corso Introduction to Excel è il passo successivo perfetto se stai iniziando. Se vuoi approfondire l’analisi dei dati, prova il corso Data Analysis in Excel, che si abbina bene al nostro Data Manipulation in Excel Cheat Sheet. Ti aiuteranno a costruire basi solide e a portare le tue competenze al livello successivo.