Excel ha una serie di funzioni TEXT che possono fare miracoli. Puoi eseguire tutti i tipi di operazioni di taglio e taglio del testo utilizzando queste funzioni.
Una delle attività comuni per le persone che lavorano con i dati di testo è estrarre una sottostringa in Excel (ad esempio, ottenere psrt del testo da una cella).
Sfortunatamente, non esiste una funzione di sottostringa in Excel che possa farlo facilmente. Tuttavia, questo potrebbe ancora essere fatto utilizzando formule di testo e alcune altre funzionalità di Excel integrate.
Diamo prima un'occhiata ad alcune delle funzioni di testo che utilizzeremo in questo tutorial.
Funzioni di testo di Excel
Excel ha una gamma di funzioni di testo che renderebbero davvero facile estrarre una sottostringa dal testo originale in Excel. Ecco le funzioni di testo di Excel che utilizzeremo in questo tutorial:
- Funzione RIGHT: estrae i numeri di caratteri specificati dalla destra della stringa di testo.
- Funzione LEFT: estrae i numeri di caratteri specificati dalla sinistra della stringa di testo.
- Funzione MID: estrae i numeri di caratteri specificati dalla posizione iniziale specificata in una stringa di testo.
- Funzione TROVA: Trova la posizione iniziale del testo specificato nella stringa di testo.
- Funzione LEN: restituisce il numero di caratteri nella stringa di testo.
Estrai una sottostringa in Excel usando le funzioni
Supponiamo di avere un set di dati come mostrato di seguito:
Questi sono alcuni ID e-mail casuali (ma da supereroi) (tranne il mio) e negli esempi seguenti ti mostrerò come estrarre il nome utente e il nome di dominio utilizzando le funzioni di testo in Excel.
Esempio 1 - Estrazione di nomi utente da ID e-mail
Durante l'utilizzo delle funzioni di testo, è importante identificare un modello (se presente). Ciò rende davvero facile costruire una formula. Nel caso sopra, il pattern è il segno @ tra il nome utente e il nome di dominio e lo useremo come riferimento per ottenere i nomi utente.
Ecco la formula per ottenere il nome utente:
=SINISTRA(A2,TROVA("@",A2)-1)
La formula sopra utilizza la funzione LEFT per estrarre il nome utente identificando la posizione del segno @ nell'id. Questo viene fatto usando la funzione FIND, che restituisce la posizione di @.
Ad esempio, nel caso di [email protected], FIND(“@”,A2) restituirebbe 11, che è la sua posizione nella stringa di testo.
Ora usiamo la funzione LEFT per estrarre 10 caratteri dalla sinistra della stringa (uno in meno del valore restituito dalla funzione LEFT).
Esempio 2 - Estrazione del nome di dominio dagli ID e-mail
La stessa logica utilizzata nell'esempio precedente può essere utilizzata per ottenere il nome di dominio. Una piccola differenza qui è che dobbiamo estrarre i caratteri dalla destra della stringa di testo.
Ecco la formula che lo farà:
=DESTRA(A2,LEN(A2)-TROVA("@",A2))
Nella formula sopra, usiamo la stessa logica, ma la regoliamo per assicurarci di ottenere la stringa corretta.
Prendiamo ancora l'esempio di [email protected]. La funzione TROVA restituisce la posizione del segno @, che in questo caso è 11. Ora, dobbiamo estrarre tutti i caratteri dopo il @. Quindi identifichiamo la lunghezza totale della stringa e sottraiamo il numero di caratteri fino alla @. Ci dà il numero di caratteri che coprono il nome di dominio sulla destra.
Ora possiamo semplicemente usare la funzione RIGHT per ottenere il nome di dominio.
Esempio 3 - Estrazione del nome di dominio dagli ID e-mail (senza .com)
Per estrarre una sottostringa dal centro di una stringa di testo, è necessario identificare la posizione del marker subito prima e dopo la sottostringa.
Ad esempio, nell'esempio seguente, per ottenere il nome di dominio senza la parte .com, l'indicatore sarebbe @ (che è subito prima del nome di dominio) e . (che è subito dopo).
Ecco la formula che estrarrà solo il nome di dominio:
=MID(A2,TROVA("@",A2)+1,TROVA(".",A2)-TROVA("@",A2)-1)
La funzione MID di Excel estrae il numero di caratteri specificato dalla posizione iniziale specificata. In questo esempio sopra, FIND(“@”,A2)+1 specifica la posizione iniziale (che è subito dopo la @) e FIND(“.”,A2)-FIND(“@”,A2)-1 identifica la numero di caratteri tra '@' e il '.‘
Aggiornare: Uno dei lettori William19 ha affermato che la formula sopra non funzionerebbe nel caso in cui ci fosse un punto (.) nell'id e-mail (ad esempio, [email protected]). Quindi ecco la formula per affrontare questi casi:
=MID(A1,TROVA("@",A1)+1,TROVA(".",A1,TROVA("@",A1))-TROVA("@",A1)-1)
Utilizzo di testo in colonne per estrarre una sottostringa in Excel
L'utilizzo di funzioni per estrarre una sottostringa in Excel ha il vantaggio di essere dinamico. Se modifichi il testo originale, la formula aggiornerà automaticamente i risultati.
Se questo è qualcosa di cui potresti non aver bisogno, l'utilizzo della funzione Testo su colonne potrebbe essere un modo semplice e veloce per dividere il testo in sottostringhe in base a marcatori specificati.
Ecco come farlo:
- Seleziona le celle in cui hai il testo.
- Vai a Dati -> Strumenti dati -> Testo in colonne.
- Nel passaggio 1 della procedura guidata da testo a colonna, selezionare Delimitato e premere Avanti.
- Nel passaggio 2, seleziona l'opzione Altro e inserisci @ nella casella a destra. Questo sarà il nostro delimitatore che Excel utilizzerà per dividere il testo in sottostringhe. Puoi vedere l'anteprima dei dati qui sotto. Fare clic su Avanti.
- Nel passaggio 3, l'impostazione Generale funziona bene in questo caso. Tuttavia, puoi scegliere un formato diverso se stai dividendo numeri/date. Per impostazione predefinita, la cella di destinazione è dove hai i dati originali. Se vuoi mantenere intatti i dati originali, cambialo in un'altra cella.
- Fare clic su Fine.
Questo ti darà immediatamente due set di sottostringhe per ogni ID email usato in questo esempio.
Se vuoi dividere ulteriormente il testo (ad esempio, dividi batman.com in batman e com), ripeti lo stesso processo con esso.
Utilizzo di TROVA e SOSTITUISCI per estrarre il testo da una cella in Excel
TROVA e SOSTITUISCI possono essere una tecnica potente quando si lavora con il testo in Excel. Negli esempi seguenti, imparerai come usare TROVA e SOSTITUISCI con caratteri jolly per fare cose incredibili in Excel.
Guarda anche: Scopri tutto sui caratteri jolly in Excel.
Prendiamo gli stessi esempi di ID email.
Esempio 1 - Estrazione di nomi utente da ID e-mail
Ecco i passaggi per estrarre i nomi utente dagli ID e-mail utilizzando la funzionalità Trova e sostituisci:
- Copia e incolla i dati originali. Poiché Trova e sostituisci funziona e altera i dati su cui viene applicato, è meglio avere un backup dei dati originali.
- Seleziona i dati e vai su Home -> Modifica -> Trova e seleziona -> Sostituisci (o usa la scorciatoia da tastiera Ctrl + H).
- Nella finestra di dialogo Trova e sostituisci, inserisci quanto segue:
- Trovare cosa: @*
- Sostituisci con: (lascia vuoto)
- Fare clic su Sostituisci tutto.
Questo rimuoverà istantaneamente tutto il testo prima della @ negli ID e-mail. Avrai il risultato come mostrato di seguito:
Come funziona?? - Nell'esempio sopra, abbiamo usato una combinazione di @ e *. Un asterisco (*) è un carattere jolly che rappresenta un numero qualsiasi di caratteri. Quindi, @* significherebbe una stringa di testo che inizia con @ e può avere un numero qualsiasi di caratteri dopo di essa. Ad esempio, in [email protected], @* sarebbe @batman.com. Quando sostituiamo @* con uno spazio vuoto, vengono rimossi tutti i caratteri dopo @ (incluso @).
Esempio 2 - Estrazione del nome di dominio dagli ID e-mail
Utilizzando la stessa logica, puoi modificare i criteri "Trova cosa" per ottenere il nome di dominio.
Ecco i passaggi:
- Seleziona i dati.
- Vai su Home -> Modifica -> Trova e seleziona -> Sostituisci (o usa la scorciatoia da tastiera Ctrl + H).
- Nella finestra di dialogo Trova e sostituisci, inserisci quanto segue:
- Trovare cosa: *@
- Sostituisci con: (lascia vuoto)
- Fare clic su Sostituisci tutto.
Questo rimuoverà istantaneamente tutto il testo prima della @ negli ID e-mail. Avrai il risultato come mostrato di seguito:
