Como extrair substring no Excel

Uma substring é apenas uma parte de uma string (ou uma entrada de texto) em uma célula. Por exemplo,’a Fúria’é uma substring de’Nosso é a Fúria’.

Às vezes, você pode querer extrair uma substring de uma string nos dados do Excel. Por exemplo, você gostaria de extrair o nome da cidade (Los Angeles) do endereço completo (6854 Santee St, Los Angeles, CA 90015, Estados Unidos). Infelizmente, não existe uma função de substring específica no Excel que possa fazer isso facilmente. No entanto, isso ainda pode ser feito usando algumas das funções integradas que são categorizadas na função TEXTO.

Neste artigo, explicaremos como usar as funções de texto no Excel para extrair uma substring de uma célula.

Funções TEXT do Excel para extrair substrings

Existem três funções principais usadas para extrair uma substring de texto de uma célula em sua planilha.

• Função ESQUERDA: Extrai uma substring da esquerda da string de texto.
• Função RIGHT: Extrai uma substring da direita da string de texto.
• Função MID: extrai uma substring do meio de uma string de texto, começando na posição especificada no texto.

Existem também outras funções, como FIND, SEARCH, LEN, que são usadas para encontrar a posição ou o comprimento do texto especificado.

Função ESQUERDA do Excel

A função LEFT extrai um número específico de caracteres do lado esquerdo da string de texto, começando com o caractere mais à esquerda (o primeiro caractere).

A sintaxe da função LEFT:

 =LEFT (texto, [num_chars])  

Argumentos da função:

• texto : especifica a string de texto que contém os caracteres de onde você deseja extrair.
• num_chars : especifica o número de caracteres (isso inclui o espaço entre os caracteres) do lado esquerdo da string de texto que você deseja extrair.

Exemplo para a função LEFT:

Suponhamos que temos um endereço na célula A1 da nossa planilha. E queremos apenas o número da porta e o nome da rua do endereço, então usaríamos a seguinte função ESQUERDA:

 =LEFT (A1,14)  

A função na captura de tela acima diz ao Excel para olhar para o valor do texto na célula ‘A1 ′ e, em seguida, extrair os caracteres’ 14 ’mais à esquerda dessa string de texto.

Como extrair um substring antes de caracteres específicos

Se sua string tem um padrão, você pode usar as funções LEN e FIND do Excel para retornar o número especificado de caracteres sem a necessidade de contar o número de caracteres para inserir o segundo argumento.

Use a seguinte fórmula para extrair um texto antes de um caractere específico:

 =LEFT (text, FIND ("char", text)-1)  

• texto : Uma string da qual desejamos extrair uma substring, pode ser uma string de texto ou uma referência a uma célula que contém uma string.
• char : Um caractere específico para o qual queremos determinar a posição.

A função FIND encontra a posição do caractere especificado e subtrai 1 (o próprio caractere) do resultado e, em seguida, puxa todos os caracteres restantes dele usando a função LEFT.

Por exemplo, temos uma lista de endereços de e-mail. Queremos extrair apenas o nome de usuário do endereço de e-mail como substring antes do”arroba”(@) da célula A2, para isso estamos usando esta fórmula:

 =LEFT (A2, FIND ("@", A2)-1)  

A fórmula acima usa a função FIND para retornar a posição do caractere ‘@’, que é 9, e 1 posição é subtraída para extrair 8 caracteres da esquerda da string pela função LEFT.

Tentaremos outro exemplo, mas desta vez com uma abordagem um pouco diferente.

Para obter o texto antes de um caractere ou caracteres específicos, desta vez usamos as funções SEARCH ou FIND e LEN. Obtenha a posição do caractere com a função SEARCH (você pode usar SEARCH ou FIND para retornar a posição do caractere), subtraia esse número do comprimento total da string retornado pela função LEN e, finalmente, extraia os caracteres restantes do string pela função LEFT.

 =LEFT (text, LEN (text)-SEARCH ("char", text))  

Em nosso exemplo, usaríamos a seguinte fórmula para extrair uma substring antes da palavra ‘The’:

 =LEFT (A2, LEN (A2)-SEARCH ("The", A2))  

A fórmula acima retorna a posição dos caracteres’The'(19) com a função SEARCH, subtrai esse número do comprimento total da string retornado pela função LEN (37) e, finalmente, extrai a quantidade restante de caracteres (18) da string pela função LEFT.

Observação: fornecemos espaço e a palavra “The”) como o argumento para a função SEARCH na fórmula acima.

