Tutorial do Excel – extraia texto ou número por posição específica

Em muitos casos, você só precisa extrair o conteúdo útil de uma frase ou uma sequência de texto em uma célula, como extrair a província do endereço, extrair o endereço de e-mail de uma frase, extrair o número da conta do correio de uma conversa, e assim por diante. Este tutorial restringe a extração a uma posição específica em uma célula e coleta diferentes métodos para ajudar a extrair texto ou números de uma célula por posição específica no Excel.

Este tutorial cobre principalmente: (Clique em qualquer título no índice abaixo ou à direita para navegar até o conteúdo correspondente.)

Extrair texto por posição

1. Extraia o número de caracteres da esquerda ou da direita

1.1 Extraia o primeiro ou o último N caracteres com fórmulas

1.2 Extraia os primeiros ou últimos N caracteres com uma ferramenta incrível

2. Extraia o texto antes ou depois de um determinado caractere/palavra

2.1 Extrair texto antes ou depois do primeiro delimitador (um caractere)

2.1.1 Extrair texto antes do primeiro delimitador com fórmulas

2.1.2 Extrair texto após o primeiro delimitador com fórmula

2.1.3 Extraia texto antes ou depois do primeiro delimitador com uma ferramenta incrível

2.2 Extrair texto antes ou depois do último delimitador (um caractere)

2.2.1 Extrair texto antes do último delimitador com fórmula

2.2.2 Extrair texto após o último delimitador com fórmula

2.3 Extrair texto após o enésimo caractere

2.4 Extraia a enésima palavra de uma string de texto

2.4.1 Extraia a enésima palavra com fórmula

2.4.2 Extraia a enésima palavra com função definida pelo usuário

2.4.3 Extraia a enésima palavra com uma ferramenta incrível

2.5 Extrair texto antes ou depois da enésima ocorrência de um delimitador

2.5.1 Extrair texto antes da enésima ocorrência de um delimitador

2.5.2 Extrair texto após a enésima ocorrência de um delimitador

2.6 Extrair texto antes ou depois da quebra de linha

2.6.1 Extrair texto antes da primeira quebra de linha com fórmula

2.6.2 Extrair texto após a última quebra de linha com fórmula

2.7 Extrair texto antes ou depois de uma palavra

2.7.1 Extrair texto antes de uma determinada palavra com fórmula

2.7.2 Extrair texto após uma determinada palavra com fórmula

2.7.3 Extraia texto antes ou depois de uma determinada palavra com uma ferramenta incrível

3. Extraia entre caracteres/palavras

3.1 Extrair texto entre dois caracteres

3.1.1 Extrair texto entre dois mesmos caracteres com fórmula

3.1.2 Extrair texto entre dois caracteres diferentes com fórmula

3.1.3 Extraia texto entre dois caracteres com uma ferramenta incrível

3.1.4 Extrair texto entre dois caracteres (incluindo os caracteres) por regra

3.2 Extrair texto entre duas palavras

3.2.1 Extrair texto entre duas palavras com fórmula

3.2.2 Extraia texto entre duas palavras com uma ferramenta incrível

Extrair números por posição

1. Extraia o número da esquerda de uma string

2. Extraia o número à direita de uma string

3. Extraia todos os números de qualquer posição em uma string de texto

3.1 Extraia todos os números de qualquer lugar em uma string com fórmula

3.2 Extraia todos os números de qualquer lugar em uma string com VBA

3.3 Extraia todos os números de qualquer lugar em uma string com uma ferramenta incrível

4. Extraia o número após o texto específico

4.1 Extrair número após um texto específico com fórmula

4.2 Extrair número após um texto específico com função definida pelo usuário

Extrair texto por posição

Esta seção reúne posições comuns onde um texto pode ser extraído de uma célula e fornece métodos correspondentes para tratá-los passo a passo. Você pode navegar para obter mais detalhes.

1. Extraia o número de caracteres da esquerda ou da direita

Para extrair o número de caracteres do lado esquerdo ou direito de uma string, você pode tentar um dos métodos abaixo.

1.1 Extraia o primeiro ou o último N caracteres com fórmulas

Supondo que você tenha uma lista de strings de texto na coluna B, conforme mostrado na captura de tela abaixo, para extrair os 2 primeiros caracteres e os 2 últimos caracteres de cada string, você pode aplicar as seguintes fórmulas.

Extraia os primeiros N caracteres de uma string de texto

A função LEFT pode ajudar a extrair facilmente os primeiros N caracteres de uma string de texto no Excel.

Fórmula genérica

=LEFT(text_string,[num_chars])

Argumentos

Text_string: a string de texto que contém os caracteres que você deseja extrair. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Num_chars: especifica o número de caracteres que você deseja extrair.

O Num_chars deve ser maior ou igual a zero;

Se o Num_chars especificado for maior que o comprimento da string de texto, ele retornará o texto inteiro;

Se o Num_chars for omitido, assume-se que é 1.

Agora você pode aplicar esta fórmula para extrair os 2 primeiros caracteres das células na coluna B.

