Excel - Jak zwrócić ciąg zawierający tylko pierwszą liczbę, który ma od 7 do 10 znaków w dużym tekście? (najlepiej tylko formuły)

Próbuję wyodrębnić niektóre liczby z kilku komórek, z których każda zawiera duży fragment tekstu.

Ciągom liczbowym towarzyszą pewne słowa poprzedzające ciąg liczbowy, który próbuję wyodrębnić.

Próbowałem rozwiązać problem, używając funkcji takich jak MID, LEWO, PRAWO, DŁUGOŚĆ, ZNAJDŹ i WYSZUKAJ Jednak ciągle otrzymuję zły wynik.

Wynika to z trzech problemów z treścią tekstu:

• Pierwszą kwestią jest to, że poprzedzające ją słowa są różne dla każdej komórki. To spowodowałoby, że formuła byłaby bardzo złożona i wymagałaby funkcji IF, OR lub AND.
• Drugim problemem jest to, że ciąg liczb, który próbuję wyodrębnić, ma różną długość od 7 do 10 liczb
• Trzeci problem polega na tym, że ciąg tylko liczbowy, który próbuję wyodrębnić, nie jest jedynym ciągiem liczbowym w tekście komórki

Rozwiązanie, które obecnie mam, dodaje inne znaki inne niż liczby; takie jak spacje, przecinki i nawiasy, jeśli ciąg liczb jest krótszy niż 10 liczb.

Więc w zasadzie chcę wiedzieć, czy istnieje sposób na wyodrębnienie ciągu zawierającego tylko pierwszą liczbę, który ma od 7 do 10 znaków z treści tekstu? Najlepiej tylko z formułą, ale VBA jest również możliwe.

Pomyślałem, że zmienię OP z przykładowymi danymi, ponieważ jeszcze nie otrzymałem odpowiedzi.

Przykład danych, którymi próbuję manipulować, można znaleźć tutaj: https://www.sendspace.com/file/f7kn6n

Ponieważ od jakiegoś czasu nie otrzymałem odpowiedzi, pomyślałem, że zaktualizuję zrzut ekranu przykładowych danych przesłanych kilka dni temu.

Przykładowe dane

Jeśli ciąg znaków będzie zawsze pierwszym zestawem cyfr w ciągu, możesz użyć następującej formuły. Jest to formuła tablicowa wprowadzona przez przytrzymanie ctrl+shiftpodczas naciskania enter:

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

Jeśli przed cyfrą, którą chcesz wyodrębnić, mogą być krótsze lub dłuższe ciągi cyfr, użyłbym UDF poniżej. Korzysta z wyrażeń regularnych, aby znaleźć ciąg pierwszej cyfry o długości dokładnie 7-10 cyfr. Ponieważ zwraca ciąg, powinien zachować wszystkie początkowe zera.

Użyj go w formule, takiej jak:

=FirstDigits(A1)

Skopiuj poniższy kod do zwykłego modułu:

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

Zignoruj ​​tę odpowiedź, jeśli otrzymasz odpowiedź „tylko formuła”, która spełnia Twoje wymagania.

Ta mała funkcja UDF () zwróci pierwszą liczbę w ciągu spełniającym Twoje wymagania:

Kod:

Public Function GetNumber(sIN As String) As Long
    Dim L As Long, i As Long
    Dim s As String

    s = sIN
    L = Len(s)
    For i = 1 To L
        ch = Mid(s, i, 1)
        If ch Like "[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

Funkcje zdefiniowane przez użytkownika (UDF) są bardzo łatwe w instalacji i obsłudze:

1. ALT-F11 wyświetla okno VBE
2. ALT-I ALT-M otwiera nowy moduł
3. wklej i zamknij okno VBE

Jeśli zapiszesz skoroszyt, UDF zostanie z nim zapisany. Jeśli używasz wersji Excela późniejszej niż 2003, musisz zapisać plik jako .xlsm zamiast .xlsx

Aby usunąć UDF:

1. wywołać okno VBE jak wyżej
2. wyczyść kod
3. zamknij okno VBE

Aby użyć UDF z Excela:

=getnumber(A1)

Aby dowiedzieć się więcej o makrach, zobacz:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

i

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

a szczegółowe informacje na temat UDF można znaleźć:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Makra muszą być włączone, aby to działało!

Oto formuła, która moim zdaniem działa.

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

Szczerze mówiąc, nie mogę tego wyjaśnić, gdy jest w tym formacie. Zaczęło się tutaj, co daje formułę do wyodrębnienia n-tego słowa

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

Potem musiałem sprawić, by sprawdził wszystkie słowa, więc dodałem ROW (1 $: 25 $) zamiast N (daje tylko tablicę 1:25).

Następnie było sprawdzić, czy jest to liczba ( ISNUMBER ) sprawdzenie długości (dwa len> = 7 i len <= 10 funkcje, pomnożyć przez układ 1:25.

Następnie musiałem wyodrębnić najmniejszą liczbę nie równą 0, co zrobiłem z wartością 1 / max (iferror (1 / formuła. Jeśli kryteria nie są spełnione, zwracana wartość to 0, błędy 1/0 są błędne, więc przypisywana jest wartość -1). inne liczby to 1 / N, weź maksimum, a następnie odwróć, co daje najmniejszą liczbę nie 0. Przez tę liczbę z powrotem w pierwotnym równaniu powyżej, aby zwrócić to słowo.

W tej chwili działa tylko dla pierwszych 25 słów ciągu (może przedłużyć wiersz (1:25)). Celuje w komórkę A1.