Ir para o conteúdo principal

Como extrair o número apenas da string de texto no Excel?


Método 1: extrair o número apenas de strings de texto com fórmula

A seguinte fórmula longa pode ajudá-lo a extrair apenas os números das strings de texto, faça o seguinte:

Selecione uma célula em branco onde deseja produzir o número extraído e digite esta fórmula: = SUMPRODUCT (MID (0 & A5, LARGE (INDEX (ISNUMBER (- MID (A5, ROW (INDIRECT ("1:") & LEN (A5))), 1)) * ROW (INDIRECT ("1:" & LEN (A5) )), 0), ROW (INDIRETO ("1:" & LEN (A5)))) + 1, 1) * 10 ^ ROW (INDIRETO ("1:" & LEN (A5))) / 10)e arraste a alça de preenchimento para preencher o intervalo de que você precisa para aplicar esta fórmula. Veja a imagem:

doc extrair números apenas 2

Observações:

  • 1. A5 representa os primeiros dados que você deseja extrair apenas números da lista.
  • 2. O resultado será mostrado como 0 quando não houver números na string.

Extraia números apenas de strings de texto:

Com o Kutools for Excel'S EXTRATO NÚMEROS função, você pode extrair rapidamente apenas números das células da string de texto. Clique para baixar o Kutools para Excel!

doc extrair números apenas 14


Método 2: extrair o número apenas de strings de texto com código VBA

Aqui está um código VBA que também pode lhe fazer um favor. Faça o seguinte:

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

2. Clique inserção > Móduloe cole o seguinte código no Módulo Janela.

Código VBA: Extraia o número apenas da string de texto:

Sub ExtrNumbersFromRange()
    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. E então, pressione F5 para executar este código e uma caixa de prompt é exibida para lembrar a seleção do intervalo de texto que deseja usar, consulte a captura de tela:

doc extrair números apenas 3

4. Então clique OK, outra caixa de prompt está seguindo, selecione uma célula para gerar o resultado, veja a captura de tela:

doc extrair números apenas 4

5. Finalmente, clique em OK botão, e todos os números nas células selecionadas foram extraídos de uma vez.


Método 3: Extraia o número apenas da string de texto com o Kutools para Excel

Kutools for Excel também tem uma função poderosa que é chamada EXTRATO NÚMEROS, com esta função, você pode extrair rapidamente apenas os números das sequências de texto originais.

Kutools for Excel : com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias

Depois de instalar Kutools for Excel, faça o seguinte:

1. Clique em uma célula ao lado da string de texto onde você colocará o resultado, veja a captura de tela:

doc extrair números apenas 5

2. Então clique Kutools > Funções Kutools > Texto > EXTRATO NÚMEROS, veja a captura de tela:

doc extrair números apenas 6

3. No Argumentos de função caixa de diálogo, selecione uma célula da qual deseja extrair os números do TXT caixa de texto e digite verdadeiro or falso no N caixa de texto, veja a captura de tela:

doc extrair números apenas 7

Note: o argumento N é um item opcional, se você inserir verdadeiro, ele retornará os números como numéricos, se você inserir falso, ele retornará os números como formato de texto, o padrão é falso, então você pode deixar em branco.

4. E, em seguida, clique em OK, os números foram extraídos da célula selecionada e, em seguida, arraste a alça de preenchimento para baixo até as células nas quais deseja aplicar esta função, você obterá o seguinte resultado:

doc extrair números apenas 8

Clique para baixar e testar gratuitamente o Kutools for Excel agora!


Método 4: extrair o número decimal apenas da string de texto com fórmula

Se as cadeias de texto incluem alguns números decimais em sua planilha, como você pode extrair apenas os números decimais das cadeias de texto?

A fórmula abaixo pode ajudá-lo a extrair os números decimais das strings de texto de forma rápida e fácil.