1. Selecione uma célula em branco, copie ou insira a fórmula abaixo e pressione o botão Entrar chave para obter o primeiro resultado. Selecione a célula de resultado e arraste sua alça de preenchimento automático para baixo para aplicar a fórmula a outras células.

=LEFT(B5,2)

Agora os 2 primeiros caracteres em cada célula do intervalo B5:B10 foram extraídos.

Extraia os últimos N caracteres de uma string de texto

Aqui aplicamos a função DIREITA para extrair os últimos N caracteres de uma string de texto no Excel.

Fórmula genérica

=RIGHT(text_string,[num_chars])

Argumentos

Text_string: a string de texto que contém os caracteres que você deseja extrair. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Num_chars: especifica o número de caracteres que você deseja extrair.

O Num_chars deve ser maior ou igual a zero;

Se o especificado Num_chars é maior que o comprimento da string de texto, retorna todo o texto;

Se o Num_chars for omitido, assume-se que é 1.

Selecione uma célula em branco, copie ou insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Em seguida, selecione esta célula de resultado e arraste sua alça de preenchimento automático para baixo para obter outros resultados.

=RIGHT(B5,2)

1.2 Extraia os primeiros ou últimos N caracteres com uma ferramenta incrível

Embora as fórmulas acima sejam simples, para extrair o primeiro ou o último n caracteres de uma longa lista de strings de texto, você ainda precisa arrastar o AutoFill Handle de cima para baixo, o que pode demorar um pouco. Aqui recomenda Kutools for Excel'S Extrair Texto utilitário para ajudar a extrair o primeiro ou o último N caracteres de uma lista de strings de texto em massa.

1. Selecione a lista de sequências de texto da qual deseja extrair o texto com antecedência e clique em Kutools > Texto > Extrair Texto.

2. No popping up Extrair Texto caixa de diálogo, você precisa configurar da seguinte maneira.

2.1) Certifique-se de que você está hospedado no Extrair por localização aba;

2.2) No Variação caixa, o intervalo selecionado é exibido dentro, você pode mudar para outro intervalo de acordo com suas necessidades;

2.3) No Opções seção:

Se você deseja extrair os primeiros N caracteres, escolha o O primeiro caractere N botão de opção e, em seguida, especifique o número de caracteres que você extrairá na caixa de texto. Neste caso, insiro o número 2;

Se você deseja extrair os últimos N caracteres, selecione o O último caractere N botão de opção e, em seguida, especifique o número de caracteres que você extrairá na caixa de texto. Nesse caso, quero extrair os últimos 2 caracteres da string de texto, então insiro o número 2 na caixa de texto.

2.4) Clique OK. Veja a imagem:

Importante: Para tornar o resultado dinâmico quando a string de texto muda, você pode verificar o Inserir como uma fórmula caixa.

3. No próximo pop-up Extrair Texto caixa de diálogo, escolha uma célula para gerar os caracteres extraídos e clique em OK.

Em seguida, o primeiro ou o último N caracteres especificados são extraídos das células selecionadas em massa.

Clique para saber mais sobre este recurso.

Se você quiser ter um teste gratuito (30 dias) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.

2. Extraia o texto antes ou depois de um determinado caractere/palavra

Para extrair texto antes ou depois de um determinado caractere ou palavra, os diferentes cenários desta seção atenderão às suas necessidades.

2.1 Extrair texto antes ou depois do primeiro delimitador (um caractere)

Conforme mostrado na captura de tela abaixo, para extrair texto antes ou depois do primeiro delimitador de cada célula no intervalo B4:B10, você pode aplicar um dos métodos abaixo.

2.1.1 Extrair texto antes do primeiro delimitador com fórmula

Aplicar uma fórmula baseada nas funções LEFT e FIND pode ajudar a extrair o texto antes do primeiro delimitador de uma célula. Você pode seguir os passos abaixo para fazê-lo.

Fórmula genérica

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

Argumentos

Text_string: A string de texto da qual você deseja extrair a substring. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Delimitador: O primeiro delimitador que define qual texto será extraído de uma célula.

Selecione uma célula em branco, copie ou insira a fórmula abaixo nela e pressione o botão Entrar chave para obter o primeiro resultado. Selecione a primeira célula de resultado e arraste sua alça de preenchimento automático para baixo para obter os textos antes do primeiro delimitador de outras células.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Extrair texto após o primeiro delimitador com fórmula

A fórmula abaixo ajuda a extrair o texto após o primeiro delimitador de uma célula no Excel.

Fórmula genérica

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

Argumentos

Text_string: A string de texto da qual você deseja extrair a substring. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Delimitador: O primeiro delimitador que define qual texto será extraído de uma célula.

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 Extraia texto antes ou depois do primeiro delimitador com uma ferramenta incrível

Aqui altamente recomenda o Extrair Texto utilidade de Kutools para Excel. Com esse recurso, você pode extrair facilmente textos antes ou depois do primeiro delimitador de um intervalo de células em massa.

1. Selecione o intervalo de células onde deseja extrair o texto e clique em Kutools > Texto > Extrair Texto.

2. No Extrair Texto caixa de diálogo, você precisa configurar da seguinte maneira.

2.1) Fique no Extrair por localização aba;

2.2) No Variação caixa, o intervalo selecionado é exibido dentro, você pode alterá-lo conforme necessário;

2.3) No Opções seção:

Para extrair texto antes do primeiro delimitador, selecione o antes do texto botão de opção e digite o primeiro delimitador na caixa de texto;

Para extrair texto após o primeiro delimitador, selecione o depois do texto botão de opção e, em seguida, insira o primeiro delimitador na caixa de texto.

2.4) Clique OK.

Observação: Para tornar o resultado dinâmico quando a sequência de texto for alterada, você pode marcar a caixa Inserir como uma fórmula.

3. Depois outro Extrair Texto caixa de diálogo aparece, escolha uma célula para gerar os resultados e clique em OK.

Em seguida, os textos antes ou depois do primeiro delimitador são extraídos das células selecionadas de uma só vez.

Para saber mais sobre esse recurso, acesse: Extraia rapidamente determinado texto de células no Excel.

Se você quiser ter um teste gratuito (30 dias) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.

2.2 Extrair texto antes ou depois do último delimitador (um caractere)

Nas etapas acima, aprendemos os métodos sobre como extrair texto antes ou depois do primeiro delimitador de uma célula. Conforme mostrado na captura de tela abaixo, esta seção mostrará duas fórmulas para extrair texto antes ou depois do último delimitador de uma célula. Você pode seguir os passos abaixo para fazê-lo.

2.2.1 Extrair texto antes do último delimitador com fórmula

Para extrair texto antes do último delimitador de uma célula, você pode usar as funções SEARCH, LEN e SUBSTITUTE dentro da função LEFT.

Fórmula genérica

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

Argumentos

Text_string: A string de texto da qual você deseja extrair a substring. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Delimitador: O último delimitador que define qual texto será extraído de uma célula.

Selecione uma célula, insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Selecione esta célula de resultado e arraste seu AutoFill Handle para baixo para extrair os textos de outras strings de texto na mesma coluna.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Extrair texto após o último delimitador com fórmula

Após extrair o texto antes do último delimitador de uma célula, você pode aplicar a fórmula abaixo para extrair o texto após o último delimitador conforme necessário.

Fórmula genérica

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

Argumentos

Text_string: A string de texto da qual você deseja extrair a substring. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Delimitador: O último delimitador que define qual texto será extraído de uma célula.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 Extrair texto após o enésimo caractere

Veja o exemplo abaixo, há uma lista de strings de texto no intervalo B4:B10, para extrair o texto após o terceiro caractere de cada célula, você pode aplicar uma fórmula baseada na função MID e na função LEN.

Fórmula genérica

=MID(text_string,nth_char+1,LEN(text_string))

Argumentos

Text_string: A string de texto da qual você deseja extrair a substring. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

nth_char: Um número representa o enésimo caractere e você extrairá o texto depois dele.

Selecione uma célula em branco, copie ou insira a fórmula abaixo nela e pressione o botão Entrar chave para obter o resultado. Selecione esta célula de resultado e arraste sua alça de preenchimento automático para baixo para obter outros resultados.

=MID(B5,3+1,LEN(B5))

2.4 Extraia a enésima palavra de uma string de texto

Supondo que você tenha uma lista de strings de texto conforme mostrado na captura de tela abaixo e queira extrair apenas a enésima palavra da string de texto, esta seção fornece três métodos para você fazer isso.

2.4.1 Extraia a enésima palavra com fórmula

Você pode combinar as funções TRIM, MID, SUBSTITUTE, REPT e LEN para extrair a enésima palavra de uma string de texto em uma célula.

Fórmula genérica

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

Argumentos

Text_string: a cadeia de texto da qual você deseja extrair a enésima palavra. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

N: Um número representa a enésima palavra que você extrairá da string de texto.

Neste caso, o intervalo B5:B10 contém as strings de texto, D5:D10 contém os números que representam a enésima palavra, vamos aplicar esta fórmula para extrair a enésima palavra da sequência de texto.

Selecione uma célula em branco, copie ou insira a fórmula abaixo nela e pressione o botão Entrar chave para obter o primeiro resultado. Selecione esta célula de resultado e arraste sua alça de preenchimento automático para baixo para obter a enésima palavra de outras células.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Observação: Você pode digitar diretamente o enésimo número na fórmula da seguinte maneira.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Extraia a enésima palavra com função definida pelo usuário

Além da fórmula acima, você também pode aplicar uma função definida pelo usuário para extrair a enésima palavra de uma célula no Excel.

1. aperte o outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. No Microsoft Visual Basic para Aplicações janela, clique em inserção > Módulo, e copie o VBA abaixo na janela Code.

Código VBA: Extraia a enésima palavra de uma string de texto em uma célula

