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?