Fórmula genérica
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Resumo
Para localizar e substituir vários valores por uma fórmula, você pode aninhar várias funções SUBSTITUTE juntas e alimentar pares de localização / substituição de outra tabela usando a função INDEX. No exemplo mostrado, estamos executando 4 operações separadas de localização e substituição. A fórmula em 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))
onde "encontrar" é o intervalo nomeado E5: E8 e "substituir" é o intervalo nomeado F5: F8. Veja abaixo informações sobre como tornar esta fórmula mais fácil de ler.
Prefácio
Não existe uma fórmula incorporada para executar uma série de operações de localização e substituição no Excel, portanto, esta é uma fórmula de "conceito" para mostrar uma abordagem. O texto a ser procurado e substituído é armazenado diretamente na planilha em uma tabela e recuperado com a função INDEX. Isso torna a solução "dinâmica" - qualquer um desses valores é alterado, os resultados são atualizados imediatamente. Obviamente, não há requisitos para usar INDEX; você pode embutir valores em código na fórmula, se preferir.
Explicação
Basicamente, a fórmula usa a função SUBSTITUTE para realizar cada substituição, com este padrão básico:
=SUBSTITUTE(text,find,replace)
"Text" é o valor recebido, "find" é o texto a ser procurado e "replace" é o texto pelo qual substituir. O texto a ser procurado e substituído é armazenado na tabela à direita, no intervalo E5: F8, um par por linha. Os valores à esquerda estão no intervalo nomeado "encontrar" e os valores à direita estão no intervalo nomeado "substituir". A função INDEX é usada para recuperar o texto "localizar" e o texto "substituir" como este:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Portanto, para executar a primeira substituição (procure por "vermelho", substitua por "rosa"), usamos:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
No total, executamos quatro substituições separadas e cada SUBSTITUTO subsequente começa com o resultado do SUBSTITUTO anterior:
=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))
Quebras de linha para legibilidade
Você notará que esse tipo de fórmula aninhada é bastante difícil de ler. Ao adicionar quebras de linha, podemos tornar a fórmula muito mais fácil de ler e manter:
= 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))
A barra de fórmulas no Excel ignora espaços em branco extras e quebras de linha, portanto, a fórmula acima pode ser colada diretamente:
A propósito, existe um atalho de teclado para expandir e recolher a barra de fórmulas.
Mais substituições
Mais linhas podem ser adicionadas à tabela para lidar com mais pares localizar / substituir. Cada vez que um par é adicionado, a fórmula precisa ser atualizada para incluir o novo par. É importante também certificar-se de que os intervalos nomeados (se você os estiver usando) sejam atualizados para incluir novos valores conforme necessário. Como alternativa, você pode usar uma Tabela do Excel adequada para intervalos dinâmicos, em vez de intervalos nomeados.
Outros usos
A mesma abordagem pode ser usada para limpar o texto "removendo" a pontuação e outros símbolos do texto com uma série de substituições. Por exemplo, a fórmula nesta página mostra como limpar e reformatar números de telefone.
