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
Sintaxe do SUBSTITUIR
=SUBSTITUIR(texto; texto_antigo; novo_texto; [núm_da_ocorrência])| Argumento | O que significa |
|---|---|
| texto | O texto ou a célula que você quer mudar. |
| texto_antigo | O texto que você procura. O SUBSTITUIR diferencia maiúsculas de minúsculas. |
| novo_texto | O 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
| Objetivo | Fórmula | Resultado |
|---|---|---|
| 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:
| A | B | |
|---|---|---|
| 1 | Como digitado | Limpo |
| 2 | 06-1234 5678 | 0612345678 |
| 3 | (020) 123 4567 | 0201234567 |
| 4 | 06 98-76-54-32 | 0698765432 |
- 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.
Continue aprendendo
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.