Quando si lavora con dati di testo in Excel, si può avere la necessità di rimuovere il testo prima o dopo un carattere specifico o una stringa di testo.
Per esempio, se si hanno i dati del nome e della designazione delle persone, si potrebbe voler rimuovere la designazione dopo la virgola e mantenere solo il nome (o viceversa dove si mantiene la designazione e si rimuove il nome).
A volte, può essere fatto con una semplice formula o un rapido Trova e sostituisci, e a volte, ha bisogno di formule più complesse o workaround.
In questo tutorial, vi mostrerò come rimuovere il testo prima o dopo un carattere specifico in Excel (usando diversi esempi).
Partiamo quindi con alcuni semplici esempi.
Questo tutorial copre:
Rimuovere il testo dopo un carattere usando Trova e sostituisci
Se vuoi rimuovere velocemente tutto il testo dopo una specifica stringa di testo (o prima di una stringa di testo), puoi farlo usando Trova e sostituisci e i caratteri jolly.
Supponiamo che abbiate un set di dati come mostrato qui sotto e che vogliate rimuovere la denominazione dopo il carattere virgola e mantenere il testo prima della virgola.
Di seguito i passi per farlo:
- Copia e incolla i dati dalla colonna A alla colonna B (questo per mantenere anche i dati originali)
- Con le celle della colonna B selezionate, fare clic sulla scheda Home
- Nel gruppo Modifica, fare clic sull’opzione Trova & Seleziona
- Nelle opzioni che appaiono nel menu a discesa, fare clic sull’opzione Sostituisci. Questo aprirà la finestra di dialogo Trova e sostituisci
- Nel campo ‘Trova cosa’, inserisci ,* (cioè, virgola seguita da un asterisco)
- Lascia vuoto il campo ‘Sostituisci con’
- Clicca sul pulsante Sostituisci tutto
I passi di cui sopra dovrebbero trovare la virgola nel set di dati e rimuovere tutto il testo dopo la virgola (inclusa la virgola).
Siccome questo sostituisce il testo dalle celle selezionate, è una buona idea copiare il testo in un’altra colonna e poi eseguire questa operazione di ricerca e sostituzione, o creare una copia di backup dei dati in modo da avere i dati originali intatti
Come funziona?
* (segno di asterisco) è un carattere jolly che può rappresentare qualsiasi numero di caratteri.
Quando lo uso dopo la virgola (nel campo ‘Trova cosa’) e poi clicco sul pulsante Sostituisci tutto, trova la prima virgola nella cella e la considera una corrispondenza.
Questo perché il segno dell’asterisco (*) è considerato una corrispondenza con l’intera stringa di testo che segue la virgola.
Quindi quando premi il pulsante sostituisci tutto, sostituisce la virgola e tutto il testo che segue.
Nel caso in cui vogliate rimuovere tutti i caratteri prima della virgola, cambiate la voce del campo Trova cosa avendo il segno dell’asterisco prima della virgola (*, invece di ,*)
Rimuovere il testo usando le formule
Se avete bisogno di più controllo su come trovare e sostituire il testo prima o dopo un carattere specifico, è meglio usare le formule di testo incorporate in Excel.
Supponiamo che abbiate il seguente set di dati dove volete rimuovere tutto il testo dopo la virgola.
Di seguito la formula per farlo:
=LEFT(A2,FIND(",",A2)-1)
La formula precedente usa la funzione FIND per trovare la posizione della virgola nella cella.
Questo numero di posizione è poi usato dalla funzione LEFT per estrarre tutti i caratteri prima della virgola. Siccome non voglio la virgola come parte del risultato, ho sottratto 1 dal valore risultante della formula Find.
Questo era uno scenario semplice.
Prendiamo uno scenario leggermente complesso.
Supponiamo di avere questa serie di dati qui sotto dove voglio rimuovere tutto il testo dopo la seconda virgola.
Ecco la formula che lo farà:
=LEFT(A2,FIND("!",SUBSTITUTE(A2,",","!",2))-1)
Siccome questo set di dati ha più virgole, non posso usare la funzione FIND per ottenere la posizione della prima virgola ed estrarre tutto a sinistra di essa.
Ho bisogno di trovare in qualche modo la posizione della seconda virgola e poi estrarre tutto ciò che è a sinistra della seconda virgola.
Per fare questo, ho usato la funzione SUBSTITUTE per cambiare la seconda virgola in un punto esclamativo. Ora questo mi dà un carattere unico nella cella. Ora posso usare la posizione del punto esclamativo per estrarre tutto a sinistra della seconda virgola.
Questa posizione del punto esclamativo è usata nella funzione LEFT per estrarre tutto prima della seconda virgola.
Fin qui tutto bene!
Ma cosa succede se c’è un numero inconsistente di virgole nel set di dati.
Per esempio, nel seguente set di dati alcune celle hanno due virgole e alcune celle hanno tre virgole, e ho bisogno di estrarre tutto il testo prima dell’ultima virgola.
In questo caso, ho bisogno di capire in qualche modo la posizione dell’ultima occorrenza della virgola e poi estrarre tutto a sinistra di essa.
Di seguito la formula che lo farà
=LEFT(A2,FIND("!",SUBSTITUTE(A2,",","!",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)
La formula qui sopra usa la funzione LEN per trovare la lunghezza totale del testo nella cella e la lunghezza del testo senza la virgola.
Quando sottraggo questi due valori, mi dà il numero totale di virgole nella cella.
Quindi, mi darebbe 3 per la cella A2 e 2 per la cella A4.
Questo valore viene poi usato nella formula SUBSTITUTE per sostituire l’ultima virgola con un punto esclamativo. E poi si può usare la funzione sinistra per estrarre tutto ciò che si trova a sinistra del punto esclamativo (che è dove si trovava l’ultima virgola)
Come potete vedere negli esempi, usare una combinazione di formule di testo vi permette di gestire molte situazioni diverse.
Inoltre, poiché il risultato è collegato ai dati originali, quando si cambiano i dati originali il risultato si aggiorna automaticamente.
Rimuovere il testo usando Flash Fill
Flash Fill è uno strumento che è stato introdotto in Excel 2013 ed è disponibile in tutte le versioni successive.
Funziona identificando i modelli quando si inseriscono manualmente i dati e poi estrapolati per darvi i dati per l’intera colonna.
Così se volete rimuovere il testo prima o dopo un carattere specifico, avete solo bisogno di mostrare a flash fairy come sarebbe il risultato (inserendolo manualmente un paio di volte), e flash fill capirà automaticamente il modello e vi darà i risultati.
Vi mostro questo con un esempio.
Di seguito ho l’insieme di dati dove voglio rimuovere tutto il testo dopo la virgola.
Questi sono i passi per farlo usando Flash Fill:
- Nella cella B2, che è la colonna adiacente dei nostri dati, inserire manualmente ‘Jeffery Haggins’ (che è il risultato atteso)
- Nella cella B3, inserire ‘Tim Scott’ (che è il risultato atteso per la seconda cella)
- Selezionare l’intervallo B2:B10
- Fare clic sulla scheda Home
- Nel gruppo Editing, fare clic sull’opzione a discesa Fill
- Fare clic su Flash Fill
I passi di cui sopra vi daranno il risultato come mostrato di seguito:
Puoi anche usare la scorciatoia da tastiera Flash Fill Control + E dopo aver selezionato le celle nella colonna dei risultati (Colonna B nel nostro esempio)
Flash Fill è uno strumento meraviglioso e nella maggior parte dei casi, è in grado di riconoscere il modello e darti il giusto risultato. Ma in alcuni casi, potrebbe non essere in grado di riconoscere correttamente il modello e darvi risultati sbagliati.
Assicuratevi quindi di controllare due volte i risultati di Flash Fill
E proprio come abbiamo rimosso tutto il testo dopo un carattere specifico usando flash fill, potete usare gli stessi passi per rimuovere il testo prima di un carattere specifico. basta mostrare flash fill come il risultato dovrebbe essere come il mio Internet manualmente nella colonna adiacente, e lui farà il resto.
Rimuovere il testo usando VBA (funzione personalizzata)
L’intero concetto di rimuovere il testo prima o dopo un carattere specifico dipende dal trovare la posizione di quel carattere.
Come visto sopra, trovare l’ultima occorrenza di quel carattere significa usare un intruglio di formule.
Se questo è qualcosa che dovete fare abbastanza spesso, potete semplificare questo processo creando una funzione personalizzata usando VBA (chiamata User Defined Functions).
Una volta creata, potete riutilizzare questa funzione più volte. È anche molto più semplice e facile da usare (dato che la maggior parte del lavoro pesante è fatto dal codice VBA nel back-end).
Di seguito il codice VBA che puoi usare per creare la funzione personalizzata in Excel:
Function LastPosition(rCell As Range, rChar As String)'This function gives the last position of the specified character'This code has been developed by Sumit Bansal (https://trumpexcel.com)Dim rLen As IntegerrLen = Len(rCell)For i = rLen To 1 Step -1If Mid(rCell, i - 1, 1) = rChar ThenLastPosition = i - 1Exit FunctionEnd IfNext iEnd Function
Devi mettere il codice VBA in un modulo regolare nell’editor VB, o nella cartella di lavoro Macro personale. Una volta che l’avete messo lì, potete usarlo come qualsiasi altra normale funzione del foglio di lavoro nella cartella di lavoro.
Questa funzione prende 2 argomenti:
- Il riferimento alla cella per la quale volete trovare l’ultima occorrenza di uno specifico carattere o stringa di testo
- Il carattere o la stringa di testo di cui dovete trovare la posizione
Supponiamo di avere il seguente set di dati e di voler rimuovere tutto il testo dopo l’ultima virgola e avere solo il testo prima dell’ultima virgola.
Di seguito la formula che lo farà:
=LEFT(A2,LastPosition(A2,",")-1)
Nella formula sopra, ho specificato di trovare la posizione dell’ultima virgola. Nel caso vogliate trovare la posizione di qualche altro carattere o stringa di testo, dovreste usarlo come secondo argomento nella funzione.
Come potete vedere questo è molto più corto e facile da usare, rispetto alla lunga formula di testo che abbiamo usato nella sezione precedente
Se mettete il codice VBA in un modulo in una cartella di lavoro sarete in grado di usare questa funzione personalizzata solo in quella specifica cartella di lavoro. Se vuoi usarla in tutte le cartelle di lavoro del tuo sistema, devi copiare e incollare questo codice nella cartella di lavoro Macro personale.
Quindi queste sono alcune delle mail che puoi usare per rimuovere velocemente il testo prima o dopo un carattere specifico in Excel.
Se si tratta di una semplice cosa una tantum, allora puoi farlo usando trova e sostituisci. cosa se è un po’ più complesso, allora devi usare una combinazione di formule Excel incorporate, o anche creare la tua formula personalizzata usando VBA.
Spero che abbiate trovato utile questo tutorial.
Altri tutorial di Excel che potrebbero piacerti:
- Conta i caratteri in una cella (o intervallo di celle) usando le formule in Excel
- Come rimuovere il primo carattere da una stringa in Excel
- Come rimuovere la virgola in Excel (da testo e numeri)
- Come estrarre la prima parola da una stringa di testo in Excel
- Separare nome e cognome in Excel (dividere i nomi usando le formule)