CODEWITHSUNDEEP
excelvba
skkakkar
skkakkar

Reputation: 2838

To get appropriate maximum number from a string

My string is su=45, nita = 30.8, raj = 60, gita = 40.8 . This has reference to SO question Extract maximum number from a string I am utilizing maxNums function and getting result as 40.8 whereas I would like it to be 60. Where an amendment in code line would get me the desired result.Code reproduced below to avoid cross reference.If this string contains all numbers with decimal point then I get the correct result but the data in consideration from external sources could have whole numbers.

Option Explicit
Option Base 0    '<~~this is the default but I've included it because it has to be 0

Function maxNums(str As String)
    Dim n As Long, nums() As Variant
    Static rgx As Object, cmat As Object

    'with rgx as static, it only has to be created once; beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If
    maxNums = vbNullString

    With rgx
        .Global = True
        .MultiLine = False
        .Pattern = "\d*\.\d*"
        If .Test(str) Then
            Set cmat = .Execute(str)
            'resize the nums array to accept the matches
            ReDim nums(cmat.Count - 1)
            'populate the nums array with the matches
            For n = LBound(nums) To UBound(nums)
                nums(n) = CDbl(cmat.Item(n))
            Next n
            'test array
            'Debug.Print Join(nums, ", ")
            'return the maximum value found
            maxNums = Application.Max(nums)
        End If
    End With
End Function

Upvotes: 0

Views: 65

Answers (3)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

Without Regex:

Public Function maxNums(str As String) As Double
    Dim i As Long, L As Long, s As String, wf As WorksheetFunction, brr()
    Set wf = Application.WorksheetFunction
    L = Len(str)

    For i = 1 To L
        s = Mid(str, i, 1)
        If s Like "[0-9]" Or s = "." Then
        Else
            Mid(str, i, 1) = " "
        End If
    Next i

    str = wf.Trim(str)
    arr = Split(str, " ")

    ReDim brr(LBound(arr) To UBound(arr))

    For i = LBound(arr) To UBound(arr)
        brr(i) = CDbl(arr(i))
    Next i

    maxNums = wf.Max(brr)
End Function

enter image description here

Upvotes: 0

Sam
Sam

Reputation: 5731

There are one or two issues with your code. The first one is that the regular expression isn't looking for decimal numbers. If you change it to

.Pattern = "\d+\.?(\d?)+"

it will work better. In short:
\d+ = At least one digit
.? = An optional dot
(\d?)+ = Optional numbers

This is not a waterproof expression, but it works to some extent at least.

The second issue is the potential problem of differing decimal symbols, in which case you will need to do some search and replace before processing.

Upvotes: 2

Alex K.
Alex K.

Reputation: 175936

If its always x=number I think it's simpler to loop over each delimited value then read past the = for the value:

Function MaxValue(data As String)
    Dim i As Long, value As Double
    Dim tokens() As String: tokens = Split(data, ",")

    For i = 0 To UBound(tokens)
        '// get the value after = as a double
        value = CDbl(Trim$(Mid$(tokens(i), InStr(tokens(i), "=") + 1)))
        If (value > MaxValue) Then MaxValue = value
    Next
End Function

Upvotes: 0

Related Questions