Função ExtractTheNthWord(Source As String, Position As Integer) 'Atualizar por Extendoffice 20211202 Dim arr() As String arr = VBA.Split(Source, " ") xCount = UBound(arr) If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then FindWord = "" Else FindWord = arr( Posição - 1) End If End Function

3. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

4. Volte para a planilha que contém as sequências de texto das quais você deseja extrair a enésima palavra. Selecione uma célula em branco, copie ou insira a fórmula abaixo nela e pressione o botão Entrar chave para obter a enésima palavra.

=FindWord(B5,D5)

=FindWord(B5,2)

Observação: na fórmula, D5 é a célula que contém um número que representa a enésima palavra. Como alternativa, você pode substituir diretamente a referência da célula por um número.

5. Selecione a célula de resultado e arraste sua alça de preenchimento automático para baixo para extrair a enésima palavra das cadeias de texto de outras células.

2.4.3 Extraia a enésima palavra com uma ferramenta incrível

Se você não deseja aplicar manualmente uma fórmula ou uma função definida pelo usuário fornecida acima, aqui recomenda Kutools for Excel Extraia a enésima palavra na célula Utilitário. Com esse recurso, você pode extrair facilmente a enésima palavra de uma sequência de texto em uma célula com apenas alguns cliques.

1. Selecione uma célula para colocar o resultado e clique em Kutools > Fórmula Helper > Texto > Extrair a enésima palavra na célula. Veja a imagem:

2. No Auxiliar de Fórmulas caixa de diálogo, você precisa configurar da seguinte maneira.

2.1) No Escolha uma lista de fórmulas caixa, o Extraia a enésima palavra na célula a opção é destacada;

2.2) No Célula caixa, escolha uma célula que contém a string de texto da qual você deseja extrair a enésima palavra;

2.3) No O enésimo caixa, selecione uma célula contendo o enésimo número ou insira diretamente um número de acordo com suas necessidades;

2.4) Clique OK.

3. Em seguida, a enésima (segunda) palavra é extraída da sequência de texto na célula B5 e você pode ver que uma fórmula é criada ao mesmo tempo. Selecione esta célula de resultado e arraste seu AutoFill Handle para baixo para obter a enésima palavra de outras strings de texto.

Clique para saber mais sobre este recurso.

Se você quiser ter um teste gratuito (30 dias) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.

2.5 Extrair texto antes ou depois da enésima ocorrência de um delimitador

Supondo que você tenha uma lista de strings de texto, conforme mostrado na captura de tela abaixo. Para extrair o texto antes ou depois da segunda ocorrência de um espaço, esta seção fornece duas fórmulas para ajudá-lo a fazê-lo.

2.5.1 Extrair texto antes da enésima ocorrência de um delimitador

Você pode usar a função LEFT junto com as funções SUBSTITUTE e FIND para extrair texto antes da enésima ocorrência de um delimitador de uma célula no Excel.

Fórmula genérica

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

Argumentos

Text_string: A string de texto da qual você deseja extrair o texto. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

N: Um número representa a enésima ocorrência de um delimitador do qual você extrairá o texto antes dele.

Selecione uma célula, copie ou insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Selecione esta célula de resultado e arraste-a para baixo para obter outros resultados na lista.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Observação: Na fórmula, B5 é a célula que contém a sequência de texto da qual você deseja extrair o texto; “ ” aqui representa um espaço e o número 2 representa a segunda ocorrência de um espaço. Você pode alterá-los de acordo com suas necessidades.

2.5.2 Extrair texto após a enésima ocorrência de um delimitador

Para extrair texto após a enésima ocorrência de um delimitador, você pode aplicar a função RIGHT com as funções SUBSTITUTE, LEN e FIND.

Fórmula genérica

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

Argumentos

Text_string: A string de texto da qual você deseja extrair o texto. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

N: Um número representa a enésima ocorrência de um delimitador do qual você extrairá o texto depois dele.

Agora você pode aplicar esta fórmula para extrair o texto após a segunda ocorrência do espaço de cada célula no intervalo B5:B10 da seguinte forma.

Selecione uma célula, insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Selecione esta célula de resultado e arraste-a para baixo para obter outros resultados.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 Extrair texto antes ou depois da quebra de linha

Suponha que você tenha uma lista de pedidos na coluna B e queira extrair apenas a parte da data e a parte do nº do produto de cada célula. Você pode usar as fórmulas do Excel abaixo para fazer isso.

2.6.1 Extrair texto antes da primeira quebra de linha com fórmula

Como você pode ver na captura de tela acima, a parte da data é localizada antes da primeira quebra de linha na célula. Esta seção demonstrará uma função LEFT junto com a função SEARCH para ajudá-lo a extrair o texto antes da primeira quebra de linha em uma célula.

Fórmula genérica

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

Argumentos

Célula: A célula da qual você deseja extrair o texto antes da primeira quebra de linha.

Selecione uma célula em branco, copie ou insira a fórmula abaixo nela e pressione o botão Entrar chave para obter o resultado. Selecione essa célula de resultado e arraste sua alça de preenchimento automático para baixo para aplicar essa fórmula a outras células.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Então você pode ver o texto antes que a primeira quebra de linha em cada célula no intervalo B5:B8 seja extraída conforme mostrado na captura de tela abaixo.