Se usarmos vários caracteres no primeiro argumento de SEARCH ou FIND, ele retornará apenas a posição do primeiro caractere.

Função RIGHT do Excel

Para extrair uma substring da extremidade direita de uma string de texto, use a função RIGHT do Excel:

 =RIGHT (texto, [num_chars])  

Exemplo para a função CERTA:

Em nossa lista de nomes de exemplo, desejamos extrair o sobrenome como substring do lado direito do nome completo usando a seguinte fórmula:

 =RIGHT (A2,7)  

A função acima informa ao Excel para olhar para o valor do texto na célula ‘A2’ e, em seguida, extrair os caracteres ‘7’ mais à direita dessa string de texto.

Como extrair uma substring após um caractere (s) específico (s)

Para obter texto após um caractere específico, você pode usar as funções LEN e FIND do Excel juntas no argumento num_char para retornar o número especificado de caracteres.

A sintaxe:

 =RIGHT (text, LEN (text)-SEARCH ("char", text))  

Suponha que temos uma lista de e-mail, queremos extrair o nome de domínio como substring do lado direito do endereço de e-mail usando a seguinte fórmula:

 =RIGHT (A2, LEN (A2)-FIND ("@", A2))
 

Na fórmula acima, a função FIND retorna a posição do caractere’@’, que é 9 e subtrai esse número do comprimento total da string retornada pela função LEN para extrair essa quantidade de caracteres à direita da string por a função DIREITA.

Função Excel MID

Para extrair uma substring, começando no meio de uma string, na posição que você especificar, use a função MID no Excel.

 =MID (text, start_num, num_chars)  

Em comparação com as outras duas funções de texto, a função MID tem um conjunto de argumentos ligeiramente diferente. Em vez de apenas especificar a string de texto original (texto) e o número de caracteres a serem extraídos (num_chars), você também precisa indicar a posição inicial (núm_inicial) na string de texto original.

Exemplo para função MID:

Em nosso exemplo, queremos extrair apenas os nomes do meio de uma lista de nomes completos, então usaríamos a fórmula abaixo:

 =MID (A2,9,4)  

A função acima conta os caracteres na célula A1 da esquerda até chegar ao 9º caractere e, em seguida, retorna os próximos 4 caracteres (incluindo o 9º caractere).

Como extrair uma substring entre dois caracteres

Para extrair uma substring entre dois caracteres, use a seguinte sintaxe:

 =MID (text, FIND ("char", text) + 1, FIND ("char", text)-FIND ("char", text)-1 )  

• text : uma string da qual desejamos extrair uma substring, pode ser uma string de texto ou uma referência para uma célula contendo uma string.
• char : Um caractere específico para o qual queremos determinar a posição.

Por exemplo, digamos que temos um endereço de e-mail e queremos extrair o ‘nome do domínio’ sem a parte do nome de usuário e.com, então usaríamos a fórmula abaixo:

 =MID (A2, FIND ("@", A2) + 1, FIND (".", A2)-FIND ("@", A2)-1 )  

É um pouco confuso, então daremos alguns detalhes sobre a fórmula:

A string de [email protected]

• A2 Especifica a célula que contém a string de texto da qual desejamos extrair a substring.
• FIND (“@”, A2) + 1 Especifica a posição inicial da substring (9), que está logo após o’@’. Portanto, fornecemos’@’como seu caractere.
• FIND (“.”, A2)-FIND (“@”, A2)-1 determina o comprimento da substring entre o’@’e o’.’subtraindo a posição de’.'(21) da posição de’@'(8) e subtraindo 1 do resultado.

Primeiro, FIND (“@”, A2) +1 especifica a posição inicial da substring (9), FIND (“.”, A2)-FIND (“@” , A2)-1 identifica o número de caracteres (12) entre o’@’e o’.’, E então a função MID extrai esse número de caracteres (substring) do texto.

Aqui está uma fórmula para outro exemplo,

 =MID (A2, FIND ("", A2) + 1, FIND ("The", A2)-FIND ("", A2)-1)  

FIND ("", A2) +1 -Um caractere de espaço “ ” é usado como o primeiro argumento.

FIND ("The", A2)-FIND ("", A2)-1 -um caractere de espaço e a palavra’The’são usados ​​juntos como o primeiro argumento na primeira função FIND ; e o espaço é usado como o primeiro argumento na segunda função FIND.

Isso é tudo sobre a função TEXTO no Excel. Esperamos que você tenha aprendido a usar a função TEXTO.