Função SUBSTITUTE Excel - Localizar e substituir texto

Baixar exemplo de pasta de trabalho

Baixe a apostila de exemplo

Este tutorial demonstra como usar o Função SUBSTITUTE Excel no Excel para localizar e substituir texto.

Visão geral da função SUBSTITUTE

A função SUBSTITUTE Localiza e substitui o texto existente por uma nova string de texto. Maiúsculas e Minúsculas.

Para usar a função SUBSTITUIR planilha do Excel, selecione uma célula e digite:

(Observe como as entradas da fórmula aparecem)

Função SUBSTITUIR Sintaxe e entradas:

1	= SUBSTITUTE (texto, antigo_texto, novo_texto, instância_num)

texto - A string original do texto.

old_text - A string de texto que você deseja localizar e substituir.

novo_teste - O texto de substituição.

instance_num - OPCIONAL. O número da instância a ser substituído. Se estiver em branco, todas as instâncias serão substituídas.

O que é SUBSTITUTE?

A função SUBSTITUTE no Excel é usada para substituir uma sequência de caracteres em uma sequência de texto por uma sequência de texto especificada diferente.

Vejamos o exemplo a seguir substituindo a string “marrom rápido” por “branco lento”.

1	= SUBSTITUIR (C2, C3, C4)

SUBSTITUTO procura na string em C2, encontra “marrom rápido” e o substitui por “branco lento”.

Como usar SUBSTITUTE

A função SUBSTITUTE leva 3 argumentos obrigatórios e 1 opcional:

• Texto: o texto que você deseja substituir caracteres
• Old_text: o texto que você deseja substituir
• Novo_texto: o texto que você deseja substituir Old_text por
• Instance_num (opcional): Especifica a ocorrência de Old_text que você deseja substituir por New_text. Por padrão, todas as ocorrências de Old_text serão substituídas se uma instância não for especificada.

Aninhamento SUBSTITUTE

E se tivéssemos que substituir duas partes da mesma corda? Vamos ver como combinaríamos duas funções SUBSTITUTE.

1	= SUBSTITUIR (SUBSTITUIR (C2, C3, C4), C5, C6)

Observe como o resultado de SUBSTITUTE (C2, C3, C4) é usado como o primeiro argumento para o segundo SUBSTITUTE. Se quebrarmos a fórmula:

= SUBSTITUIR (C2, C3, C4) avalia como “O branco lento a raposa salta sobre o cão preguiçoso ”.

Em seguida, SUBSTITUIR (“A raposa branca lenta salta sobre o cão preguiçoso”, C5, C6) que avalia como “O branco lento leão salta sobre o cão preguiçoso ”.

Diferença entre SUBSTITUTE e REPLACE

Uma função semelhante a SUBSTITUTE é REPLACE. Na maioria dos casos, eles podem ser usados ​​alternadamente, no entanto, a seguinte regra prática se aplica.

A função SUBSTITUTE deve ser usada quando o seqüência de texto ser substituído é conhecido, ou uma palavra ou palavras inteiras estão sendo substituídas, como nosso exemplo fox usado anteriormente.

1	= SUBSTITUIR (C2, C3, C4)

A função REPLACE deve ser usada quando o posição dos caracteres do texto para ser substituído na string são conhecidos, ou quando uma parte de uma palavra ou string está sendo substituída. Por exemplo, remover um hífen de uma sequência de números.

A função REPLACE também pode ser aninhada como SUBSTITUTE, entretanto, é importante observar que a posição relativa do caractere inicial pode mudar com base no comprimento da string a ser substituída na primeira etapa da fórmula aninhada.

Por exemplo, substituir “marrom rápido” por “branco lento” é substituir 11 caracteres por 10 caracteres. Portanto, se aninhado, o caractere inicial para o segundo REPLACE estaria 1 caractere fora das posições iniciais do caractere.

Dicas de SUBSTITUIR

• SUBSTITUTE pode ser usado em substrings:
• Lembre-se de que você precisa especificar um instance_num se estiver tentando substituir um conjunto de caracteres que se repetem na string:

Observe que 3030 está incorreto, pois “20” e “20” em “2020” foram substituídos por “30”.

Interessado em mais funções de texto?

Veja nossos outros artigos para mais informações sobre REPLACE, ou como outras funções de texto no Excel como LEFT, MID e RIGHT são usadas.

SUBSTITUIR no Planilhas Google

A função SUBSTITUTE funciona exatamente da mesma forma no Planilhas Google e no Excel:

Notas Adicionais

A função SUBSTITUTE diferencia maiúsculas de minúsculas !. Você pode usar as funções LOWER ou UPPER para converter suas strings de texto em um caso consistente antes de usar a função SUBSTITUTE. Então, se desejar, você pode usar a função PROPER para colocar a primeira letra de cada palavra em maiúscula.

Como alternativa, você pode usar as funções SEARCH e REPLACE para simular um SUBSTITUTE sem distinção entre maiúsculas e minúsculas. Use a função SEARCH para encontrar a posição inicial da string de texto. A função SEARCH não faz distinção entre maiúsculas e minúsculas. Em seguida, use a função REPLACE para substituir o texto. Você precisará usar o resultado da função SEARCH como a entrada núm_inicial em REPLACE. Você também precisará definir o número de caracteres a serem substituídos (num_chars). Você pode contá-los manualmente ou usar a função LEN para contar o número de caracteres. Exemplo:

Substituir - Remover Hífens

Uma dúvida comum é como remover os hifens do texto. O truque é substituir o hífen por nada.

Para este exemplo, vamos supor que a célula A1 tenha texto com hifens. Veja como removê-los

1. Em uma célula auxiliar em branco, digite = SUBSTITUTE (
2. Clique na célula em que deseja substituir os caracteres ou A1 (isso preenche automaticamente a fórmula)
3. Digite, (vírgula)
4. Digite “-”
5. Digite, (vírgula)
6. Digite “” e pressione Enter
Seu resultado final em sua célula auxiliar deve ser semelhante a este:
= SUBSTITUTAR (A1, ”-“, ””)

3 mais exemplos substitutos:

Olhando para a célula B3, queremos substituir a palavra “doente” por “grande”. Podemos usar a seguinte expressão:

1	= SUBSTITUIR (B3, "doente", "grande", 1)

Se o texto que queremos substituir ocorrer mais de uma vez, precisamos especificar a instância que queremos dizer. No segundo exemplo, temos duas ocorrências da palavra “velho”. Portanto, se quisermos alterar a segunda instância para a palavra "cinza", temos que usar:

1	= SUBSTITUTAR (B4, "antigo", "cinza", 2)

Observe que, se a ocorrência não for especificada, todas as instâncias serão substituídas. Então:

1	= SUBSTITUIR (B4, "antigo", "cinza")

Eliminaria todas as ocorrências da palavra “antigo” e as substituiria pela palavra “cinza”. Observe que, se o texto antigo não puder ser encontrado, a string permanece inalterada. Então, olhando para o último exemplo:

1	= SUBSTITUTE (B5, "preto", "cinza", 1)

Significa que tentamos substituir a palavra “preto” pela palavra “cinza”. No entanto, como "preto" não ocorre, a corda original permaneceu levitra enviada nos Estados Unidos inalterada:

Para baixar o arquivo .XLSX deste artigo, clique aqui

Exemplos SUBSTITUTE em VBA

Você também pode usar a função SUBSTITUTE em VBA. Modelo:
application.worksheetfunction.substitute (text, old_text, new_text, instance_num)
Para os argumentos da função (texto, etc.), você pode inseri-los diretamente na função ou definir as variáveis ​​a serem usadas.

Quando o seguinte código é executado

1	Range ("A1") = WorksheetFunction.Substitute (Range ("A1"), "usando", "", 1)

O conteúdo da célula A1 se tornará:

Portanto, a palavra “usando” foi substituída por uma string vazia. O último parâmetro (1) indica qual instância do texto deve ser substituída, caso o texto esteja presente mais de uma vez. Então, por exemplo, se executarmos a seguinte instrução

1	Range ("A1") = WorksheetFunction.Substitute (Range ("A1"), "the", "an", 1)

A célula A1 ficará assim

Observe que a primeira ocorrência de “the” foi substituída por “an”, enquanto a segunda “the” não foi afetada. Se omitimos o último parâmetro, ambas as palavras “the” teriam sido substituídas

Voltar para a lista de todas as funções no Excel