Excel - Como devolver o primeiro número apenas cadeia que tem entre 7 e 10 caracteres num corpo grande de texto? (preferencialmente apenas fórmulas)

Question

Estou tentando extrair alguns números de algumas células que possuem um grande corpo de texto.

As sequências numéricas são acompanhadas por certas palavras que precedem a sequência numérica que estou tentando extrair.

Eu tentei resolver o problema usando funções como MID, LEFT, RIGHT, LEN, FIND e SEARCH No entanto, continuo com o resultado errado.

Isso se deve a três questões com os corpos do texto:

• A primeira questão é que as palavras que a precedem são diferentes para cada célula. Isso tornaria a fórmula altamente complexa e com necessidade de funções IF, OR ou AND.
• A segunda questão é que a cadeia numérica que estou tentando extrair tem um comprimento variável entre 7 e 10 números.
• O terceiro problema é que o número que apenas a string que estou tentando extrair não é a única string numérica no corpo das células

A solução que tenho atualmente adiciona outros caracteres além de números, como espaços, vírgulas e colchetes, se a cadeia numérica tiver menos de 10 números.

Então, basicamente, eu quero saber se existe uma maneira de extrair o primeiro número apenas de uma string entre 7 e 10 caracteres do corpo do texto? De preferência apenas com a fórmula, mas o VBA também é uma possibilidade.

Pensei em editar o OP com os exemplos de dados, pois ainda não recebi uma resposta.

Um exemplo dos dados que estou tentando manipular pode ser encontrado aqui:   link

Como não recebi uma resposta há algum tempo, imaginei que atualizaria com uma captura de tela dos dados de exemplo que enviei há alguns dias.

Exemplo de dados

    

Answer 1

Se a sua sequência de dígitos for sempre o primeiro conjunto de dígitos da sua string, você poderá usar a seguinte fórmula. É uma fórmula de matriz inserida ao segurar ctrl+shift ao atingir enter :

=MAX(IFERROR(--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{7,8,9,10}),0))

Se houver substrings de dígitos menores ou mais longas antes do que você deseja extrair, use o UDF abaixo. Ele faz uso de expressões regulares para encontrar a primeira cadeia de dígitos com exatamente 7 a 10 dígitos. Como está retornando uma string, ela deve reter qualquer zero inicial.

Use em uma fórmula como:

=FirstDigits(A1)

Copie o código abaixo em um módulo normal:

Option Explicit
Function FirstDigits(S As String) As String
    Dim RE As Object, MC As Object
    Const sPat As String = "\b\d{7,10}\b"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = sPat
    If .test(S) Then
        Set MC = RE.Execute(S)
        FirstDigits = MC(0)
    Else
        FirstDigits = "No digit string 7-10 digits long"
    End If
End With

End Function

    

Answer 2

Ignore esta resposta se você receber uma resposta "somente de fórmula" que atenda aos seus requisitos.

Este pequeno UDF () retornará o primeiro número em uma string que atende aos seus requisitos:

Ocódigo:

PublicFunctionGetNumber(sINAsString)AsLongDimLAsLong,iAsLongDimsAsStrings=sINL=Len(s)Fori=1ToLch=Mid(s,i,1)IfchLike"[0-9]" Then
        Else
            Mid(s, i, 1) = " "
        End If
    Next i

    With Application.WorksheetFunction
        arr = Split(.Trim(s), " ")
    End With

    For Each a In arr
        If Len(a) > 6 And Len(a) < 11 Then
            GetNumber = CLng(a)
            Exit Function
        End If
    Next a
    GetNumber = 0
End Function

Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

1. ALT-F11 exibe a janela do VBE
2. ALT-I ALT-M abre um novo módulo
3. cole o material e feche a janela do VBE

Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover o UDF:

1. abrir a janela do VBE como acima
2. limpe o código
3. feche a janela do VBE

Para usar o UDF do Excel:

=getnumber(A1)

Para saber mais sobre macros em geral, consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!

    

Answer 3

Aqui está uma fórmula que eu acho que funciona.

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), 
    ((1/MAX(IFERROR(1/(
        ISNUMBER((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1))))*1)*
        (LEN((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1)))))>=7)*
        (LEN((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1)))))<=10)*
        (ROW($1:$25))),-1)))-1)*LEN(A1)+1, LEN(A1)))

Para ser sincero, não posso explicá-lo quando estiver neste formato. Começou aqui que dá uma fórmula para extrair a enésima palavra

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

Então eu tive que verificar todas as palavras adicionadas ROW ($ 1: $ 25) no lugar de N (apenas dá o array 1:25).

Depois tive que verificar se era um número ( isnumber ), verificar o tamanho (os dois len > = 7 e len < = 10 funções, multiplique pelo array 1:25.

Em seguida, precisei extrair o menor número não 0, o que fiz com a fórmula 1 / max (iferror (1 / ). Se os critérios não forem atendidos, o valor retornado será 0, 1 / 0 erros são atribuídos a -1 - Os outros números são 1 / N, pegue o máximo e então inverta novamente o que dá o menor número não 0. Através desse número de volta na equação original acima para retornar aquela palavra.

Neste momento, ele só funciona para as primeiras 25 palavras de uma string (poderia estender Row (1:25)). Ele atinge a célula A1.