Observação: Na fórmula, CHAR(10) representa uma quebra de linha no Windows.

2.6.2 Extrair texto após a última quebra de linha com fórmula

Na etapa anterior, falamos sobre como extrair texto antes da primeira quebra de linha em uma célula. E esta parte irá guiá-lo como extrair texto após a última quebra de linha dentro de uma célula com uma fórmula diferente.

Fórmula genérica

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

Argumentos

Célula: A célula da qual você deseja extrair o texto antes da primeira quebra de linha.

Selecione uma célula em branco, insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Selecione essa célula de resultado e arraste sua alça de preenchimento automático para baixo para aplicar a fórmula a outras células.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Em seguida, o produto n. parte de cada célula na lista é extraída conforme mostrado na captura de tela acima.

Observação: Na fórmula, CHAR(10) representa uma quebra de linha no Windows.

2.7 Extrair texto antes ou depois de uma palavra

Nas partes anteriores, aprendemos como extrair texto antes ou depois de um caractere ou delimitador. O que você deve fazer para extrair texto antes ou depois de uma palavra inteira? Esta seção apresentará três métodos para ajudá-lo a realizar essa tarefa.

2.7.1 Extrair texto antes de uma determinada palavra com fórmula

A fórmula a seguir ajuda a extrair texto antes de uma determinada palavra dentro de uma célula no Excel.

Fórmula genérica

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Argumentos

Célula: A célula da qual você deseja extrair o texto antes de uma determinada palavra.

palavra: A palavra que você deseja extrair todo o texto antes dela. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Neste exemplo, vamos extrair todos os textos antes da palavra “Excel”, então digitamos a palavra diretamente na fórmula e a colocamos entre aspas duplas. Ou você pode fazer referência a uma célula que contém a palavra “Excel”.

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

notas:

1) Esta fórmula diferencia maiúsculas de minúsculas.

2) Se a palavra digitada tiver duplicatas na mesma célula, a fórmula só extrai o texto após a primeira ocorrência.

2.7.2 Extrair texto após uma determinada palavra com fórmula

Para extrair texto após uma determinada palavra, você pode aplicar a seguinte fórmula para fazê-lo.

Fórmula genérica

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Argumentos

Célula: A célula da qual você deseja extrair o texto após uma determinada palavra.

palavra: A palavra que você deseja extrair todo o texto depois dela. Pode ser uma referência de célula ou uma cadeia de texto real entre aspas duplas;

Selecione uma célula, insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Selecione a célula de resultado e arraste sua alça de preenchimento automático para baixo para aplicar essa fórmula a outras células.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Então você pode ver todos os textos após a palavra “Excel” em cada célula ser extraída, conforme mostrado na captura de tela abaixo.

notas:

1) Esta fórmula não diferencia maiúsculas de minúsculas.

2) Se a palavra digitada tiver duplicatas na mesma célula, a fórmula só extrai o texto após a primeira ocorrência.

2.7.3 Extraia texto antes ou depois de uma determinada palavra com uma ferramenta incrível

Se você acha que o uso de fórmula pode causar muitos inconvenientes, aqui recomenda o Extrair Texto utilidade de Kutools for Excel. Esse recurso ajuda a automatizar a tarefa de extração no Excel com apenas alguns cliques.

1. Clique Kutools > Texto > Extrair Texto para ativar esse recurso.

2. No Extrair Texto caixa de diálogo, faça as seguintes configurações.

2.1) Certifique-se de que você está na Extrair por localização aba;

2.2) No Variação caixa, clique no

botão para selecionar o intervalo de células onde deseja extrair os textos;

2.3) No Opções seção:

Para extrair todos os textos antes de uma palavra, escolha a antes do texto botão de opção e digite a palavra na caixa de texto;

Para extrair todos os textos após uma palavra, escolha a depois do texto botão de opção e digite a palavra na caixa de texto.

2.4) Clique no OK botão. Veja a imagem:

notas: Se você deseja criar resultados dinâmicos, verifique o Inserir como uma fórmula caixa. Em seguida, os resultados serão atualizados automaticamente quando os dados no intervalo forem alterados.

3. Então um Extrair Texto caixa de diálogo aparece, você precisa selecionar uma célula para produzir o resultado e, em seguida, clique no botão OK botão.

Em seguida, os textos antes ou depois de uma determinada palavra dentro de cada célula no intervalo selecionado são extraídos imediatamente.

Observação: Esse recurso diferencia maiúsculas de minúsculas.

Clique para saber mais sobre este recurso.

Se você quiser ter um teste gratuito (30 dias) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.

3. Extraia entre caracteres/palavras

Se você deseja extrair texto entre determinados caracteres ou palavras, tente os métodos a seguir.

3.1 Extrair texto entre dois caracteres

