SUBSTITUIR no Excel: sintaxe, exemplos e erros

Sintaxe

=SUBSTITUIR(texto, texto_antigo, novo_texto, [núm_da_ocorrência])

Dependendo das configurações regionais, os argumentos são separados por ponto e vírgula em vez de vírgulas: =SUBSTITUIR(A2; "-"; " "). É o padrão nos países onde a vírgula é o separador decimal.

Dois comportamentos surpreendem todo mundo uma vez: a correspondência diferencia maiúsculas (“ID” e “id” são alvos distintos), e sem núm_da_ocorrência a troca atinge todas as ocorrências — não existe interruptor de “só a primeira”.

Exemplos básicos

=SUBSTITUIR(A2, "-", " ")

Hífens viram espaços: telefones, códigos de peça e números de documento escritos com hífens ficam legíveis num golpe. Cada hífen muda — a fórmula repete a troca pela célula inteira.

=SUBSTITUIR(SUBSTITUIR(A2, "(", ""), ")", "")

Apagar caracteres: o novo_texto "" apaga cada ocorrência sem deixar rastro. Aninhando dois SUBSTITUIR, uma única fórmula arranca os dois parênteses de “(555) 0102” — uma camada por caractere que você quer fora.

=SUBSTITUIR(A2, "/", "-", 2)

O argumento núm_da_ocorrência fixa uma única ocorrência: só a segunda barra vira hífen, a primeira e a terceira ficam paradas. As ocorrências contam da esquerda para a direita a partir do começo do texto.

=NÚM.CARACT(A2)-NÚM.CARACT(SUBSTITUIR(A2, " ", ""))+1

O contador de palavras clássico: apagar todos os espaços e comparar os dois comprimentos dá o número de espaços, e +1 vira o número de palavras. Sem VBA, sem análise de dados — uma fórmula.

O resultado do SUBSTITUIR é texto — mesmo parecendo número. O “1234” nascido de um “1 234” limpo não soma, não faz média, não gera gráfico. Envolva: =VALOR(SUBSTITUIR(A2, " ", "")) devolve o texto limpo a número de verdade.

Erros comuns e como corrigi-los

Mais uma não-operação silenciosa: quando o núm_da_ocorrência passa do número real de ocorrências, o SUBSTITUIR devolve o texto original intacto — sem erro, sem aviso. Se uma fórmula “não faz nada”, conte as ocorrências antes de culpar a grafia.

• Localizar e substituir no ExcelRoda as mesmas trocas em planilhas e pastas de trabalho inteiras de uma vez — sem fórmulas, numa passada.
• Remover espaços no ExcelElimina os espaços invisíveis que fazem o texto_antigo não pegar numa célula de aparência idêntica.

SUBSTITUIR versus Localizar e Substituir

A divisão prática: Localizar e Substituir para uma limpeza pontual do que já está na planilha, SUBSTITUIR quando a limpeza precisa sobreviver a dados novos — importações, atualizações, colagens em massa. Muitas planilhas terminam com uma coluna SUBSTITUIR se alimentando de uma coluna de importação crua.

SUBSTITUIR no Planilhas Google

Mesmo nome, mesmos quatro argumentos, mesma correspondência literal que diferencia maiúsculas — as fórmulas acima colam sem mudança. O Planilhas Google embrulha a mesma ideia numa ferramenta poderosa: o REGEXREPLACE aceita expressões regulares, então uma fórmula só normaliza uma bagunça inteira de formatos de uma vez.

• Em configurações com vírgula decimal os argumentos vão com ponto e vírgula: =SUBSTITUIR(A2; "-"; " ").
• A caixa Localizar e substituir (Ctrl+H) do Planilhas Google traz a opção Pesquisar usando expressões regulares, que o Excel não tem.
• Para apagar caracteres, "" como novo_texto se comporta exatamente como no Excel — sem deixar rastro.

Limitações e dicas avançadas

• Um texto_antigo por chamada: limpar vários caracteres diferentes significa aninhar — cada camada arranca uma classe de caracteres a mais.
• A ordem importa quando os padrões se sobrepõem: troque “::” antes de “:”, senão os dois-pontos duplos já se foram quando a regra dos dois-pontos simples rodar.
• Sem curingas — o texto_antigo é casado literalmente, então “*” e “?” caçam asteriscos e interrogações de verdade. A força dos padrões mora em Localizar e Substituir ou, no Planilhas Google, no REGEXREPLACE.
• Quebras de linha são CARACT(10): =SUBSTITUIR(A2, CARACT(10), " ") achata uma célula com quebra numa linha só — converta o resultado em números de verdade depois, se precisar.
• Um fluxo de limpeza reutilizável: SUBSTITUIR o ruído, ARRUMAR o resíduo, VALOR os números — três colunas que sobrevivem a cada reimportação.

Trate o SUBSTITUIR como o sistema imunológico de uma planilha importada: os dados crus pousam sujos, as colunas de substituição os limpam na chegada, e toda fórmula adiante se alimenta da versão limpa — ninguém mais edita a coluna crua à mão.

• Remover caracteres especiais no ExcelDespinta o ruído de símbolos de colunas inteiras sem montar fórmulas SUBSTITUIR aninhadas.
• Remover quebras de linha no ExcelLimpa as quebras CARACT(10) escondidas que um SUBSTITUIR por célula não alcança em escala.