Insira esta fórmula:=LOOKUP(9.9E+307,--LEFT(MID(A5,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A5&"1023456789")),999),ROW(INDIRECT("1:999")))), E então preencha a alça até as células que você deseja que contenham esta fórmula, todos os números decimais foram extraídos das strings de texto, veja a captura de tela:

doc extrair números apenas 13


Extraia números apenas de strings com o Kutools para Excel

Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

Artigos relativos:

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição


Office Tab 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!
Comments (61)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
how to get a extract extact numbers from text
This comment was minimized by the moderator on the site
Hi, I'm looking to list all numbers in a string before a certain character. The string has letters and numbers, so I want to remove the letters and only list the numbers that appear before the second minus sign "-". Example:MUMUT-S941-22460991e002
I want that to isolate the numbers 941 in the above example.
This comment was minimized by the moderator on the site
How to use Index instead of Indirect to extract number from string. Indirect is volatile and Index can replace it as per http://www.excelhero.com/blog/2011/03/the-imposing-index.html. Thanks Ahead!
This comment was minimized by the moderator on the site
Hello! I found the following formula to extract only the numbers from text strings in Excel very useful

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

Could you explain more on this formula as it seems quite complicated? Many thanks.
This comment was minimized by the moderator on the site
Reading ID: 3151346 BeatO User ID: 239930 Name: Mahesh Phone: 9823010759 Email: City: nashik State: Maharashtra Reading: 55 Meal Time: Random Meal Type: RANDOM Reading Time: 2020-03-01 00:15:57 View user readings in Portal



I want to extract numeric no. post Reading text
This comment was minimized by the moderator on the site
Hello, how can i extract the cheque no.only from the below text

OUTWARD CLEARING CLRG CHQ DEPOSIT CHQ. NO: 000123~700320456 ABCD ARAB PLAZ BRANC Value DATE, 01/02/2020 - S79519642

i tried below formula and its working but i need to change the 1:57 by calculating how many character before the first digit plus how many digits i want to extract.
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+5 im using this formula to know how many characters before the first number..

=TEXTJOIN("",TRUE,IFERROR(MID(A3,ROW(INDIRECT("1:57")),1)+0,""))
This comment was minimized by the moderator on the site
=Mid(A2,find("CHQ. NO", A2)+9,6)
This comment was minimized by the moderator on the site
Hi. I wonder to know is it possible to extract from the next string '102-105+106-10605-10605 -10631-10632-10633-10634-10635+107' all values of only three digits that have sign '-' (i.e. minus) before them (or plus - doesn't matter). Then extract extract all 5-digits values with the same rule? If this is possible to do I will save many many hours of manual work that I can devote to lot's of uselful tasks.

P.S.
I know that regular expressions are able to do that, BUT... I hate their syntax, so I'm searching any other method to complete this task.

Thanks in advance.
This comment was minimized by the moderator on the site
Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40

Ali Baba Dark Chocolate 25g box 12 pcs
Ali Baba Dark Chocolate 20g*24 box
Cadbury 5 Star White Chocolate 15g
Kinder 2 White Chocolate 25g*24
ALpella Biscuits W/Marshmallow300g
Alpella Chocolate 40g
This comment was minimized by the moderator on the site
did anyone answer or did you figure this out? this is exactly my problem right - even down to the letter 'g'. the problem with the formula I am using now is that it returns the first number, if there are more than one, from the string and not the number I am wishing to return.
This comment was minimized by the moderator on the site
Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40


Ali Baba Dark Chocolate 25g box 12 pcs
Ali Baba Dark Chocolate 20g*24 box
Cadbury 5 Star White Chocolate 15g
Kinder 2 White Chocolate 25g*24
ALpella Biscuits W/Marshmallow300g
Alpella Chocolate 40g
This comment was minimized by the moderator on the site
can any one answer this question, how to extract the no's
This comment was minimized by the moderator on the site
You may try select the number before g in the first cell, copy and paste it in new cell. Then press CTRL + E. All the number before g will be auto generated. 

This comment was minimized by the moderator on the site
Tkssssss you saved my day :) God bless you
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations