Extract number from complex string if it falls in a given range

Question

I need an Excel formula (or VBA macro) that will allow me to extract a value from a string. The string is a sequence of words, separated by spaces, in a single cell. I want the word (representing a bike size) that is either

• a number (presumably an integer, but this is not specified) between 47 and 60 (or some other range, perhaps to be specified dynamically), or
• one of the strings "sm", "med", or "lg".

I expect that there will be exactly one qualifying word in the string, so any reasonable error-handling response to

• no qualifying words, or
• multiple qualifying words

will be acceptable. The size may be at various positions in the string. Examples:

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

I'm interested only in whole words, so 58 (substring of "5800") does not qualify.

Right now I am stripping off the '15 and then extracting the last two digits. But this approach works only if the bike size is the second to last value. However, as shown above, there are cases where the size is at other positions in the string.

How can I do this with a formula or VBA macro in Excel?

Answer 1

I made a solution using 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

You have to open Macros /Visual Basic go to Modules _> Add Module and paste the code on the right side (also see How do I add VBA in MS Office?).

Then if your string is on cell A1, your min value on C1, your max value on D1 then on B1 you simply put =BikeSize(C1,D1,A1) to get the result.

If the string doesn't have a matching number it outputs a zero 0.

If the string has more than one matching number it outputs all matching separated by a dash.

It also recognizes sm, med and lg.