Extract max decimal number from alphanumeric text

[mc51]

Hi, I have an excel file with the following data column

10mm
14.2x198.3x23mm
~3-5.5mm
10 x 15.5 x 20mm

I want to extract the max number of each cell and write to the cell next to it, so the result would be

<table class="cms_table"><tbody><tr class="cms_table_tr" valign="top"><td class="cms_table_td">10mm</td> <td class="cms_table_td"> 10</td><td style="vertical-align: top;">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">14.2x198.3x23mm</td> <td class="cms_table_td"> 198.3</td><td style="vertical-align: top;">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">~3-5.5mm</td> <td class="cms_table_td"> 5.5</td><td style="vertical-align: top;">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">10 x 15.5 x 20mm</td> <td class="cms_table_td"> 20</td><td style="vertical-align: top;">
</td> </tr> </tbody></table>


Can anyone help me? Thanks a lot.

 

[Jon von der Heyden]

Blimey mate! You must be on a get healthy campaign! Well I'm over 2 months off the cigarettes now. Feeling much better for it and put on nearly a stone!

On a downer though my MSDN sub expires in a few days . Downloading like mad now and pulling off all the product keys.

 

[Rick Rothstein]

Here is a non-RegExp function (UDF) that you can use...

Function GetMax(ByVal S As String) As Double
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9.]" Then Mid(S, X) = " "
  Next
  GetMax = Evaluate("=MAX(" & Replace(WorksheetFunction.Trim(S), " ", ",") & ")")
End Function

 

[Weaver]

Hi Weaver

The pattern is just searching for any sequence starting with a digit followed by zero or more digits/periods.

The Execute method of the regexp object is used to generate a collection of matching elements from the string passed to it (each match 'consumes' that portion of the string eg so if you have 20x15x30, the first match will be 20 and this is consumed from the string (rather than 2 being matched and then 0 producing a second match), leaving the next match to be 15 and then the last to be 30). I then iterated thru the collection to identify the max value.

Hope the above made sense - it made sense to me when I was thinking about it, but doesn't look so good now it's written down

Makes sense - I suppose it's just that whenever I've used regexp myself it's been to either test or modify a resulting string. I never knew about the collections function and wondered if that was a function of the pattern (regular expressions are still mentally filed under "confusing")