Substituindo texto com SUBSTITUTE

Os dados frequentemente precisam de operações de localizar e substituir dentro de fórmulas: remover hífens de números de telefone, trocar uma palavra ou remover símbolos soltos.

A função SUBSTITUTE encontra um trecho de texto dentro de uma célula e substitui cada ocorrência por um texto novo, tudo sem alterar a célula original.

Ela é o equivalente baseado em fórmula ao recurso Localizar e substituir, mas atualiza os resultados automaticamente à medida que os dados mudam.

SUBSTITUTE aceita até quatro argumentos:

• texto a cadeia de caracteres ou célula original
• texto antigo o texto a localizar
• texto novo o texto que ocupará o lugar dele
• número da ocorrência (opcional) qual ocorrência substituir

A estrutura é =SUBSTITUTE(text, old_text, new_text, [instance_num]). Se você omitir o último argumento, todas as ocorrências serão substituídas.

Um truque comum é substituir algo por nada para excluí-lo. Para isso, defina texto novo como uma cadeia vazia "".

Se A2 contiver 555-123-4567, a fórmula abaixo removerá todos os hífens, retornando 5551234567.

Esse padrão limpa números de telefone, identificadores e outros valores com pontuação.

SUBSTITUTE também funciona com palavras inteiras, não apenas com caracteres individuais.

Se A2 contiver Relatório anual 2023, você poderá atualizar o ano substituindo a palavra:

O resultado será Relatório anual 2024. Cada ocorrência correspondente na célula será alterada.

Importante: SUBSTITUTE faz a correspondência do texto de forma exata, incluindo maiúsculas e minúsculas.

Se A2 contiver Gato gato CAT, então =SUBSTITUTE(A2, "cat", "dog") alterará apenas o gato em minúsculas, produzindo Gato cão CAT.

Se você precisar substituir sem diferenciar maiúsculas de minúsculas, normalize primeiro o uso de maiúsculas com LOWER ou UPPER, ou use as funções de expressões regulares da próxima lição nas Planilhas.

O quarto argumento opcional, número da ocorrência, permite selecionar uma única ocorrência em vez de todas.

Se A2 contiver a-b-c-d e você quiser alterar apenas o segundo hífen, defina o número da ocorrência como 2.

O resultado será a-b/c-d: apenas o segundo hífen se tornou uma barra.

Imagine um valor importado armazenado como texto: $1,250.00. Para transformá-lo em um número utilizável, você precisa remover o símbolo de dólar e a vírgula.

Aninhe duas chamadas de SUBSTITUTE, uma dentro da outra, para remover os dois caracteres.

A chamada interna remove a vírgula; a chamada externa remove o símbolo de dólar, deixando 1250.00.

Você pode aninhar quantas chamadas de SUBSTITUTE forem necessárias, cada uma limpando um caractere diferente.

Para normalizar um código desorganizado que contenha espaços, pontos e barras, transformando-o em hífens uniformes, encadeie três substituições.

Cada camada fornece seu resultado à próxima, construindo a cadeia de caracteres final já limpa.

Não confunda SUBSTITUTE com a função semelhante REPLACE.

• SUBSTITUTE substitui texto por correspondência de conteúdo (localize esta palavra e troque-a).
• REPLACE substitui texto por posição (substitua 4 caracteres começando na posição 5).

Use SUBSTITUTE quando souber o que localizar e REPLACE quando souber onde o texto está.

Um bônus interessante: combine SUBSTITUTE com LEN para contar quantas vezes algo aparece.

Subtraia do comprimento original o comprimento obtido depois de remover um caractere. Se A2 contiver a,b,c,d, isso contará as vírgulas, retornando 3.

Esse truque é ótimo para contar itens em uma lista delimitada.

Suponha que A2 contenha números desorganizados com pontos e espaços, como 555.123 4567. Você deseja um formato consistente com hífens: 555-123-4567.

Primeiro remova os pontos e os espaços; depois, você poderia inserir os hífens novamente. Por enquanto, basta uniformizar os separadores usando hífens.

O aninhamento de substituições padroniza quaisquer separadores indesejados que apareçam.

Teste sua compreensão de SUBSTITUTE.

Você aprendeu a localizar e substituir texto em fórmulas:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Use "" como texto novo para excluir caracteres.
• A função diferencia maiúsculas de minúsculas; normalize o uso de maiúsculas primeiro, se necessário.
• O número da ocorrência opcional seleciona uma única ocorrência.
• Aninhe chamadas para limpar vários caracteres; combine-as com LEN para contar ocorrências.

Você concluiu a seção Como juntar e alterar texto. Excelente trabalho na criação de dados limpos e consistentes!