Para extrair texto entre dois caracteres, que podem ser iguais ou diferentes. Esta seção fornece vários métodos, e você pode escolher um deles de acordo com suas necessidades.

3.1.1 Extrair texto entre dois mesmos caracteres com fórmula

Conforme mostrado na captura de tela abaixo, há uma lista de strings de texto na coluna B e você deseja extrair a parte do número entre os caracteres “/” de cada célula do intervalo, a fórmula a seguir pode ser um favor.

Selecione uma célula em branco, copie ou insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Selecione a célula de resultado e arraste-a para baixo para obter os resultados de outras células na lista.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Em seguida, o texto entre dois mesmos caracteres “/” é extraído de cada célula do intervalo. Veja captura de tela:

Observação:

1) B5 é a célula da qual você deseja extrair o texto entre dois mesmos caracteres;

2) "/” são dois mesmos caracteres que você deseja extrair texto entre eles.

Você precisa alterar essas variáveis com base em seus próprios dados.

3.1.2 Extrair texto entre dois caracteres diferentes com fórmula

Depois de aprender como extrair texto entre dois mesmos caracteres dentro de uma célula, aqui vamos demonstrar uma fórmula para extrair texto entre dois caracteres diferentes. Conforme mostrado na captura de tela abaixo, para extrair apenas o endereço de e-mail entre “<” e “>” de cada célula na coluna B, você pode fazer o seguinte.

Fórmula genérica

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

Argumentos

Célula: A célula da qual você deseja extrair o texto entre dois caracteres diferentes;

End_char: O caractere final dos dois caracteres diferentes;

Start_char: O caractere inicial dos dois caracteres diferentes.

Selecione uma célula em branco, copie ou insira a fórmula abaixo e pressione o botão Entrar chave para obter o resultado. Selecione essa célula de resultado e arraste sua alça de preenchimento automático para baixo para aplicar essa fórmula a outras células.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Você pode ver que apenas o texto entre os caracteres especificados é extraído conforme mostrado na captura de tela acima.

3.1.3 Extraia texto entre dois caracteres com uma ferramenta incrível

Aqui altamente recomenda o Extraia strings entre o texto especificado característica de Kutools for Excel para ajudá-lo a extrair facilmente texto entre dois caracteres iguais ou diferentes dentro de uma célula no Excel.

1. Selecione uma célula em branco para produzir o resultado e clique em Kutools > Fórmula Helper > Fórmula Helper.

2. No Auxiliar de Fórmulas caixa de diálogo, faça as seguintes configurações.

2.1) Verifique o filtros caixa e, em seguida, digite a palavra "Extrair" na caixa de texto;

2.2) No Escolha uma lista de fórmulas caixa, clique no Extraia strings entre o texto especificado opção;

2.3) No Entrada de argumentos seção:

Na série Célula box, selecione uma célula onde deseja extrair o texto (aqui seleciono a célula B5);

Na série Iniciar char (s) caixa, insira o caractere inicial dos dois caracteres diferentes;

Na série Fim char(s), insira o caractere final dos dois caracteres diferentes.

2.4) Clique Ok. Veja a imagem:

3. Em seguida, apenas o texto entre “<” e “>” dentro da célula B5 é extraído. Enquanto isso, uma fórmula foi criada, você pode selecionar esta célula de resultado e, em seguida, arrastar sua alça de preenchimento automático para baixo para extrair textos de outras células na mesma lista.

Clique para saber mais sobre este recurso.

Se você quiser ter um teste gratuito (30 dias) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.

3.1.4 Extrair texto entre dois caracteres (incluindo os caracteres) por regra

Se você quiser manter os dois caracteres após a extração, tente aplicar uma regra no recurso Extrair Texto do Kutools for Excel.

1. Clique Kutools > Texto > Extrair Texto.

2. No Extrair Texto caixa de diálogo, faça as seguintes configurações.

2.1) Clique no Extrair por regra aba;

2.2) No Variação seção, clique no

botão para selecionar o intervalo de células onde deseja extrair o texto entre os caracteres;

2.3) No Texto caixa, entre <*>;

Dicas: "<"E">” são os caracteres que você deseja extrair texto entre eles, * é um curinga que representa qualquer número de caracteres. Você pode inserir a condição de acordo com suas necessidades.

2.4) Clique no Adicionar botão para adicionar a condição ao Descrição da regra caixa de listagem;

2.5) Clique no Ok botão. Veja a imagem:

3. Outro Extrair Texto caixa de diálogo aparece, selecione uma célula para gerar o resultado e, em seguida, clique no botão OK botão.

Em seguida, o texto entre os caracteres especificados (incluindo os caracteres) é extraído de cada célula no intervalo selecionado em massa.

Se você quiser ter um teste gratuito (30 dias) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.

3.2 Extrair texto entre duas palavras

Além de extrair texto entre dois caracteres, você também pode precisar extrair texto entre duas palavras. Por exemplo, extraia todas as sequências de texto entre duas palavras “KTE” e “recurso” de cada célula na coluna B, conforme mostrado na captura de tela abaixo. Você pode tentar um dos seguintes métodos para fazê-lo.

