How to find the largest number in text string in Excel formula or VBA

Question

This is my first post here. I am looking to get the largest number out of this type of text. And here is the example.

Class 1 - $250,000 - PTD equal to principal sumClass 2 - $500,000 - PTD equal to principal sumClass 3 - $500,000 - PTD equal to principal sumClass 4 - $250,000 Class 5 - $250,000 Class 6 - $250,000

Everyone of the number will have dollar sign. I have tried Scott's solution here. But no luck.

Please let me know if and how it can be done.

Thank you.

@Felix Yao *I have tried the solution Scott's solution here. But no luck.* What was the problem you've encountered? — AntiDrondert, Apr 20 '18 at 10:40
My code works for me. It returns the number without the formatting, you need to format the cell in which the output is placed to match the desired formatting. It is designed to return a number and not a string. — Scott Craner, Apr 20 '18 at 12:49
@AntiDrondert Please take a look at Dan's solution. It gets really close. But this method requires space in front and back of the number in order for it to work. — Felix Yao, Apr 20 '18 at 13:32

DisplayName · Answer 1 · 2018-04-20T10:56:32.213

1

I'd go this way:

Function GetMax(s As String)
    Dim val As Variant
    Dim num As Double
    Dim pos As Long

    For Each val In Split(s, "$")
        pos = 0
        Do While IsNumeric(Mid(val, 1, pos + 1))
            pos = pos + 1
        Loop
        If pos > 0 Then
            num = CDbl(Mid(val, 1, pos))
            If num > GetMax Then GetMax = num
        End If
    Next
End Function

edited Apr 20 '18 at 10:56

answered Apr 20 '18 at 10:47

DisplayName

13,283
2
11
19

@FelixYao, did you try this? – DisplayName Apr 20 '18 at 11:12
Yes I did. It gets stuck once I hit run. – Felix Yao Apr 20 '18 at 13:42
I am sorry. It is not running successful for me as an UDF. When I start it, it just runs unstopped until I hit Esc. – Felix Yao Apr 20 '18 at 14:03
@DisplayName: you are good. I didn't know `CDbl()` could tolerate embedded commas. – S Meaden Apr 20 '18 at 14:51
@FelixYao, I tested with both your string examples with no issues: what’s the string that gives you the issue? – DisplayName Apr 20 '18 at 14:59

Dan · Answer 2 · 2018-04-20T11:08:28.000

0

You can just adapt the answer you linked to by first removing all the "$" signs using VBAs Replace function:

Function MaxInString(rng As String) As Double
Dim splt() As String
Dim i&

'==================NEW LINE==================='
rng = Replace(rng, "$", "")
'============================================='

splt = Split(rng)
For i = LBound(splt) To UBound(splt)
    If IsNumeric(splt(i)) Then
        If splt(i) > MaxInString Then
            MaxInString = splt(i)
        End If
    End If
Next i
End Function

Based on your new requirements, here is a possible regex based solution (based on this https://stackoverflow.com/a/44339803/1011724):

Public Function max_number(s As String) As Double

      Static re As VBScript_RegExp_55.RegExp
      s = Replace(s, ",", "")
      If re Is Nothing Then
        Set re = New RegExp
        re.IgnoreCase = True: re.Global = True
        re.Pattern = "-?\d*\.?\d+"
       End If

     max_number = 0
     For Each elem In re.Execute(s)
        If max_number < CDbl(elem) Then
            max_number = CDbl(elem)
        End If
     Next

End Function

Just make sure to first follow Step 1 in this answer: https://stackoverflow.com/a/22542835/1011724 to add a reference to the regex library first.

edited Apr 20 '18 at 11:08

answered Apr 20 '18 at 10:34

Dan

45,079
17
88
157

Thanks Dan for the speedy reply. I did think about replacing "$" with space. But it doesn't work for the following scenario. – Felix Yao Apr 20 '18 at 10:44
AD&D Class 1, 11 - to a maximum of $200,000Class 2, 10 - $80,000Class 3 - $80,000Class 4, 5 - no coverageClass 8 - maximum $500,000Class 9 - same coverage in place immediately preceeding retirement – Felix Yao Apr 20 '18 at 10:44
@FelixYao then you should add that case into your question. If you can't split on any character, you're going to either have to figure out a regex solution or loop through each character, check if it's a number, comma or fullstop and if it is not, replace it with a space – Dan Apr 20 '18 at 10:53
I think it is promising. But what is elem in the function? – Felix Yao Apr 20 '18 at 11:45
@FelixYao set a break point and use the watch window to explore how the values of `elem` change in the loop. Why is it only promising and not correct? – Dan Apr 20 '18 at 11:46

score 0 · Answer 3 · answered Apr 20 '18 at 11:15

Try this code (necessary comments in code):

Option Explicit
Sub GetMaxNumber()
    Dim txt As String, idx As Long, idx2 As Long, maxValue As Long, extractedNumber As Long, char As String
    maxValue = 0
    'set variable in a code or use cell value
    'txt = Range("A1").Value
    txt = "Class 1 - $250,000 - PTD equal to principal sumClass 2 - $500,000 - PTD equal to principal sumClass 3 - $500,000 - PTD equal to principal sumClass 4 - $250,000 Class 5 - $250,000 Class 6 - $250,000"
    idx = InStr(1, txt, "$")
    'on each loop we will look for dollar sign (you mentioned, that every number starts with it)
    'and then, look for first non-comma non-numeric characted, there the number will end
    'at the end we extract the number from text
    Do While idx > 0
        idx2 = idx + 1
        char = Mid(txt, idx2, 1)
        'determine the end of a number
        Do While IsNumeric(char) Or char = ","
            char = Mid(txt, idx2, 1)
            idx2 = idx2 + 1
        Loop
        'extract the number, also removing comma from it
        extractedNumber = Replace(Mid(txt, idx + 1, idx2 - idx - 2), ",", "")
        'if extracted number is greater than current max, replace it
        If maxValue < extractedNumber Then maxValue = extractedNumber
        idx = InStr(idx + 1, txt, "$")
    Loop

    MsgBox maxValue
End Sub

score 0 · Answer 4 · answered Apr 20 '18 at 11:38

I would split on the spaces, then loop through looking for dollar sign and then once found paste the string into a cell to parse out commas etc.

Note the following code uses the cell parser to strip out commas and currency signs.

Sub Test2()
    Sheet1.Cells(1, 1).Value = "3,000"
    Debug.Assert Sheet1.Cells(1, 1).Value = 3000

    Sheet1.Cells(1, 1).Value = "$250,000"
    Debug.Assert Sheet1.Cells(1, 1).Value = 250000

End Sub

Here is full listing

Sub Test()

    Dim s As String
    s = "Class 1 - $250,000 - PTD equal to principal sumClass 2 - $500,000 - PTD equal to principal sumClass 3 - $500,000 - PTD equal to principal sumClass 4 - $250,000 Class 5 - $250,000 Class 6 - $250,000"


    Dim vSplit As Variant
    vSplit = Split(s, " ")

    Dim ccyMax As Currency
    ccyMax = -1

    Dim vSplitLoop As Variant
    For Each vSplitLoop In vSplit

        If Left$(vSplitLoop, 1) = "$" Then
            Sheet1.Cells(1, 1).Value = vSplitLoop
            Dim ccyParsed As Currency
            ccyParsed = Sheet1.Cells(1, 1).Value
            If ccyParsed > ccyMax Then ccyMax = ccyParsed
        End If

    Next

    Debug.Print ccyMax

End Sub

How to find the largest number in text string in Excel formula or VBA

4 Answers4