Excel - Как вернуть первую строку, состоящую только из цифр, длиной от 7 до 10 символов в большом тексте? (желательно только формулы)

Question

Я пытаюсь извлечь некоторые числа из нескольких ячеек, каждая из которых имеет большой объем текста.

Числовые строки сопровождаются определенными словами, которые предшествуют числовой строке, которую я пытаюсь извлечь.

Я пытался решить проблему, используя такие функции, как MID, LEFT, RIGHT, LEN, FIND и SEARCH. Однако я продолжаю получать неправильный результат.

Это связано с тремя проблемами с телами текста:

• Первая проблема состоит в том, что слова, которые предшествуют этому, различны для каждой ячейки. Это сделало бы формулу очень сложной и нуждающейся в функциях IF, OR или AND.
• Вторая проблема заключается в том, что числовая строка, которую я пытаюсь извлечь, имеет длину от 7 до 10 чисел
• Третья проблема заключается в том, что строка, состоящая только из цифр, которую я пытаюсь извлечь, не является единственной строкой с цифрами в тексте ячеек

Решение, которое у меня есть в настоящее время, добавляет другие символы, кроме цифр, такие как пробелы, запятые и скобки, если строка чисел меньше 10 чисел.

Так что, в основном, я хочу знать, есть ли способ извлечь строку из первого числа, длина которой составляет 7-10 символов, из основного текста? Предпочтительно только с формулой, но VBA также возможна.

---

Я решил отредактировать ОП с примерами данных, так как я еще не получил ответ.

Пример данных, которыми я пытаюсь манипулировать, можно найти здесь:https://www.sendspace.com/file/f7kn6n.

---

Поскольку я некоторое время не получал ответ, я решил обновить снимок экрана с данными примера, которые я загрузил несколько дней назад.

Пример данных

Answer 1

Игнорируйте этот ответ, если вы получите ответ «только для формул», который соответствует вашим требованиям.

---

---

Этот маленький UDF() вернет первое число в строке, отвечающей вашим требованиям:

Код:

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

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=getnumber(A1)

Чтобы узнать больше о макросах в целом, смотрите:

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

а также

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

а подробности о UDF смотрите в:

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

Макросы должны быть включены, чтобы это работало!

Answer 2

Вот формула, я думаю, работает.

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

Честно говоря, я не могу объяснить это один раз в этом формате. Это началось здесь, которая дает формулу для извлечения n-го слова

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

Затем я должен был проверить все слова, поэтому добавил N ($ 1:$ 25) вместо N (просто дает массив 1:25).

Затем мне пришлось проверить, было ли это число (isnumber), проверить длину (две функции len> = 7 и len <= 10 , умножить ее на массив 1:25.

Затем мне нужно было извлечь наименьшее число, отличное от 0, которое я сделал с 1/max(iferror(1/ формула. Если критерии не выполнены, возвращаемое значение равно 0, из них 1/0 ошибок, поэтому присваивается -1. Остальные числа 1/N, возьмите максимум и затем инвертируйте снова, что дает наименьшее число, а не 0 Через это число обратно в исходное уравнение выше, чтобы вернуть это слово.

Прямо сейчас это работает только для первых 25 слов строки (можно расширить строку (1:25)). Это предназначается для ячейки A1.

Answer 3

Если ваша строка цифр всегда будет первым набором цифр в вашей строке, то вы можете использовать следующую формулу. Это формула массива, введенная удерживанием ctrl+shift при нажатии enter:

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

Если перед той строкой, которую вы хотите извлечь, может быть более короткая или более длинная подстрока цифр, я бы использовал приведенную ниже UDF. Он использует регулярные выражения, чтобы найти строку первой цифры длиной ровно 7-10 цифр. Поскольку он возвращает строку, он должен сохранять любые начальные нули.

Используйте это в формуле, такой как:

=FirstDigits(A1)

Скопируйте приведенный ниже код в обычный модуль:

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