Guru do Excel e Power BI

Boa tarde. Qual seria a fórmular para eu encontrar números em um texto e retorna-los como uma lista.

Por exemplo: Tenho uma redação e nela existem no meio do texto números de tamanhas diversos e de extensão diversa, como 99998899, 87345 , 1118777 , etc.
Gostaria de saber uma formula para procurar todos números no texto e imprimi-los como uma lista. Podem me ajudar com isso? Grata!

Anexe uma pasta de trabalho do Excel com um exemplo, por favor. Coloque o resultado esperado.

Aqui babdallas a planilha com o resultado esperado. Grata.

babdallas escreveu:Anexe uma pasta de trabalho do Excel com um exemplo, por favor. Coloque o resultado esperado.

Aqui a planilha com o resultado esperado. Obrigada.

Usei PowerQuery. Só clicar em Atualizar Tudo. Com fórmula vai ficar complexo.

Encontrei esta UDF (GetNums) que extrai múltiplos grupos de números de uma célula, mas retorna todos juntos.

Código: Selecionar todos

Function GetNums(sInp As String) As String
    Dim i As Long
    
    For i = 1 To Len(sInp)
        Select Case Mid(sInp, i, 1)
            Case "0" To "9"
                GetNums = GetNums & Mid(sInp, i, 1)
        End Select
    Next i
End Function

Em sua planilha, tecle Alt+F11
Na nova janela, clique Inserir, Módulo e cole o código acima.
Salve como planilha habilitada para Macro.

Para separar em grupos de 8 dígitos, use esta fórmula:

Código: Selecionar todos

=SEERRO(EXT.TEXTO(GetNums(TEXTO!A$2);1+(LIN(Z1)-LIN(Z$1))*8;8);"")

Copie para baixo.

Favor conferir no anexo.

Good luck!

babdallas escreveu:Usei PowerQuery. Só clicar em Atualizar Tudo. Com fórmula vai ficar complexo.

Muito obrigada. Éxiste a possibilidade de eu colocar uma senha nessa Power Query para que outras pessoas nao modifiquem?

Estevão, essa planilha anexa elá esta funcionando ou preciso fazer algo? Não enendi direito

... Não domino muito o excel...
Grata

Estevaoba escreveu:Encontrei esta UDF (GetNums) que extrai múltiplos grupos de números de uma célula, mas retorna todos juntos.
Código: Selecionar todos
Function GetNums(sInp As String) As String
    Dim i As Long
    
    For i = 1 To Len(sInp)
        Select Case Mid(sInp, i, 1)
            Case "0" To "9"
                GetNums = GetNums & Mid(sInp, i, 1)
        End Select
    Next i
End Function
Em sua planilha, tecle Alt+F11
Na nova janela, clique Inserir, Módulo e cole o código acima.
Salve como planilha habilitada para Macro.

Para separar em grupos de 8 dígitos, use esta fórmula:
Código: Selecionar todos
=SEERRO(EXT.TEXTO(GetNums(TEXTO!A$2);1+(LIN(Z1)-LIN(Z$1))*8;8);"")
Copie para baixo.

Favor conferir no anexo.

Good luck!

A planilha que postei está sim funcionando. Basta colocar o texto e os resultados aparecem na coluna B da aba NÚMEROS EXTRAÍDOS.
Coloquei as explicações para o caso de você precisar usar em outra planilha.

Ab.

Infelizmente ainda não é possível colocar senha no PowerQuery.

Entendi. Mas pelo que testei, ele separa em pacote de numeros de 8 dígitos, não é? Isso funcionaria se eu fosse localizar números do mesmo tamanho, com protocolos ou documentos,

mas a intenção é localizar telefone.
Neste caso, alguns são digitados com 11 digitos, outros com 9 , outros com 8 dígitos, entende? Creio que a lógica para essa situação seria localizar numerais e separá-los do texto pelo espaçamento.
Será que conseguimos sem powerquery? Porque preciso colocar uma senha para que meus colaboradores não desconfigure, e com power query não da para bloquear, né?
Desde já, muito obrigada.

Valeu parceiro!

Estevaoba escreveu:A planilha que postei está sim funcionando. Basta colocar o texto e os resultados aparecem na coluna B da aba NÚMEROS EXTRAÍDOS.
Coloquei as explicações para o caso de você precisar usar em outra planilha.

