Извлечь число из сложной строки, если оно попадает в заданный диапазон

Question

Мне нужна формула Excel (или макрос VBA), которая позволит мне извлечь значение из строки. Строка представляет собой последовательность слов, разделенных пробелами, в одной ячейке. Я хочу слово (представляющее размер велосипеда), которое либо

• число (предположительно, целое число, но оно не указано) от 47 до 60 (или некоторый другой диапазон, возможно, задается динамически), или
• одна из строк "sm", "med" или "lg".

Я ожидаю, что в строке будет ровно одно уточняющее слово, поэтому любой разумный ответ на

• нет подходящих слов или
• несколько уточняющих слов

будет приемлемым Размер может быть в разных позициях в строке. Примеры:

Cervelo P2 105 5800 56 '15                        the number 56 is the desired result
Cervelo P2 105 54 6000 '15                        the number 54 is the desired result
Cervelo P3 105 5800 60 '15                        the number 60 is the desired result
Cervelo P2 105 5800 sm '15                        the string sm is the desired result

Меня интересуют только целые слова, поэтому 58 (подстрока "5800") не подходит.

Прямо сейчас я снимаю '15 и затем извлекаю последние две цифры. Но этот подход работает, только если размер велосипеда является вторым по последнему значению. Однако, как показано выше, в некоторых случаях размер находится в других местах строки.

Как я могу сделать это с помощью формулы или макроса VBA в Excel?

Answer 1

Я сделал решение, используя VBA:

Public Function BikeSize(MinSize As Integer, MaxSize As Integer, datainput As String)
    Dim dataoutput() As Variant
    ReDim dataoutput(0)
    BikeSize = 0
    datasplitted = Split(datainput, " ")
    arraysize = UBound(datasplitted)
    j = 1
    For i = 0 To arraysize
        m = datasplitted(i)
        If m >= MinSize And m <= MaxSize Then
            ReDim Preserve dataoutput(j)
            dataoutput(j) = m
            j = j + 1
        End If
        If m = "sm" Or m = "med" Or m = "lg" Then
            ReDim Preserve dataoutput(j)
            dataoutput(j) = m
            j = j + 1
        End If
    Next i
    totalresults = UBound(dataoutput)
    Select Case totalresults
        Case 0
            BikeSize = 0
        Case 1
            BikeSize = dataoutput(totalresults)
        Case Else
            For i = 1 To totalresults
                wrongresult = wrongresult & dataoutput(i) & " - "
            Next i
            BikeSize = wrongresult
    End Select
End Function

Вы должны открыть Macros /Visual Basic, перейти к разделу Модули _> Добавить модуль и вставить код с правой стороны (также см. Как добавить VBA в MS Office?).

Затем, если ваша строка находится в ячейке A1, ваше минимальное значение для C1, ваше максимальное значение для D1, а затем для B1 вы просто помещаете =BikeSize(C1,D1,A1) чтобы получить результат.

Если строка не имеет совпадающего числа, она выводит ноль 0.

Если строка имеет более одного совпадающего числа, она выводит все совпадения, разделенные тире.

Он также распознает sm, med и lg.