Excel: como trocar texto correspondente com SUBSTITUIR

No Excel, use SUBSTITUIR para trocar uma palavra ou símbolo específico. Se omitir o quarto argumento, todas as ocorrências são substituídas. Informe 2 para mudar apenas a segunda. A função diferencia maiúsculas de minúsculas e retorna o resultado separadamente da célula de origem.

Informar o texto original, o que buscar e o substituto

=SUBSTITUIR(text;old_text;new_text;[instance_num])

O primeiro argumento é o texto original ou a referência à sua célula. O segundo é o texto a procurar e o terceiro é o substituto. O quarto argumento é opcional e conta as ocorrências da esquerda para a direita. O número 2 indica a segunda ocorrência, qualquer que seja a posição.

Documentação da Microsoft sobre SUBSTITUIR

Comparar a troca de todas as ocorrências com a troca da segunda

Digite AB-CD-cd-CD em A2 e teste cada fórmula em uma célula vazia diferente. CD em maiúsculas aparece duas vezes. O cd em minúsculas no meio não corresponde.

Fórmula Resultado O que muda
=SUBSTITUIR(A2;"CD";"ZZ") AB-ZZ-cd-ZZ Troca os dois CD em maiúsculas
=SUBSTITUIR(A2;"CD";"ZZ";2) AB-CD-cd-ZZ Troca apenas o segundo CD em maiúsculas
=SUBSTITUIR(A2;"CD";"ZZ";3) AB-CD-cd-CD Retorna o original porque não há uma terceira ocorrência

Remover hífens e espaços mantendo os zeros à esquerda

Digite 00-27 04 como texto em A6. Se o Excel converter a entrada, comece com um apóstrofo. A primeira fórmula retira todos os hífens e retorna 0027 04. A segunda retira também os espaços comuns e retorna 002704.

=SUBSTITUIR(A6;"-";"")
=SUBSTITUIR(SUBSTITUIR(A6;"-";"");" ";"")

Ao aninhar as funções, o resultado da SUBSTITUIR interna passa para a externa. Assim você pode remover tipos diferentes de caracteres em sequência. Guarde os códigos como texto quando os zeros à esquerda tiverem significado. A fórmula não recupera zeros perdidos na digitação.

Guia da Microsoft para manter zeros à esquerda

O que conferir quando a substituição não acontece

Confira maiúsculas e minúsculas e o tipo de hífen e de espaço. Buscar um espaço comum deixa intactos os espaços incondicionais. Use ARRUMAR para remover espaços comuns nas extremidades e deixar apenas um entre as palavras.

Use MUDAR se a posição for fixa mesmo quando o texto varia. Para trocar dois caracteres a partir da posição 4 de cada código, indique a posição e o número de caracteres.