Formula generica
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Sommario
Per trovare e sostituire più valori con una formula, puoi nidificare più funzioni SOSTITUISCI insieme e inserire coppie di ricerca / sostituzione da un'altra tabella utilizzando la funzione INDICE. Nell'esempio mostrato, stiamo eseguendo 4 operazioni separate di ricerca e sostituzione. La formula in G5 è:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
dove "trova" è l'intervallo denominato E5: E8 e "sostituisci" è l'intervallo denominato F5: F8. Vedi sotto per informazioni su come rendere questa formula più facile da leggere.
Prefazione
Non esiste una formula incorporata per eseguire una serie di operazioni di ricerca e sostituzione in Excel, quindi questa è una formula "concettuale" per mostrare un approccio. Il testo da cercare e sostituire con viene memorizzato direttamente nel foglio di lavoro in una tabella e recuperato con la funzione INDICE. Ciò rende la soluzione "dinamica": uno qualsiasi di questi valori viene modificato, i risultati vengono aggiornati immediatamente. Ovviamente non è necessario utilizzare INDEX; puoi inserire i valori nella formula se preferisci.
Spiegazione
Al centro, la formula utilizza la funzione SOSTITUISCI per eseguire ogni sostituzione, con questo schema di base:
=SUBSTITUTE(text,find,replace)
"Testo" è il valore in entrata, "trova" è il testo da cercare e "sostituisci" è il testo con cui sostituire. Il testo da cercare e sostituire è memorizzato nella tabella a destra, nell'intervallo E5: F8, una coppia per riga. I valori a sinistra sono nell'intervallo denominato "trova" e i valori a destra sono nell'intervallo denominato "sostituisci". La funzione INDICE viene utilizzata per recuperare sia il testo "trova" che il testo "sostituisci" in questo modo:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Quindi, per eseguire la prima sostituzione (cerca "rosso", sostituisci con "rosa") usiamo:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
In totale, eseguiamo quattro sostituzioni separate e ogni SOSTITUTO successivo inizia con il risultato del SOSTITUTO precedente:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Interruzioni di riga per la leggibilità
Noterai che questo tipo di formula annidata è abbastanza difficile da leggere. Aggiungendo interruzioni di riga, possiamo rendere la formula molto più facile da leggere e mantenere:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
La barra della formula in Excel ignora lo spazio bianco aggiuntivo e le interruzioni di riga, quindi la formula sopra può essere incollata direttamente:
A proposito, c'è una scorciatoia da tastiera per espandere e comprimere la barra della formula.
Più sostituzioni
È possibile aggiungere più righe alla tabella per gestire più coppie di ricerca / sostituzione. Ogni volta che viene aggiunta una coppia, la formula deve essere aggiornata per includere la nuova coppia. È anche importante assicurarsi che gli intervalli denominati (se li si utilizza) siano aggiornati per includere nuovi valori secondo necessità. In alternativa, è possibile utilizzare una tabella Excel appropriata per gli intervalli dinamici, invece di intervalli denominati.
Altri usi
Lo stesso approccio può essere utilizzato per ripulire il testo "rimuovendo" la punteggiatura e altri simboli dal testo con una serie di sostituzioni. Ad esempio, la formula in questa pagina mostra come pulire e riformattare i numeri di telefono.