3.2.1 Extrair texto entre duas palavras com fórmula

Você pode usar uma fórmula baseada na função MID e na função SEARCH para extrair todas as sequências de texto entre duas palavras em uma célula.

Fórmula genérica

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

Argumentos

Célula: a célula da qual você deseja extrair todas as sequências de texto entre duas palavras;

Start_word: a palavra inicial das duas palavras que você deseja extrair todas as sequências de texto depois dela;

Comprimento1: o comprimento do caractere da palavra inicial.

End_word: a palavra final das duas palavras que você deseja extrair todas as sequências de texto antes dela.

Comprimento2: o comprimento do caractere da palavra inicial mais 1.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Importante: Na fórmula, o número 3 representa o comprimento do caractere da palavra "KTE"; o número 4 representa o comprimento do caractere da palavra "KTE" mais 1.

Você pode ver que todas as sequências de texto entre as duas palavras especificadas são extraídas de cada célula na coluna B.

3.2.2 Extraia texto entre duas palavras com uma ferramenta incrível

Para muitos usuários do Excel, as fórmulas podem ser difíceis de lembrar e manipular. Aqui com o Extraia strings entre o texto especificado característica de Kutools for Excel, você pode facilmente extrair texto entre duas palavras com apenas alguns cliques.

1. Selecione uma célula para gerar o resultado e clique em Kutools > Fórmula Helper > Fórmula Helper.

2. No Fórmula Helper caixa de diálogo, você precisa configurar da seguinte maneira.

2.1) Verifique o filtros caixa e, em seguida, digite a palavra "Extrair" na caixa de texto;

2.2) No Escolha uma lista de fórmulas caixa, clique no Extraia strings entre o texto especificado opção;

2.3) No Entrada de argumentos seção:

Na série Célula box, selecione uma célula onde deseja extrair o texto (aqui seleciono a célula B5);

Na série Iniciar char (s) caixa, digite a palavra inicial das duas palavras que você deseja extrair todas as sequências de texto depois dela;

Na série Caracter(s) final(is) caixa, digite a palavra final das duas palavras que você deseja extrair todas as sequências de texto antes dela.

2.4) Clique Ok. Veja a imagem:

3. Em seguida, todas as cadeias de texto entre duas palavras “KTE” e “feature” dentro da célula B5 são extraídas. Enquanto isso, uma fórmula foi criada, você pode selecionar esta célula de resultado e, em seguida, arrastar sua alça de preenchimento automático para baixo para extrair textos de outras células na mesma lista.

Se você quiser ter um teste gratuito (30 dias) deste utilitário, por favor clique para fazer o downloade, em seguida, aplique a operação de acordo com as etapas acima.

Extrair números por posição

Para uma lista de strings alfanuméricas, pode haver três casos:

O número está no início do texto;
O número está no final do texto;
O número pode estar em qualquer lugar no texto.

Nesta seção, forneceremos diferentes métodos que podem ser usados para extrair os números em cada caso mencionado acima.

1 Extraia o número da esquerda de uma string

Esta parte apresentará uma fórmula para ajudá-lo a extrair apenas os números que aparecem antes do texto em uma célula.

Fórmula genérica

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

Argumentos

Célula: A célula da qual você deseja extrair o número à esquerda da string de texto.

Observação:

1) Se você estiver usando o Excel 2019 e anterior, você precisa pressionar o botão Ctrl + mudança + Entrar teclas para confirmar esta fórmula de matriz.

2) Se você estiver usando o Excel 365 ou Excel 2021, basta confirmar esta fórmula com o Entrar chave.

Selecione uma célula em branco, insira a fórmula abaixo e pressione Ctrl + mudança + Entrar or Entrar chave para obter o resultado. Selecione esta célula de resultado e arraste sua alça de preenchimento automático para baixo para obter o número de outras células.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

notas:

1) Se uma célula contiver apenas números, o número inteiro será extraído.

2) Esta fórmula extrai apenas os números à esquerda de uma string de texto. Se existirem números no meio ou no final da string de texto, eles serão ignorados.

2 Extraia o número da direita de uma string

Conforme mostrado na captura de tela abaixo, para extrair apenas os números que aparecem após o texto em uma célula, tente a seguinte fórmula.

Fórmula genérica

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

Argumentos

Célula: A célula da qual você deseja extrair o número do início da string de texto.

Observação:

1) Se você estiver usando o Excel 2019 e anterior, você precisa pressionar o botão Ctrl + mudança + Entrar teclas para confirmar esta fórmula de matriz.

2) Se você estiver usando o Excel 365 ou Excel 2021, basta confirmar esta fórmula com a tecla Enter.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

notas:

1) Se uma célula contiver apenas números, o número inteiro será extraído.

2) Esta fórmula apenas extrai os números da direita de uma string de texto. Se existirem números no meio ou no início da string de texto, eles serão ignorados.

3. Extraia todos os números de qualquer posição em uma string de texto

