Aprender Excel
PT
Define como números, datas e fórmulas aparecem, para que tudo funcione como no seu próprio Excel.

Início › Funções do Excel › SUBSTITUIR

Função SUBSTITUIR no Excel: trocar texto com uma fórmula

O SUBSTITUIR troca um texto específico de uma célula por outro texto. Remova hifens de números de telefone, troque pontos por vírgulas ou corrija um erro de digitação que se repete, tudo sem mexer nos dados originais. No Excel em inglês, a função se chama SUBSTITUTE.

Neste guia
  1. Sintaxe do SUBSTITUIR
  2. Exemplos
  3. Experimente você mesmo
  4. Várias trocas
  5. Contar ocorrências
  6. Texto em números
  7. SUBSTITUIR ou MUDAR
  8. Na prática: limpar números de telefone
  9. Continue aprendendo
  10. Perguntas frequentes

Sintaxe do SUBSTITUIR

=SUBSTITUIR(texto; texto_antigo; novo_texto; [núm_da_ocorrência])
ArgumentoO que significa
textoO texto ou a célula que você quer mudar.
texto_antigoO texto que você procura. O SUBSTITUIR diferencia maiúsculas de minúsculas.
novo_textoO texto de substituição. Use "" para remover o texto antigo.
núm_da_ocorrência
(opcional)
Qual ocorrência trocar. Se você omitir, todas são trocadas.

Exemplos

ObjetivoFórmulaResultado
Remover hifens=SUBSTITUIR("11-98765-4321"; "-"; "")11987654321
Trocar uma palavra=SUBSTITUIR("Relatório T1"; "T1"; "T2")Relatório T2
Só a 2ª ocorrência=SUBSTITUIR("a-b-c"; "-"; "/"; 2)a-b/c
Remover todos os espaços=SUBSTITUIR(A2; " "; "")Sem nenhum espaço

Pratique SUBSTITUIR: experimente você mesmo

Ler é uma coisa, digitar é outra. Monte a fórmula abaixo passo a passo: as dicas dizem o que digitar, e você pode clicar nas células ou arrastar sobre elas para adicioná-las, igual ao Excel.

Tarefa: Troque os hifens do telefone por espaços, em B2: =SUBSTITUIR(A2;"-";" ")

Várias trocas de uma vez

Aninhe o SUBSTITUIR dentro dele mesmo, um nível por troca. Para remover parênteses e hifens de um número de telefone:

=SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(A2; "("; ""); ")"; ""); "-"; "")

"(11) 98765-4321" vira "11 987654321".

Contar quantas vezes um caractere aparece

Compare o tamanho antes e depois de remover o caractere:

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

Para "maçã,banana,cereja" retorna 2 vírgulas, então há 3 itens. Mais sobre o NÚM.CARACT no guia do NÚM.CARACT.

Corrigir números com o separador errado

Números importados como "1,234.50" (estilo americano) são texto em um Excel configurado com vírgula decimal. Troque os separadores e converta:

=VALOR(SUBSTITUIR(SUBSTITUIR(A2; ","; ""); "."; ","))

retorna o número 1234,5. Leia também converter texto em número.

SUBSTITUIR versus MUDAR e Localizar e Substituir

  • SUBSTITUIR troca um texto pelo que ele é: todos os "-" da célula.
  • MUDAR troca um texto pela posição: =MUDAR(A2; 1; 3; "") remove os três primeiros caracteres.
  • Localizar e Substituir (Ctrl+U no Excel em português) muda as próprias células, sem fórmula. É mais rápido para uma limpeza pontual.

Na prática: limpar números de telefone

Telefones digitados à mão vêm com espaços, hífens e parênteses. Antes de importar para um CRM, você quer só os dígitos. Um SUBSTITUIR por caractere, aninhados:

AB
1Como digitadoLimpo
206-1234 56780612345678
3(020) 123 45670201234567
406 98-76-54-320698765432
  • B2: =SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(A2; " "; ""); "-"; ""); "("; ""); ")"; "")

Leia de dentro para fora: o SUBSTITUIR mais interno tira os espaços, o seguinte os hífens e os dois externos os parênteses. O resultado continua sendo texto, e é isso que você quer: o 0 inicial fica.

Perguntas frequentes

O SUBSTITUIR diferencia maiúsculas de minúsculas?

Sim. SUBSTITUIR(A2; "abc"; "x") não troca "ABC". Envolva antes o texto em MINÚSCULA se as maiúsculas não devem importar.

O SUBSTITUIR aceita curingas?

Não. Ele só troca texto exato. Use Localizar e Substituir para trocas com curingas.

Como troco as quebras de linha?

Quebras de linha são CARACT(10): =SUBSTITUIR(A2; CARACT(10); ", ") transforma as quebras em vírgulas.