Quando si lavora con dati di testo in Excel, potrebbe essere necessario rimuovere il testo prima o dopo un carattere o una stringa di testo specifici.
Ad esempio, se hai il nome e i dati di designazione delle persone, potresti voler rimuovere la designazione dopo la virgola e mantenere solo il nome (o viceversa dove mantieni la designazione e rimuovi il nome).
A volte, può essere fatto con una formula semplice o un rapido Trova e sostituisci e, a volte, ha bisogno di formule o soluzioni alternative più complesse.
In questo tutorial, ti mostrerò come rimuovere il testo prima o dopo un carattere specifico in Excel (usando diversi esempi).
Quindi iniziamo con alcuni semplici esempi.
Rimuovi il testo dopo un carattere usando Trova e sostituisci
Se desideri rimuovere rapidamente tutto il testo dopo una stringa di testo specifica (o prima di una stringa di testo), puoi farlo utilizzando Trova e sostituisci e i caratteri jolly.
Supponiamo di avere un set di dati come mostrato di seguito e di voler rimuovere la designazione dopo il carattere virgola e mantenere il testo prima della virgola.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Copia e incolla i dati dalla colonna A alla colonna B (questo serve per mantenere anche i dati originali)
- Con le celle nella colonna B selezionate, fai clic sulla scheda Home
- Nel gruppo Modifica, fai clic sull'opzione Trova e seleziona
- Nelle opzioni visualizzate nel menu a discesa, fare clic sull'opzione Sostituisci. Si 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"
- Fare clic sul pulsante Sostituisci tutto
I passaggi precedenti dovrebbero trovare la virgola nel set di dati e rimuovere tutto il testo dopo la virgola (compresa la virgola).
Poiché questo sostituisce il testo dalle celle selezionate, è una buona idea copiare il testo in un'altra colonna e quindi eseguire questa operazione di ricerca e sostituzione o creare una copia di backup dei dati in modo da mantenere intatti i dati originali
Come funziona?
* (asterisco) è un carattere jolly che può rappresentare un numero qualsiasi di caratteri.
Quando lo uso dopo la virgola (nel campo "Trova cosa") e quindi faccio clic sul pulsante Sostituisci tutto, trova la prima virgola nella cella e la considera una corrispondenza.
Questo perché il segno 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.
Nota: questo metodo funziona bene quindi hai solo una virgola in ogni cella (come nel nostro esempio). Nel caso in cui tu abbia più virgole, questo metodo troverà sempre la prima virgola e quindi rimuoverà tutto ciò che segue. Quindi non puoi usare questo metodo se vuoi sostituire tutto il testo dopo la seconda virgola e mantenere il primo così com'è.Nel caso in cui si desideri rimuovere tutti i caratteri prima della virgola, modificare la voce del campo trova cosa inserendo il segno asterisco prima della virgola (*, invece di ,*)
Rimuovi il testo usando le formule
Se hai bisogno di un maggiore controllo su come trovare e sostituire il testo prima o dopo un carattere specifico, è meglio usare le formule di testo integrate in Excel.
Supponiamo di avere il set di dati di seguito Dove si desidera rimuovere tutto il testo dopo la virgola.
Di seguito è la formula per farlo:
=SINISTRA(A2,TROVA(",",A2)-1)
La formula sopra utilizza la funzione TROVA per trovare la posizione della virgola nella cella.
Questo numero di posizione viene quindi utilizzato dalla funzione SINISTRA per estrarre tutti i caratteri prima della virgola. Poiché non voglio la virgola come parte del risultato, ho sottratto 1 dal valore risultante della formula Trova.
Questo era uno scenario semplice.
Prendiamone uno un po' complesso.
Supponiamo di avere questo set di dati di seguito in cui voglio rimuovere tutto il testo dopo la seconda virgola.
Ecco la formula che lo farà:
=SINISTRA(LA2,TROVA("!",SOSTITUISCI(LA2,",","!",2))-1)
Poiché questo set di dati ha più virgole, non posso usare la funzione TROVA per ottenere la posizione della prima virgola ed estrarre tutto a sinistra di essa.
Devo in qualche modo scoprire la posizione della seconda virgola e quindi estrarre tutto ciò che si trova a sinistra della seconda virgola.
Per fare ciò, ho usato la funzione SOSTITUISCI 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 viene utilizzata nella funzione SINISTRA per estrarre tutto ciò che precede la seconda virgola.
Fin qui tutto bene!
Ma cosa succede se c'è un numero incoerente di virgole nel set di dati.
Ad esempio, nel set di dati seguente alcune celle hanno due virgole e alcune celle hanno tre virgole e devo estrarre tutto il testo prima dell'ultima virgola.
In questo caso, devo in qualche modo capire la posizione dell'ultima occorrenza della virgola e quindi estrarre tutto a sinistra di essa.
Di seguito è la formula che lo farà
=SINISTRA(LA2,TROVA("!",SOSTITUTO(LA2,",","!",LIM(A2)-LEN(SOSTITUTO(LA2,",",""))))-1)
La formula sopra utilizza la funzione LUNGHEZZA per trovare la lunghezza complessiva 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 quindi utilizzato all'interno della formula SOSTITUISCI per sostituire l'ultima virgola con un punto esclamativo. E poi puoi usare la funzione di sinistra per estrarre tutto ciò che si trova a sinistra del punto esclamativo (che è dove si trovava l'ultima virgola)
Come puoi vedere negli esempi, l'uso di una combinazione di formule di testo ti consente di gestire molte situazioni diverse.
Inoltre, poiché il risultato è collegato ai dati originali, quando si modificano i dati originali il risultato si aggiornerà automaticamente.
Rimuovi il testo usando il riempimento flash
Flash Fill è uno strumento introdotto in Excel 2013 ed è disponibile in tutte le versioni successive.
Funziona identificando i modelli quando si immettono manualmente i dati e quindi estrapolati per fornire i dati per l'intera colonna.
Quindi, se vuoi rimuovere il testo prima o dopo un carattere specifico, devi solo mostrare a fata flash come sarebbe il risultato (inserendolo manualmente un paio di volte), e il riempimento flash capirebbe automaticamente il modello e ti darebbe i risultati.
Lascia che te lo mostri con un esempio.
Di seguito ho il set di dati in cui voglio rimuovere tutto il testo dopo la virgola.
Ecco i passaggi per farlo utilizzando Flash Fill:
- Nella cella B2, che è la colonna adiacente dei nostri dati, inserisci manualmente "Jeffery Haggins" (che è il risultato previsto)
- Nella cella B3, inserisci "Tim Scott" (che è il risultato previsto per la seconda cella)
- Seleziona la gamma B2:B10
- Fare clic sulla scheda Home
- Nel gruppo Modifica, fai clic sull'opzione a discesa Riempi
- Fare clic su Riempimento flash
I passaggi precedenti ti darebbero il risultato come mostrato di seguito:
Puoi anche usare la scorciatoia da tastiera Riempimento flash Ctrl + Mi 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 può darti risultati errati.
Quindi assicurati di ricontrollare i risultati di Flash Fill
E proprio come abbiamo rimosso tutto il testo dopo un carattere specifico utilizzando il riempimento flash, puoi utilizzare gli stessi passaggi per rimuovere il testo prima di un carattere specifico. mostra solo il riempimento flash come il risultato dovrebbe apparire come il mio Internet manualmente nella colonna adiacente, e farebbe il resto.
Rimuovi testo utilizzando VBA (funzione personalizzata)
L'intero concetto di rimozione del testo prima o dopo un carattere specifico dipende dalla ricerca della posizione di quel carattere.
Come visto sopra, trovare l'ultima occorrenza di quel personaggio significa usare un intruglio di formule.
Se questo è qualcosa che devi fare abbastanza spesso, puoi semplificare questo processo creando una funzione personalizzata usando VBA (chiamata Funzioni definite dall'utente).
Una volta creata, puoi riutilizzare questa funzione ancora e ancora. È anche molto più semplice e facile da usare (poiché la maggior parte del lavoro pesante viene svolto dal codice VBA nel back-end).
Sotto il codice VBA che puoi utilizzare per creare la funzione personalizzata in Excel:
Function LastPosition(rCell As Range, rChar As String) 'Questa funzione fornisce l'ultima posizione del carattere specificato 'Questo codice è stato sviluppato da Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len(rCell) For i = rLen To 1 Step -1 If Mid(rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Exit Function End If Next i End Function
È necessario inserire il codice VBA in un modulo normale nell'editor VB o nella cartella di lavoro macro personale. Una volta che lo hai lì, puoi usarlo come qualsiasi altra normale funzione del foglio di lavoro nella cartella di lavoro.
Questa funzione accetta 2 argomenti:
- Il riferimento di cella per il quale si desidera trovare l'ultima occorrenza di un carattere o una stringa di testo specifici
- Il carattere o la stringa di testo di cui devi trovare la posizione
Supponiamo ora di avere il set di dati seguente e di voler rimuovere tutto il testo dopo l'ultima virgola e di avere solo il testo prima dell'ultima virgola.
Di seguito è la formula che lo farà:
=SINISTRA(A2,UltimaPosizione(A2,",")-1)
Nella formula sopra, ho specificato di trovare la posizione dell'ultima virgola. Nel caso in cui desideri trovare la posizione di qualche altro carattere o stringa di testo, dovresti usarlo come secondo argomento nella funzione.
Come puoi vedere, questo è molto più breve e più facile da usare, rispetto alla formula di testo lungo che abbiamo usato nella sezione precedente
Se inserisci il codice VBA in un modulo in una cartella di lavoro, sarai in grado di utilizzare questa funzione personalizzata solo in quella specifica cartella di lavoro. Se vuoi usarlo in tutte le cartelle di lavoro sul 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 rapidamente il testo prima o dopo un carattere specifico in Excel.
Se è una cosa semplice una tantum, puoi farlo usando Trova e sostituisci. cosa succede se è un po 'più complesso, è necessario utilizzare una combinazione di formule Excel integrate o persino creare la propria formula personalizzata utilizzando VBA.
Spero che tu abbia trovato utile questo tutorial.