Os métodos acima ajudam a extrair o número apenas da esquerda ou direita de uma string de texto. Se você deseja extrair todos os números de qualquer lugar em uma string de texto, aqui fornecemos três métodos para você fazer isso.

3.1 Extraia todos os números de qualquer lugar em uma string com fórmula

Você pode aplicar a seguinte fórmula para extrair todos os números de qualquer lugar em uma string de texto no Excel.

1. Selecione uma célula em branco, copie ou insira a fórmula abaixo e pressione o botão Entrar tecla para obter todos os números da célula B5.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. Selecione a célula de resultado e arraste sua alça de preenchimento automático para baixo para obter todos os números de outras células.

3.2 Extraia todos os números de qualquer lugar em uma string com VBA

A fórmula acima é muito longa e complicada para muitos usuários do Excel. Na verdade, você pode executar um script VBA para automatizar a tarefa no Excel. Você pode fazer o seguinte.

1. aperte o outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Na abertura Microsoft Visual Basic para Aplicações janela, clique em inserção > Módulo. Em seguida, copie o VBA abaixo na janela de código do módulo.

Código VBA: Extraia todos os números de qualquer lugar em uma string de texto

Sub ExtrNumbersFromRange() 'Atualizado por Extendoffice 20220106 Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox("Por favor, selecione strings de texto :", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Então Exit Sub Set xRRg = Application.InputBox("Por favor, selecione a célula de saída:", xTitleId, "", Type:=8) Se TypeName(xRRg) = "Nothing" Então Exit Sub xI = 0 strNumber = "" Para cada xRg Em xDRg xI = xI + 1 nCellLength = Len(xRg) For xNumber = 1 Para nCellLength If IsNumeric(Mid(xRg, xNumber, 1)) Então strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Next xRg End Sub

3. aperte o F5 chave para executar o código. Na abertura KutoolsforExcel caixa de diálogo, selecione o intervalo de células onde você deseja extrair todos os números de cada célula e, em seguida, clique no botão OK botão.

4. Depois outro KutoolsforExcel caixa de diálogo aparece. Nesta caixa de diálogo, selecione uma célula de destino e clique em OK.

Em seguida, todos os números são extraídos de cada célula no intervalo selecionado em massa.

4. Extraia números após texto específico

Conforme mostrado na captura de tela abaixo, para extrair quaisquer números após o texto específico “Não.”, esta seção fornece dois métodos para ajudá-lo a fazer isso.

4.1 Extrair números após um texto específico com fórmula

Você pode aplicar a seguinte fórmula para extrair números após um texto específico em uma célula no Excel.

Fórmula genérica:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

Argumentos

Célula: A célula da qual você deseja extrair números após um texto específico;

Texto: O texto que você deseja extrair números depois dele.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

notas:

1) Se uma célula não contiver o texto específico, a fórmula retornará #N/A.

2) A fórmula diferencia maiúsculas de minúsculas.

4.2 Extrair números após um texto específico com função definida pelo usuário

A seguinte função definida pelo usuário também pode ajudar a extrair números após um texto específico em uma célula. Por favor, faça o seguinte.

1. aperte o outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. No Microsoft Visual Basic para Aplicações janela, clique em inserção > Módulo, e copie o código VBA abaixo na janela de código do módulo.

Código VBA: extrair números após um texto específico em uma célula

Função GetNumberAfterTheChar(Rng As Range, Char As String) 'Atualizado por Extendoffice 20220106 Dim xValue As String Dim xRntString As String Dim xStart As Integer Dim xC xValue = Rng.Text xStart = InStr(1, xValue, Char, vbTextCompare) If IsEmpty(xStart) Then GetNumberAfterTheChar = "" Exit Function End If If xStart < 1 Then GetNumberAfterTheChar = "" Sair da função End If xStart = xStart - 1 + Len(Char) If xStart < 1 Then GetNumberAfterTheChar = "" Sair da função End If xValue = Mid(xValue, xStart + 1) xRntString = "" Para xI = 1 Para Len(xValue) xC = Mid(xValue, xI, 1) Selecione Case Asc(xC) Case 48 Para 57 xRntString = xRntString & xC Case Else Exit For End Select Next GetNumberAfterTheChar = xRntString End Function

3. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.

4. Selecione uma célula, insira a fórmula abaixo e pressione o botão Entrar chave. Selecione essa célula de resultado e arraste sua alça de preenchimento automático para baixo para aplicar essa fórmula a outras células.

=GetNumberAfterTheChar(B5,"No. ")

notas:

1) Nesta fórmula, B5 é a célula onde você deseja extrair o número, e “Não. ” é o texto específico que você deseja extrair o número depois dele. Você pode alterá-los conforme necessário.

2) Se houver um delimitador entre o texto específico e o número, adicione o delimitador no final do texto. Nesse caso, adicionei um espaço após o texto “No.”, que finalmente é exibido como “No. ”.

3) Este método não diferencia maiúsculas de minúsculas;

4) Se uma célula não contiver o texto específico, a fórmula retornará um resultado em branco.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.

Leia mais... Download grátis... Compra...

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!