Ab.

Você pode proteger a pasta de trabalho (arquivo) ou a planilha (aba). Veja:

https://support.office.com/pt-br/articl ... 76f44cccc9

Talvez esta configuração no PowerQuery te ajude.

https://support.microsoft.com/pt-br/off ... t-br&ad=br

Babdallas, você que é expert em VBA, veja por favor se há como editar a UDF no meu post #53779 para possibilitar extrair valores numéricos separados como a autora do tópico deseja.

Ab.

Fiz com VBA, mas não com UDF e sim com uma subrotina. Não consegui pensar em uma maneira de fazer isso com UDF.

Encontrei outra UDF que retorna os valores numéricos separados, aí é mais fácil separar em células.

Segue anexo.

Ab.

Consegui transformar em UDF, retornando uma matriz com os números. Depois basta usar SEERRO+ÌNDICE+LINS para retorna os valores.

Abaixo, seguem os códigos da subsrotina e depois da UDF.

Subrotina

Código: Selecionar todos

Public Sub GetNums(sInp As String, rngCelula As Range)
    Dim i                   As Long
    Dim lngCont             As Long
    Dim strStringAtual      As String
    Dim strStringPosterior  As String
    Dim dblNum()            As Double
    
    'Conta qtos n?meros possui
    For i = 1 To VBA.Len(sInp)
        strStringAtual = VBA.Mid(sInp, i, 1)
        If i < VBA.Len(sInp) Then strStringPosterior = VBA.Mid(sInp, i + 1, 1)
        
        Select Case strStringAtual
            Case "0" To "9"
                If Not VBA.IsNumeric(strStringPosterior) Then lngCont = lngCont + 1
        End Select
    Next i
    
    ReDim dblNum(1 To lngCont) As Double
    
    lngCont = 1
    'Obt?m os npumeros em uma matriz
    For i = 1 To VBA.Len(sInp)
        strStringAtual = VBA.Mid(sInp, i, 1)
        If i < VBA.Len(sInp) Then strStringPosterior = VBA.Mid(sInp, i + 1, 1)
        
        Select Case strStringAtual
            Case "0" To "9"
                dblNum(lngCont) = dblNum(lngCont) & CDbl(strStringAtual)
                If Not VBA.IsNumeric(strStringPosterior) Then
                    lngCont = lngCont + 1
                End If
        End Select
    Next i
    
    rngCelula.Resize(UBound(dblNum)).Value2 = Application.Transpose(dblNum)
End Sub

Public Sub Teste()
    GetNums sInp:=Planilha1.Range("A2").Value, rngCelula:=Planilha3.Range("B2")
End Sub

UDF

Código: Selecionar todos

Public Function GetNums_UDF(sInp As String) As Variant
    Dim i                   As Long
    Dim lngCont             As Long
    Dim strStringAtual      As String
    Dim strStringPosterior  As String
    Dim dblNum()            As Double
    
    'Conta qtos n?meros possui
    For i = 1 To VBA.Len(sInp)
        strStringAtual = VBA.Mid(sInp, i, 1)
        If i < VBA.Len(sInp) Then strStringPosterior = VBA.Mid(sInp, i + 1, 1)
        
        Select Case strStringAtual
            Case "0" To "9"
                If Not VBA.IsNumeric(strStringPosterior) Then lngCont = lngCont + 1
        End Select
    Next i
    
    ReDim dblNum(1 To lngCont) As Double
    
    lngCont = 1
    'Obt?m os npumeros em uma matriz
    For i = 1 To VBA.Len(sInp)
        strStringAtual = VBA.Mid(sInp, i, 1)
        If i < VBA.Len(sInp) Then strStringPosterior = VBA.Mid(sInp, i + 1, 1)
        
        Select Case strStringAtual
            Case "0" To "9"
                dblNum(lngCont) = dblNum(lngCont) & CDbl(strStringAtual)
                If Not VBA.IsNumeric(strStringPosterior) Then
                    lngCont = lngCont + 1
                End If
        End Select
    Next i
    
    GetNums_UDF = Application.Transpose(dblNum)
End Function

Guru do Excel e Power BI

Extrair números de texto e listá-los

Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los

Re: Extrair números de texto e listá-los