Excel

Extrair palavra contendo texto específico

Extract Word Containing Specific Text

Fórmula do Excel: extrair palavra contendo texto específicoFórmula genérica
= TRIM ( MID ( SUBSTITUTE (A1,' ', REPT (' ',99)), MAX (1, FIND ('@', SUBSTITUTE (A1,' ', REPT (' ',99)))-50),99))
Resumo

Para extrair uma palavra que contém um texto específico, você pode usar uma fórmula baseada em várias funções, incluindo TRIM, LEFT, SUBSTITUTE, MID, MAX e REPT. Você pode usar esta fórmula para extrair coisas como endereços de e-mail ou outras substrings com um id único.





No exemplo mostrado, a fórmula em C5 é:

 
= TRIM ( MID ( SUBSTITUTE (B5,' ', REPT (' ',99)), MAX (1, FIND ('@', SUBSTITUTE (B5,' ', REPT (' ',99)))-50),99))
Explicação

A essência: esta fórmula 'inunda' o espaço entre as palavras em uma string de texto com um grande número de espaços, encontra e extrai a substring de interesse e usa a função TRIM para limpar a bagunça.





Trabalhando de dentro para fora, o texto original em B5 é inundado com espaços usando SUBSTITUTE:

 
 SUBSTITUTE (B5,' ', REPT (' ',99))

Isso substitui cada espaço com 99 espaços.



Nota: 99 é apenas um número arbitrário que representa a palavra mais longa que você precisa extrair.

Em seguida, a função FIND localiza o caractere específico (neste caso, '@') dentro do texto inundado:

como inserir uma quebra de linha no excel
 
 FIND ('@', SUBSTITUTE (B5,' ', REPT (' ',99)))-50

FIND retorna a posição do '@' neste texto, da qual 50 é subtraído. Subtrair 50 efetivamente 'retrocede' a posição para algum lugar no meio dos espaços que precedem a substring de interesse. No exemplo mostrado, a posição calculada é 366.

A função MAX é usada para lidar com o problema da substring que aparece primeiro no texto. Nesse caso, a posição será negativa e MAX é usado para redefinir para 1.

Com uma posição inicial estabelecida, MID é usado para extrair 99 caracteres de texto, começando em 366 do texto em B5, novamente inundado com espaço:

 
 MID ( SUBSTITUTE (B5,' ', REPT (' ',99)),366,99)

Isso extrai a substring de interesse, com muitos caracteres de espaço antes e depois.

Por fim, a função TRIM é usada para cortar espaços à esquerda e à direita e retorna a substring que contém o caractere especial.

Autor Dave Bruns


^