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.

---

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

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

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.

Note: 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.

---

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

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

=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.

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.

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

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

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.

=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

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

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

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by 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(Position - 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)

Or

=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 função definida pelo usuário fornecida acima, aqui recomenda o Kutools para 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.

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.

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

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

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

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

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

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 essa fórmula a outras células.

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)

Observações:

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

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.

Observações:

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.

Observações: 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.

---

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:

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

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.

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.

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 para Excel.

1. Clique Kutools > Texto > Extrair Texto.

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

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.

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

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(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Note: 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.

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.

---

Extrair números por posição

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

1. O número está no início do texto;
2. O número está no final do texto;
3. 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

Observação:

Selecione uma célula em branco, insira a fórmula abaixo e pressione Ctrl + Shift + 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)

Observações:

---

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

Observação:

Selecione uma célula em branco, insira a fórmula abaixo e pressione Ctrl + Shift + 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.

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

Observações:

---

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()
'Updated by 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("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        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 Kutools para Excel 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 Kutools para Excel 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

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.

=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}))

Observações:

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

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by 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 = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 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. ")

Observações:

---

Artigos relacionados: